Merge Multiple Rows Of The Same ID Into One Row While Creating New Columns In Pandas

Suppose there is a dataframe like this: I want to compress this dataframe into one ID one row format while creating new columns if there are different values in a column for the s

Solution 1:

selection part:

cols=df.filter(like='Col').columns
exclude=['Col1','Col4']
#These are the columns that you want to kept like Col1

Calculation part:

out=df.pivot_table(cols,'Something_ID','Description').swaplevel(axis=1)
includecols=~out.columns.get_level_values(1).isin(exclude)
newdf=out.loc[:,~includecols].droplevel(0,1).groupby(level=0,axis=1).first()
out=out.loc[:,includecols]
out.columns=out.columns.map('_'.join)
out=newdf.join(out).reset_index()

output of out:

  Something_ID      Col1    Desc1_Col2  Desc2_Col2  Desc3_Col2  Desc4_Col2
0   id1             1.1     0.1             2.1     3.1         NaN
1   id2             8.1     1.1             2.1     NaN         5.1

Sample Dataframe used by me:

df=pd.DataFrame({'Something_ID': {0: 'id1', 1: 'id1', 2: 'id1', 3: 'id2', 4: 'id2', 5: 'id2'},
 'Description': {0: 'Desc1',
  1: 'Desc2',
  2: 'Desc3',
  3: 'Desc1',
  4: 'Desc4',
  5: 'Desc2'},
 'Col1': {0: 1.1, 1: 1.1, 2: 1.1, 3: 8.1, 4: 8.1, 5: 8.1},
 'Col2': {0: 0.1, 1: 2.1, 2: 3.1, 3: 1.1, 4: 5.1, 5: 2.1},
 'Col3': {0: 0.3, 1: 6.3, 2: 9.3, 3: 3.3, 4: 15.3, 5: 6.3},
 'Col4': {0: 0.6, 1: 12.6, 2: 18.6, 3: 6.6, 4: 30.6, 5: 12.6}})