Python pandas dataframe reshape long to wide, multiple columns and constant values

user_8826065 0
Viewed 1022

I need to reshape a DataFrame from long to wide format. Example of what the data looks like: 

import pandas as pd

df = pd.DataFrame({'id':[1,1,1,2,2,2,3,3], 'id_age':
30,30,30,23,23,23,29,29], 'product':['A','B','C','A','B','C','A','B'],
'rank':[1,2,3,3,1,2,2,1], 'result':['x','y','z','p','q','r','s','t']})

print(df)

   id  id_age product  rank result
0   1      30       A     1      x
1   1      30       B     2      y
2   1      30       C     3      z
3   2      23       A     3      p
4   2      23       B     1      q
5   2      23       C     2      r
6   3      29       A     2      s
7   3      29       B     1      t

The desired output is 

id  id_age    product       rank     result
0   1      30  [A, B, C]  [1, 2, 3]  [x, y, z]
1   2      23  [A, B, C]  [3, 1, 2]  [p, q, r]
2   3      29     [A, B]     [2, 1]     [s, t]

i.e. one row per id. I have tried to solve the problem by creating a pivot table, but I cannot figure out how to:

  1. reshape it for more than one column (product, rank, result)
  2. handle the values that are constant within individuals (id_age)
  3. get the columns in the final data frame to be in list format (and the order is important, the first value of the list in rank corresponds to the first value of the list in result for a given individual).

Any recommendations on how to move forward on this issue is appreciated!

pandas reshape pivot python
2 Answers

For me working:

df = df.groupby(['id','id_age']).agg(tuple).applymap(list)
print (df)
             product       rank     result
id id_age                                 
1  30      [A, B, C]  [1, 2, 3]  [x, y, z]
2  23      [A, B, C]  [3, 1, 2]  [p, q, r]
3  29         [A, B]     [2, 1]     [s, t]
user_29010020

I used do not know how to do it either...

df1=df.groupby(['id','id_age'])['product','rank','result'].apply(lambda x : x.T.values.tolist()).apply(pd.Series)
df1.columns=['product','rank','result']
df1
Out[126]: 
             product       rank     result
id id_age                                 
1  30      [A, B, C]  [1, 2, 3]  [x, y, z]
2  23      [A, B, C]  [3, 1, 2]  [p, q, r]
3  29         [A, B]     [2, 1]     [s, t]
user_79645270
Related