Reshaping data in pandas dataframe with two columns

Asked Oct 29 '21 at 15:22

Active Oct 29 '21 at 15:22

Viewed 16 times

Consider this sample data:

    ID     Name      question     comment
0   123    Smith     question A   good 
1   123    Smith     question B   bad 
2   444    Jones     question C   bad 
3   444    Jones     question A   good 
4   333    Williams  Question B   bad

Tried pivot and pivottable (some kind of unmelting) to try and get this ideal reshaping:

        ID     Name      question A  question B  quesetion C
    0   123    Smith     good        bad         null
    1   444    Jones     good        null        bad
    2   333    Williams  null        bad         null

This did not work:

 pd.pivot_table(data, values = 'comment', index=['ID','Name'], columns = 'question').reset_index()

I am sure there is some detail within the pivot or pivottable functions I am missing, so it's close. Thanks for taking a look.

asked Oct 29 '21 at 15:22

John Taylor

You data is not numeric, either pass `aggfunc='first'` or use `df.pivot(values = 'comment', index=['ID','Name'], columns = 'question').reset_index()` – Quang Hoang Oct 29 '21 at 15:24
How would I pass aggfunc='first' in this context? – John Taylor Oct 29 '21 at 15:25
`pd.pivot_table(...., aggfunc='first')`? – Quang Hoang Oct 29 '21 at 15:25
I will try that. The first option threw this error, which I recall seeing before. 'ValueError: Index contains duplicate entries, cannot reshape' – John Taylor Oct 29 '21 at 15:27
That means some user with `id,name` answer some question twice. `pivot_table` with `aggfunc='first'` will retain just the first answer. If you want to keep all answer, you need to enumerate rows with same pair `id,name` – Quang Hoang Oct 29 '21 at 15:31
The aggfunc='first' worked it seems. Thanks so much! – John Taylor Oct 29 '21 at 15:32

Reshaping data in pandas dataframe with two columns

0 Answers0