Pivoting a column as an index

mikempc
mikempc Registered Posts: 8 ✭✭✭
edited July 16 in Using Dataiku

I have a database with the respondent_id and answer_name columns and a few others. There are duplicates and I would like to pivot to get in the index the respondent_id and answer_name values in the columns:

7qoG4

And I would like to pivot it in order to have:

                  answer_name
'172283369531047' 18-24
'172283369731047' 18-24
'172283369831047' 18-24
...

So how can I pivot a column as an index and keep the other ones?


Operating system used: Windows

Answers

  • VitaliyD
    VitaliyD Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 102 Dataiker

    Hi,

    As per provided requirements you are probably better of using Distinct or Group recipes. Please refer to the screenshots below. Is there any particular reason you want to pivot?

    Screenshot 2021-11-18 at 18.42.52.pngScreenshot 2021-11-18 at 18.43.00.pngScreenshot 2021-11-18 at 18.43.13.pngScreenshot 2021-11-18 at 18.43.23.png

  • mikempc
    mikempc Registered Posts: 8 ✭✭✭
    edited July 17

    Yes @VitaliyD
    , I want to pivot because rather than having one row by answer my client wants that each row corresponds to one respondent and that each column is one answer (Indeed there is also a question_name column which is relative only to a set of answer_name i.e. 18-24, 25-34, ... for Age Group but not male nor I like it).

    So actually I need to transform something like

    respondent_it     answer_name question_name
    '172283369531047' 18-24       Age group
    '172283369731047' 18-24       Age group
    '172283369831047' 25-34       Age group
    '172283369531047' male        Sex
    '172283369731047' female      Sex
    '172283369831047' female      Sex

    Into something like:

    respondent_it     Age group   Sex
    '172283369531047' 18-24       male
    '172283369731047' 18-24       female
    '172283369831047' 25-34       female

  • VitaliyD
    VitaliyD Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 102 Dataiker

    Hi, In this case, the pivoting should work. You can use a Pivot step in Prepare recipe and then delete unwanted columns, please refer to the screenshots below:

    Screenshot 2021-11-19 at 10.50.00.pngScreenshot 2021-11-19 at 10.50.39.pngScreenshot 2021-11-19 at 10.51.34.png

    Hope this helps.

    -Best.

Setup Info
    Tags
      Help me…