Pivoting a column as an index

mikempc
Level 2
Pivoting a column as an index

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

0 Kudos
3 Replies
VitaliyD
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
Level 2
Author

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

 

 

0 Kudos
VitaliyD
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.

0 Kudos