Dataframe restructure-CodePudding

I have a dataframe and I want to configure it in a way that years are horizontal and Ex1, Ex2 are vertical but at the same time keep the ID values also vertical and consistent. I have tried the transpose function in pandas but it doesn't get the job done.

This is the original dataframe:

   ID   Year    Ex1 Ex2
0   1   2021    315 35
1   1   2022    233 23
2   1   2023    566 5766
3   2   2024    754 7554
4   2   2025    334 3334
5   2   2021    543 5443
6   3   2022    678 6728
7   3   2023    65  645
8   3   2024    456 4556
9   3   2025    53  563

and this is the required result:

    ID  Ex  2021    2022    2023    2024    2025
0   1   Ex1 315      233    566     
1   1   Ex2 35        23    5766        
2   2   Ex1 543                      754    334
3   2   Ex2 5443                    7554    3334
4   3   Ex1          678     65      456     53
5   3   Ex2          6728   645     4556    563

CodePudding user response：

You can also use stack and unstack:

print (df.set_index(["ID","Year"]).stack().unstack("Year", fill_value=""))

Year    2021  2022  2023  2024  2025
ID                                  
1  Ex1   315   233   566            
   Ex2    35    23  5766            
2  Ex1   543               754   334
   Ex2  5443              7554  3334
3  Ex1         678    65   456    53
   Ex2        6728   645  4556   563

CodePudding user response：

You can melt and pivot (or pivot_table):

(df.melt(id_vars=['ID', 'Year'], var_name='Ex')
   .pivot_table(index=['ID', 'Ex'], columns='Year', values='value', fill_value='')
   .reset_index()
   .rename_axis(None, axis=1)
)

output:

   ID   Ex    2021    2022    2023    2024    2025
0   1  Ex1   315.0   233.0   566.0                
1   1  Ex2    35.0    23.0  5766.0                
2   2  Ex1   543.0                   754.0   334.0
3   2  Ex2  5443.0                  7554.0  3334.0
4   3  Ex1           678.0    65.0   456.0    53.0
5   3  Ex2          6728.0   645.0  4556.0   563.0