Sum dataframe rows and put the result in the next row

user_14073111 0
Viewed 177

I have a dataframe like this:

    col1    col2      col3    col4
0    A       A_1       8        4
1    A       A_2       2        7
2    A       A_3       5        5
3    A       A_sum
4    B       B_1       9        8
5    B       B_2       6        8
6    B       B_3       5        2
7    B       B_sum

I want to sum the row from col3 and col4 that belong to A or B and put the sum in the index 3 and 7 and then have an ouput data frame like this:

    col1    col2      col3    col4
0    A       A_1       8        4
1    A       A_2       2        7
2    A       A_3       5        5
3    A       A_sum     15       16
4    B       B_1       9        8
5    B       B_2       6        8
6    B       B_3       5        2
7    B       B_sum     20       18

Edited: Thank you @andrej it worket perfectly with the df like above, but in this one below:

    col1    col2      col3    col4
0    A       A_1       8        4
1    A       A_2       NaN      7
2    A       A_3       NaN      5
3    A       A_sum     NaN      NaN
4    B       B_1       9        8
5    B       B_2       6        8
6    B       B_3       5        NaN
7    B       B_sum     NaN      NaN

when I try your solution @andrej, it gives me this output:

    col1    col2      col3    col4
0    A       A_1       8        4
1    A       A_2       8        7
2    A       A_3       8        5
3    A       A_sum     8        16
4    B       B_1       9        8
5    B       B_2       6        8
6    B       B_3       5        16
7    B       B_sum     20       16

Is there a way to have the sum values only for index 3 and 7?

python-requests pandas dataframe python-3.x python
1 Answers

Try:

# if the empty values aren't NaNs already:
# df = df.replace("", np.nan) 

vals = df.groupby("col1").sum()
df = df.set_index("col1")
df.update(vals, overwrite=False)
print(df.reset_index())

Prints:

  col1   col2  col3  col4
0    A    A_1   8.0   4.0
1    A    A_2   2.0   7.0
2    A    A_3   5.0   5.0
3    A  A_sum  15.0  16.0
4    B    B_1   9.0   8.0
5    B    B_2   6.0   8.0
6    B    B_3   5.0   2.0
7    B  B_sum  20.0  18.0

EDIT:

m = ~df.col2.str.contains("_sum")
df.loc[m] = df[m].fillna(0)

vals = df.groupby("col1").sum()
df = df.set_index("col1")
df.update(vals, overwrite=False)
print(df.reset_index())

Prints:

  col1   col2  col3  col4
0    A    A_1   8.0   4.0
1    A    A_2   0.0   7.0
2    A    A_3   0.0   5.0
3    A  A_sum   8.0  16.0
4    B    B_1   9.0   8.0
5    B    B_2   6.0   8.0
6    B    B_3   5.0   0.0
7    B  B_sum  20.0  16.0
user_100359850
Related