Concatenate row entries with column names

user_8924087 0
Viewed 205

I have two dataframes df1 and df2 with the same number of rows that look like this:

print(df1)

     0
0   CBD
1   THC
2   CBG
3   CBN
4   CBC

and

print(df2)

    min% max%
0   9.0 12.0
1   0.1 0.2
2   NaN NaN
3   NaN NaN
4   NaN NaN

I would like to obtain a single dataframe with one single row, where the entries of the first dataframe are concatenated with the column names to obtain 5 * 2 = 10 columns:

  CBD min%  CBD max%   THC min%   THC max% CBG min% CBG max% CBN min% CBN max% CBC min% CBC max%
0 9.0       12.0       0.1        0.2      NaN      NaN      NaN      NaN      NaN      NaN

I have tried concatenating the two dataframes, as I suppose I will need to transpose but I am stuck here:

pd.concat([df1, df2], axis=1)


     0  min% max%
0   CBD 9.0 12.0
1   THC 0.1 0.2
2   CBG NaN NaN
3   CBN NaN NaN
4   CBC NaN NaN

Any ideas how I could get the desired result?

pandas dataframe python
2 Answers

Try:

idx = pd.MultiIndex.from_product([df1["0"], df2.columns])
df2 = pd.concat([df1, df2], axis=1)
x = df2.set_index("0").stack().reindex(idx).to_frame().T
x.columns = x.columns.to_flat_index().str.join(" ")
print(x)

Prints:

   CBD min%  CBD max%  THC min%  THC max%  CBG min%  CBG max%  CBN min%  CBN max%  CBC min%  CBC max%
0       9.0      12.0       0.1       0.2       NaN       NaN       NaN       NaN       NaN       NaN
user_100359850

Maybe...

Given:

    Col0
0   CBD
1   THC
2   CBG
3   CBN
4   CBC

    min% max%
0   9.0 12.0
1   0.1 0.2
2   NaN NaN
3   NaN NaN
4   NaN NaN

Try:

df1 = pd.concat([df1, df2], axis=1)
df3 = df1.set_index(['Col0'])[['min%', 'max%']].unstack().reset_index()
df3['Col0'] = df3['Col0'] + ' ' + df3['level_0']
del df3['level_0']
print(df3.set_index('Col0').T)

Outputs:

Col0    CBD min%    THC min%    CBG min%    CBN min%    CBC min%    CBD max%    THC max%    CBG max%    CBN max%    CBC max%
0       9.0         0.1         NaN         NaN         NaN         12.0        0.2         NaN         NaN         NaN
user_91922840
Related