Group the same column value in the dataframe and add the sum of the same values as a new column

user_15886547 0
Viewed 79

I have a pandas DataFrame like following.

df = pd.DataFrame({
        'Column1': ['A', 'B', 'C', 'A', 'B', 'A', 'C', 'A', 'B', 'B'], 
        'Column2': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
        'Column3': ['X','Y','Z','X', 'X', 'Z','X','Y','Z','X']})

I want to group by column 1. I also want to sum these values in column 2 and add the values in column 3 as a new column.

    Column1 Column2 Column3
0      A       1       X
1      B       2       Y
2      C       3       Z
3      A       4       X
4      B       5       X
5      A       6       Z
6      C       7       X
7      A       8       Y
8      B       9       Z
9      B      10       X

Expected outcome

    Column1 Column2 X   Y   Z
0      A      19    5   8   6
1      B      26    15  2   9
2      C      10    7   0   3

I looked at the sample questions. But I could not find an answer to my problem. Any help regarding this is appreciated.

pandas-groupby pandas dataframe python
1 Answers

Use DataFrame.pivot_table with DataFrame.insert:

df = df.pivot_table(index='Column1', 
                    columns='Column3', 
                    values='Column2', 
                    aggfunc='sum', 
                    fill_value=0).reset_index().rename_axis(None, axis=1)
df.insert(1, 'Column2', df.sum(axis=1))
print (df)
  Column1  Column2   X  Y  Z
0       A       19   5  8  6
1       B       26  15  2  9
2       C       10   7  0  3
user_29010020
Related