How can I group by in python and create columns with information of a column if another column has a specific value?

Question

I have a data frame with "Team", "HA" (home away), "attack", "defense"

And what I need to have is a table, grouped by Team with 4 columns like this

I guess it could be done with an aggregate function, but I don't really know how

df_ad=df_calc.groupby(['Team','Liga']).agg...

The equivalent in SQL would be

SELECT Team, CASE
    WHEN HA='Home' THEN attack
    END AS Home_attack, CASE
    WHEN HA='Home' THEN defense
    END AS Home_defense, CASE
    WHEN HA='Away' THEN attack
    END AS Away_attack, CASE
    WHEN HA='Away' THEN defense
    END AS Away_defense
FROM df_calc;

score 0 · Accepted Answer · answered Jul 04 '22 at 18:04

Just pivot your dataframe:

out = df.pivot('Team', 'HA', ['attack', 'defense'])
out.columns = out.columns.swaplevel().to_flat_index().map(' '.join)
out = out.reset_index()
print(out)

# Output
          Team Away attack Home attack Away defense Home defense
0  A. San Luis           1           3            2            4
1     AC Milan           5           7            6            8
2      AS Roma           9          11           10           12

For the first part, you can read: How can I pivot a dataframe?

How can I group by in python and create columns with information of a column if another column has a specific value?

1 Answers1