Pandas stack columns with the same index

I would like to manipulate data contained within a Dataframe. The dataframe is structured as follows

EID     FX      FY      FZ      SUB
1001    FXA     FYA     FZA     101
1002    FXB     FYB     FZB     101
1003    FXC     FYC     FZC     101
1004    FXD     FYD     FZD     101
1001    FXE     FYE     FZE     102
1002    FXF     FYF     FZF     102
1003    FXG     FYG     FZG     102
1004    FXH     FYH     FZH     102
1001    FXI     FYI     FZI     103
1002    FXJ     FYJ     FZJ     103
1003    FXK     FYK     FZK     103
1004    FXL     FYL     FZL     103

Sorting the data by the Column 'SUB' and aligning to the Column 'EID' to look like this,

EID     FX      FY      FZ      SUB     FX      FY      FZ      SUB     FX      FY      FZ      SUB
1001    FXA     FYA     FZA     101     FXE     FYE     FZE     102     FXI     FYI     FZI     103
1002    FXB     FYB     FZB     101     FXF     FYF     FZF     102     FXJ     FYJ     FZJ     103
1003    FXC     FYC     FZC     101     FXG     FYG     FZG     102     FXK     FYK     FZK     103
1004    FXD     FYD     FZD     101     FXH     FYH     FZH     102     FXL     FYL     FZL     103

I have worked though this example and it works with a single column, Move values in rows in a new column in pandas

BUT I would like to move more than just a single column.

Thanks in advance for any help you can provide.

CodePudding user response：

The logic is:

Group by "SUB".
Iterate the grouper and collect all sub-groups.
Concat all collected sub-groups by index.

df_arr = []
for key, sub_df in df.groupby("SUB"):
  df_arr.append(sub_df.set_index("EID"))

df = pd.concat(df_arr, axis="columns")

Output:

       FX   FY   FZ  SUB   FX   FY   FZ  SUB   FX   FY   FZ  SUB
EID                                                             
1001  FXA  FYA  FZA  101  FXE  FYE  FZE  102  FXI  FYI  FZI  103
1002  FXB  FYB  FZB  101  FXF  FYF  FZF  102  FXJ  FYJ  FZJ  103
1003  FXC  FYC  FZC  101  FXG  FYG  FZG  102  FXK  FYK  FZK  103
1004  FXD  FYD  FZD  101  FXH  FYH  FZH  102  FXL  FYL  FZL  103

CodePudding user response：

Try this:

cols = df.columns[1:]
df = (df.set_index(['EID',df.groupby('EID').cumcount()])[cols]
        .unstack()
        .reset_index())