Merge first two of a dataframe and create new header

user_13067389 0
Viewed 37

I have a dataframe

df = pd.DataFrame({ 
    'Col1': ['abc', 'qrt', 130, 200, 190, 210], 
    'Col2': ['xyz','tuv', 130, 200, 190, 210],
    'Col3': ['pqr', 'set', 130, 200, 190, 210],})

I wish to take the first two rows of the dataframe, merge them separated by a hyphen and convert them into a new header. I tried

df.columns = np.concatenate(df.iloc[0], df.iloc[1])
df.columns = new_header

But that does not seem to work. The output should look like

df = pd.DataFrame({ 
    'abc_qrt': [ 130, 200, 190, 210], 
    'xyz_tuv': [130, 200, 190, 210],
    'pqr_set': [ 130, 200, 190, 210],})
pandas concatenation merge
2 Answers

Try with

df = df.T.set_index([0,1]).T
df.columns = df.columns.map('_'.join)
df
Out[308]: 
  abc_qrt xyz_tuv pqr_set
2     130     130     130
3     200     200     200
4     190     190     190
5     210     210     210
user_79645270

You can take the first two rows, join them with _ and then set columns of the rest with that:

df.iloc[2:].set_axis(df.iloc[:2].agg("_".join), axis=1)

to get

  abc_qrt xyz_tuv pqr_set
2     130     130     130
3     200     200     200
4     190     190     190
5     210     210     210
user_93321870
Related