reshape dataframe pandas with string data - no similar answers

Question

Given dataframe in this format:

Name      Attribute      Answer
Joe       One            Yes
Joe       Two            No
Joe       Two            More info
Mary      One            Left undone
Mary      Three          No response
Mary      One            Too late

I have tried versions of pivot, pivotable, unstack etc to 'unmelt' this data from long to wide format. The result I am seeking is this:

Name       One                        Two                Three
Joe        Yes                        No, More info      Null
Mary       Left undone, Too late      Null               No response

Essentially, I need to make all the unique values in the Attribute column into column headers and then the value in the Attribute column for each unique named person be the value in the Answer column

I am certain there is some reshaping magic I have not pieced together sufficiently, but the typical approaches and reading more than 20 'reshape data long to wide' questions on SO were not on point.

I posed this question two hours or so ago and someone closed it claiming it was already answered. Welp, went through each of the supposed answers to this and none of them succeeded. So, my question has not already been answered. FYI.

`df.set_index(['Name', 'Attribute'])['Answer'].unstack().reset_index()` — Scott Boston, Apr 08 '21 at 20:19
Thanks for the prompt reply. Copy/pasted this code and threw this error: 'Index contains duplicate entries, cannot reshape' — John Taylor, Apr 08 '21 at 20:21
You have multiple entry per line, Do you want use the first? — Scott Boston, Apr 08 '21 at 20:22
`df.groupby(['Name', 'Attribute'])['Answer'].first().unstack()` ? — Scott Boston, Apr 08 '21 at 20:23
@ScottBoston Yes, the Name column has multiple rows per name and I need to transform every row into the shape I noted above. There are probably 50 unique names in that Name column, but more than 2000 rows in the df in total. — John Taylor, Apr 08 '21 at 20:24
@ScottBoston Your groupby offering seemed to have worked perfectly. Thanks so much! — John Taylor, Apr 08 '21 at 20:25
@ScottBoston I spoke too soon. This question is not like the others noted above. In my df there are rows with the same name, same attribute, but a different response. I have edited my initial question to reflect this nuance. — John Taylor, Apr 08 '21 at 20:37

score 2 · Accepted Answer · answered Apr 09 '21 at 00:53

2

Try:

df.groupby(['Name','Attribute'])['Answer'].agg(lambda x: ', '.join(x)).unstack().reset_index()

Output:

Attribute  Name                    One        Three            Two
0           Joe                    Yes          NaN  No, More info
1          Mary  Left undone, Too late  No response            NaN

answered Apr 09 '21 at 00:53

Scott Boston

147,308
15
139
187

Thanks @Scott Boston. This groupy example worked perfectly. Even had a request to insert a ';' instead of a comma and that worked well too. Thanks again! – John Taylor Apr 10 '21 at 12:51

reshape dataframe pandas with string data - no similar answers

1 Answers1