How to change a dataframe a certain way pandas python?

Question

I have this

    Date      |Category_1 |Category_2 |Value|
    __________|___________|___________|______
    24.10.2020|A          |AAA        |5
     _________|___________|___________|______
    24.10.2020|A          |BBB        |0.6
     _________|___________|___________|______
    24.10.2020|B          |BBB        |17

I need to get this

     Date     |A_AAA      |A_BBB      |B_BBB|
    __________|___________|___________|______
    24.10.2020|5          |0.6        |17

How to do this in python with a help of pandas?

this is a pivot table or cross tab, all you need to do is concat your Cat 1 + 2 columns before hand or during the pivot. — Umar.H, Oct 26 '20 at 16:28
Does this answer your question? [How to pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) — Umar.H, Oct 26 '20 at 16:29

score 1 · Accepted Answer · answered Oct 26 '20 at 16:28

You can use a pivot to create a table like that with a MultiIndex column. Once you do that, you can flatten the multiindex column back down to a regular Index with the formatting you want (e.g. "{Category1}_{Category2}")

out = df.pivot(index=["Date"], columns=["Category1", "Category2"], values="Value")
out.columns = ["{}_{}".format(*col) for col in out.columns]

print(out)
            A_AAA  A_BBB  B_BBB
Date                           
24.10.2020    5.0    0.6   17.0

If you have repeated combinations of categories (e.g. more than 1 row with Category1 == "A" AND category2 == "AAA") then you'll need to use pivot_table instead of pivot.

How to change a dataframe a certain way pandas python?

1 Answers1