Search code examples
pythonpandasdataframeconcatenation

In pandas, how to re-arrange the dataframe to simultaneously combine groups of columns?

I hope someone could help me solve my issue.

Given a pandas dataframe as depicted in the image below,

enter image description here

I would like to re-arrange it into a new dataframe, combining several sets of columns (the sets have all the same size) such that each set becomes a single column as shown in the desired result image below.

enter image description here

Thank you in advance for any tips.

Solution

  • You can do this with pd.wide_to_long and rename the 'c' column:

    df_out = pd.wide_to_long(df.reset_index().rename(columns={'c':'c1'}),
                         ['a','b','c'],'index','no')
df_out = df_out.reset_index(drop=True).ffill().astype(int)
df_out

    Output:

       a  b  c
0  0  2  4
1  1  3  4
2  5  7  9
3  6  8  9

    Same dataframe just sorting is different.

    pd.wide_to_long(df,  ['a','b'], 'c', 'no').reset_index().drop('no', axis=1)

    Output:

       c  a  b
0  4  0  2
1  9  5  7
2  4  1  3
3  9  6  8