Starting from a dataframe df I would like to groupby and sum per each category in a specific column and create a new column per each category (see below example with desired output). As an example given the dataframe df
data = {"ID": ["111", "111","111" , "2A2","3B3","4C4","5D5","6E6",],
"category": ["A", "B", "A","A","B","B","C","C",],
"length": [1,2,4,1,2,2,1,3],}
df = pd.DataFrame(data)
I would like to obtain the original dataframe df with the additional columns A, B ,C (one new column per each unique attribute in the column "category") grouped by "ID"
I’ve checked some similar answers so far but I could not solve the issue.
This is one approach that I’ve followed without getting the desired output:
grouped_multiple = df.groupby(['ID','material']).agg({'length': [np.sum, np.sum, np.sum]})
grouped_multiple.columns = ["A", "B", "C"]
grouped_multiple = grouped_multiple.reset_index()
print(grouped_multiple)
which outputs:
However my desidered output would look like
ID category A B C
0 111 A 5 2 0
1 2A2 A 1 0 0
2 3B3 B 0 2 0
3 4C4 B 0 2 0
4 5D5 C 0 0 1
5 6E6 C 0 0 3
Every element in the category column is grouped by the ID and category and then summed, then the columns are created for every unique value in the category column.
Thanks for any help!
>Solution :
df.groupby(['ID', 'category'])['length'].sum().unstack().fillna(0)
Output:
category A B C
ID
111 5.0 2.0 0.0
2A2 1.0 0.0 0.0
3B3 0.0 2.0 0.0
4C4 0.0 2.0 0.0
5D5 0.0 0.0 1.0
6E6 0.0 0.0 3.0