Combine some columns in last row of a pandas dataframe

Question

edit: I understand how to get the actual values, but I wonder how to append a row with these 2 sums to the existing df?

I have a dataframe score_card that looks like:

15min_colour	15min_high	15min_price	30min_colour	30min_high	30min_price
1	1	-1	1	-1	1
1	-1	1	1	1	1
-1	1	-1	1	1	1
-1	1	-1	1	-1	1

Now I'd like to add a row that sums up all the 15min numbers (first 3 columns) and the 30min numbers and so on (the actual df is larger). Means I don't want to add up the individual columns but rather the sum of the columns' sums. The row I'd like to add would look like:

sum_15min_colour&15min_high&15min_price	sum_30min_colour&30min_high&30min_price
0	8

Please disregard the header, it's only to clarify what I'm intending to do. I assume there's a multiindex involved, but I couldn't figure out how to apply it to my existing df to achieve the desired output.

Also, is it possible to add a colum with the sum of the whole table?

Thanks for your support.

The sum of 15 minute groups is zero... amirite – piRSquared Mar 23 '21 at 17:53 — piRSquared, Mar 23 '21 at 17:53
`np.sum(df.filter(like='15').values)` ??? – Pygirl Mar 23 '21 at 17:54 — Pygirl, Mar 23 '21 at 17:54

score 1 · Accepted Answer · answered Mar 23 '21 at 17:55

1

You can sum in this way:

np.sum(df.filter(like='15').values), np.sum(df.filter(like='30').values)

0,8

answered Mar 23 '21 at 17:55

Pygirl

12,969
5
30
43

Thanks. See edit, how can I append these results to the existing df? – Florida_Rolf Mar 24 '21 at 08:16
@Florida_Rolf: How they should look like after getting appended to original dataframe. As you will have 4 columns in original df and this will give you two columns. – Pygirl Mar 24 '21 at 08:20
that's exactly what I'm trying to solve. Is there a way of merging 3 columns in the last row? – Florida_Rolf Mar 24 '21 at 08:28
@Florida_Rolf: That's actually not possible I guess. You can do something like this: https://stackoverflow.com/questions/49533330/pandas-data-frame-how-to-merge-columns – Pygirl Mar 24 '21 at 09:21

score 1 · Answer 2 · answered Mar 23 '21 at 17:56

1

`groupby`

Can take a callable (think function) and use it on the index or columns

df.groupby(lambda x: x.split('_')[0], axis=1).sum().sum()

15min    0
30min    8
dtype: int64

answered Mar 23 '21 at 17:56

piRSquared

285,575
57
475
624

Thanks. See edit, how can I append these results to the existing df? – Florida_Rolf Mar 24 '21 at 08:14

score 0 · Answer 3 · answered Mar 23 '21 at 17:59

it's depends on axis.

Simply - this sum the value in axis 0: So in your case - columns(it's sum all values in columns vertically).

df.sum(axis = 0, skipna = True)

print(df):

OUTPUT:

sum_column = df["col1"] + df["col2"]
df["col3"] = sum_column
print(df)

OUTPUT:

So in your case:

summed0Axis = df.sum(axis = 0, skipna = True)
sum_column = summed0Axis["15min_colour"] + summed0Axis["15min_high"] + summed0Axis["15min_price"]

print(sum_column)

more intelligent option:

Find all columns, which included 15:

columnsWith15 = df.loc[:,df.columns.str.contains("15").sum]
columnsWith30 = df.loc[:,df.columns.str.contains("30").sum]

Combine some columns in last row of a pandas dataframe

3 Answers3

groupby

`groupby`