1

I have a dataframe, which looks like

import pandas as pd
df=pd.DataFrame({'group': ['bmw', 'bmw', 'audi', 'audi', 'mb'], 
                   'date': ['01/20', '02/20', '01/20', '02/20','01/20'],
                   'value1': [1,2,3,4,5],
                   'value2': [6,7,8,9,10]})

I want to make it wider and be look like

enter image description here

I tried to find a solution here, but did not find it. Could you help to create the new table?

Priit Mets
  • 465
  • 2
  • 14
  • Please, take a while to read [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – Corralien Jul 15 '21 at 12:34

2 Answers2

4

Use pivot:

out = df.pivot(index='date', columns='group', values=['value1', 'value2'])
out.columns = out.swaplevel(axis='columns').columns.to_flat_index().map('_'.join)

>>> out.reset_index()
    date  audi_value1  bmw_value1  mb_value1  audi_value2  bmw_value2  mb_value2
0  01/20          3.0         1.0        5.0          8.0         6.0       10.0
1  02/20          4.0         2.0        NaN          9.0         7.0        NaN
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • hey, your answer is so helpful for my work too. but I feel it has different scenario because it has ID groups and I want to keep the column as it is, if all the values of a ID group are same. could you please help with that? here is the link https://stackoverflow.com/questions/68394584/merge-multiple-rows-of-the-same-id-into-one-row-while-creating-new-columns-in-pa – Gaya3 Jul 15 '21 at 13:26
2

Use DataFrame.pivot without value for all another columns and flatten MultiIndex:

df = df.pivot(index='date', columns='group')
df.columns = df.columns.map(lambda x: f'{x[1]}_{x[0]}')
print (df)
       audi_value1  bmw_value1  mb_value1  audi_value2  bmw_value2  mb_value2
date                                                                         
01/20          3.0         1.0        5.0          8.0         6.0       10.0
02/20          4.0         2.0        NaN          9.0         7.0        NaN
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252