[FIXED] In pandas how to create new column from observations and aggregate values from another column

Issue

I have this dataframe and I want to transform it into another dataframe with a column which combines observations from several columns in the first dataframe and aggregates values from the column "points". Here's the dataframe and below is the desired result:

player_data = pd.DataFrame({"customer_id": ["100001", "100002", "100005", "100006", "100007", "100011", "100012", 
                                            "100013", "100022", "100023", "100025", "100028", "100029", "100030"],
                            "country": ["Austria", "Germany", "Germany", "Sweden", "Sweden", "Austria", "Sweden", 
                                        "Austria", "Germany", "Germany", "Austria", "Austria", "Germany", "Austria"],
                            "category": ["basic", "pro", "basic", "advanced", "pro", "intermidiate", "pro", 
                                         "basic", "intermidiate", "intermidiate", "advanced", "basic", "intermidiate", "basic"],
                            "gender": ["male", "male", "female", "female", "female", "male", "female",
                                       "female", "male", "male", "female", "male", "male", "male"],
                            "age_group": ["20", "30", "20", "30", "40", "20", "40",
                                          "20", "30", "30", "40", "20", "30", "20"],
                            "points": [200, 480, 180, 330, 440, 240, 520, 180, 320, 300, 320, 200, 280, 180]})

The new dataframe is supposed to look like this:

Thank you all!

Solution

Would this be what you are looking for?

df_new = df.groupby(['country', 'category', 'gender', 'age_group'])['points'].agg('sum').reset_index()
df_new.pivot_table(values = 'points', index = ['country', 'category', 'gender'], columns = 'age_group', fill_value = 0).reset_index().sort_values(['country', 'category', 'gender'])

However, this will not have any columns that have only 0s for example Australia | Advanced | M will not be in here since there wasn't any mention in it for the original df. If you wanted to dynamically add them you might need to rethink the structure of your df.

Answered By - ArchAngelPwn