1

With Pandas I'm trying to rename unnamed columns in dataframe with values on the first ligne of data.

My dataframe:

id store unnamed: 1 unnamed: 2 windows unnamed: 3 unnamed: 4
0 B1 B2 B3 B1 B2 B3
1 2 c 12 15 15 14
2 4 d 35 14 14 87

My wanted result:

id store_B1 store_B3 store_B2 windows_B1 windows_B2 windows_B3
0 B1 B2 B3 B1 B2 B3
1 2 c 12 15 15 14
2 4 d 35 14 14 87

I don't know how I can match the column name with the value in my data. Thanks for your help. Regards

GiiaG
  • 71
  • 3
Laurent Cesaro
  • 341
  • 1
  • 3
  • 17

1 Answers1

4

You can use df.columns.where to make unnamed: columns NaN, then convert it to a Series and use ffill:

df.columns = pd.Series(df.columns.where(~df.columns.str.startswith('unnamed:'))).ffill() + np.where(~df.columns.isin(['id','col2']), ('_' + df.iloc[0].astype(str)).tolist(), '')

Output:

>>> df
   id store_B1 store_B2 store_B3 windows_B1 windows_B2 windows_B3
0   0       B1       B2       B3         B1         B2         B3
1   1        2        c       12         15         15         14
2   2        4        d       35         14         14         87
  • it's possible to use a list of column name instead of one ? df.columns != ['id', 'col2', 'col3'] – Laurent Cesaro Feb 08 '22 at 16:26
  • Yes. `~df.columns.isin(['id', 'col2', 'col3'])` –  Feb 08 '22 at 16:29
  • I tried this one : df.columns = pd.Series(df.columns.where(~df.columns.str.startswith('unnamed:'))).ffill() + np.where(df.columns != df.columns.isin(['id','col2']), ('_' + df.iloc[0].astype(str)).tolist(), '') But I missed something.. – Laurent Cesaro Feb 08 '22 at 16:34
  • I updated the answer. Basically you want to just say `~df.columns.isin(['id','col2'])`, not `df.columns != df.columns.isin(['id','col2'])`. –  Feb 08 '22 at 16:36
  • 1
    Thanks really much ! It working really well !! – Laurent Cesaro Feb 08 '22 at 16:38