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.10.12.13.1NaN1   id2             8.11.12.1NaN5.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}})