create multi index column dataframe by transposing(pivoting) a column

Question

I have a dataframe that looks something like:

date                  enddate      category     Low     High
2023-01-02 06:00:00   2023-12-01   A            45      55
2023-01-02 06:00:00   2024-12-01   A            46      56
2023-01-02 06:00:00   2025-12-01   A            47      57
2023-01-02 06:00:00   2023-12-01   B            85      86
2023-01-02 06:00:00   2024-12-01   B            86      87
2023-01-02 06:00:00   2025-12-01   B            88      89

And am looking to convert to a dataframe that looks something like:

date                  
                           2023-12-01       2024-12-01          2025-12-01
                          Category Low High Category Low High  Category Low High
2023-01-02 06:00:00       A    45  55        A   46  47        A    47  57
2023-01-02 06:00:00       B    85  86        B   86  87        B    88  89

SO it is essentially creating multi index columns. Am not sure what will be the efficient way here. I played around with stacking/unstacking and pivoting a bit, but could not really grab myself around it.

If you can suggest a good way please.

Please note the date values may not be 06:00:00 for all the rows.

You may want to see Q/A 10 in the dup link. Or it's just straight up pivot as in the answer below. — Quang Hoang, Jan 03 '23 at 04:34

score 0 · Answer 1 · answered Jan 03 '23 at 04:26

Could try set_index + unstack:

df.set_index(['date', 'enddate', 'category']).unstack(level=1).swaplevel(axis=1).sort_index(axis=1, level=0)

enddate                      2023-12-01     2024-12-01     2025-12-01    
                                   High Low       High Low       High Low
date                category                                             
2023-01-02 06:00:00 A                55  45         56  46         57  47
                    B                86  85         87  86         89  88

create multi index column dataframe by transposing(pivoting) a column

1 Answers1