Pandas - transpose one column

Question

I'm having difficulty using transpose with pandas.

I have the following df:

date         name    quantity
1/1/2018     A       5
1/1/2018     B       6
1/1/2018     C       7
1/2/2018     A       9
1/2/2018     B       8
1/2/2018     C       6

I eventually want to create a pairwise correlation for all the names and their quantities on each date. To to that end, I'm trying to create the following output from this df first:

 date       A    B    C
 1/1/2018   5    6    7
 1/2/2018   9    8    6

The transpose is difficult to me since I can get duplicate column headers, but I also don't want to lose any data by dropping them first. I have a feeling the answer may be with a panda utility that I don't really use and I may be tunneling on transpose...

Answer 1

Since you aren't performing an aggregation, pd.DataFrame.pivot should be preferred to groupby / pivot_table:

res = df.pivot(index='date', columns='name', values='quantity')

print(res)

name      A  B  C
date             
1/1/2018  5  6  7
1/2/2018  9  8  6

If you wish you can use reset_index to elevate date to a column.

Answer 2

By no means is my proposed solution better than jpp's. I just happened to run into the same problem and solved it differently.

df.set_index(['date', 'name']).unstack()

The result looks a little messier too but it worked in my case: