Is there a way I can collapse the data frame into ONE row with associated columns

Question

Here is my data frame:

    col1   col2     col3    
 0  data1   1        11
 1  data2   2        22
 2  data3   3        33

I would like to transform it into this form:

     data1_col2     data1_col3    data2_col2    data2_col3    data3_col2...  
 0        1             11             2             22            3

If there is a solution for this answer.. what is the term for this type of operation?

Answer 1

You can do :

s = df.set_index('col1').stack()
out = pd.DataFrame({x[0]+'_'+x[1] : [s[x]] for x in s.index})

Output:

    data1_col2  data1_col3  data2_col2  data2_col3  data3_col2  data3_col3
0            1          11           2          22           3          33

Answer 2

You can try this:

import numpy as np 
import pandas as pd 

data = {"cols" : ["r1", "r2", "r3", "r4"], "X" : [1, 2, 3, 4], "Y" : [1, 22, 33, 44]}

df = pd.DataFrame(data=data)
print(df)

new_cols = [i + '_' + str(j) for i in df.cols for j in df.columns[1:]]
print(new_cols)

new_data = df.iloc[:, 1:].values.flatten()

df2 = pd.DataFrame(columns=new_cols)
df2.loc[0] = new_data
print(df2)

This gives df as:

  cols  X   Y
0   r1  1   1
1   r2  2  22
2   r3  3  33
3   r4  4  44

and new_cols as

['r1_X', 'r1_Y', 'r2_X', 'r2_Y', 'r3_X', 'r3_Y', 'r4_X', 'r4_Y'],

and df2 as

   r1_X  r1_Y  r2_X  r2_Y  r3_X  r3_Y  r4_X  r4_Y
0     1     1     2    22     3    33     4    44