Q: Python (pandas or other) - I need to "flatten" a data file from many rows, few columns to 1 row many columns

Question

I need to "flatten" a data file from many rows, few columns to 1 row many columns.

I currently have a dataframe in pandas (loaded from Excel) and ultimately need to change the way the data is displayed so I can accumulate large amounts of data in a logical manner. The below tables are an attempt at illustrating my requirements.

From:

         1      2
Ryan     0.706  0.071
Chad     0.151  0.831
Stephen  0.750  0.653

To:

1_Ryan  1_Chad  1_Stephen  2_Ryan  2_Chad  2_Stephen
0.706   0.151   0.75       0.071   0.831   0.653

Thank you for any assistance!

Answer 1

One line, for fun

df.unstack().pipe(
    lambda s: pd.DataFrame([s.values], columns=s.index.map('{0[0]}_{0[1]}'.format))
)

   1_Ryan  1_Chad  1_Stephen  2_Ryan  2_Chad  2_Stephen
0   0.706   0.151       0.75   0.071   0.831      0.653

Answer 2

Let's use stack, swaplevel, to_frame, and T:

df_out = df.stack().swaplevel(1,0).to_frame().T.sort_index(axis=1)

Or better yet,(using @piRSquared unstack solution)

df_out = df.unstack().to_frame().T

df_out.columns = df_out.columns.map('{0[0]}_{0[1]}'.format)

df_out

Output:

   1_Chad  1_Ryan  1_Stephen  2_Chad  2_Ryan  2_Stephen
0   0.151   0.706       0.75   0.831   0.071      0.653