I want to add data of first row of the dataframe to its column name & delete first row.
Source DataFrame:
2013K2 2013K3 2013K4 2013K5
ABC1 ABC2 ABC3 ABC4
324 5435 543 543
6543 543 657 765
765 876 876 9876
Need to rename column name as Column Name +'|'+ Data of First row:
2013K2|ABC1 2013K3|ABC2 2013K4|ABC3 2013K5|ABC4
324 5435 543 543
6543 543 657 765
765 876 876 9876
IIUC
df.columns=df.columns+'|'+df.iloc[0,:]
df.iloc[1:,]
Out[41]:
0 2013K2|ABC1 2013K3|ABC2 2013K4|ABC3 2013K5|ABC4
1 324 5435 543 543
2 6543 543 657 765
3 765 876 876 9876
df=df.iloc[1:,]
df
Out[43]:
0 2013K2|ABC1 2013K3|ABC2 2013K4|ABC3 2013K5|ABC4
1 324 5435 543 543
2 6543 543 657 765
3 765 876 876 9876
You could do it like this, using T and set_index, then combining multiindex columns to a single column heading using map and format.
df_out = df.T.set_index(0, append=True).T
df_out.columns = df_out.columns.map('{0[0]}|{0[1]}'.format)
df_out
Output:
2013K2|ABC1 2013K3|ABC2 2013K4|ABC3 2013K5|ABC4
1 324 5435 543 543
2 6543 543 657 765
3 765 876 876 9876
you can use the following one-liner:
In [148]: df = df.rename(columns=lambda x: x+'|'+df.iloc[0][x]).iloc[1:]
In [149]: df
Out[149]:
2013K2|ABC1 2013K3|ABC2 2013K4|ABC3 2013K5|ABC4
1 324 5435 543 543
2 6543 543 657 765
3 765 876 876 9876
One can use simple for loop and other basic functions to create a new list of column names and change the dataframe:
newcols = [] # empty list to have new column names
for i in range(len(df.columns)):
newcols.append(df.columns[i]+'|'+df.iloc[0,i]) # make and add new name to list
df.columns = newcols # assign column names
df = df.iloc[1:,:] # exclude first row
print(df)
Output:
2013K2|ABC1 2013K3|ABC2 2013K4|ABC3 2013K5|ABC4
1 324 5435 543 543
2 6543 543 657 765
3 765 876 876 9876
