How to sum based on other cols

Question

I'd like to calculate the following sum(size):

The sum(size) is a sum of col size, and for each col i'd to sum only up the last non-0/NaN row. Is this possible in python?

I've tried to add df.apply(pd.Series.last_valid_index) to identify the last index for each col, but i don't know how to incorporate into the over query to get the sum for each col.

Answer 1

You can try something like this:

import pandas as pd
import numpy as np

df = pd.DataFrame([[5,5,4,3],[7,6,6,np.nan],[4,np.nan,4,2],[3,3,np.nan,np.nan],[6,np.nan,np.nan, np.nan]])
s=df.apply(pd.Series.last_valid_index)
df.loc['Sum',:] = [df.iloc[:i+1, 0].sum() for i in s]

print(df)

Output:

        0     1     2     3
0     5.0   5.0   4.0   3.0
1     7.0   6.0   6.0   NaN
2     4.0   NaN   4.0   2.0
3     3.0   3.0   NaN   NaN
4     6.0   NaN   NaN   NaN
Sum  25.0  19.0  16.0  16.0

Answer 2

Here is a vectorial approach:

s = (
 df.filter(like='m')
   .notna()[::-1].cummax()
   .mul(df['size'], axis=0)
   .sum()
 )

s['size'] = df['size'].sum()

df.loc['sum'] = s

print(df)

Output:

     size    m1    m2    m3
0       5   5.0   4.0   3.0
1       7   6.0   6.0   NaN
2       4   NaN   4.0   2.0
3       3   3.0   NaN   NaN
4       6   NaN   NaN   NaN
sum    25  19.0  16.0  16.0

Intermediates:

(df.filter(like='m')
   .notna()[::-1].cummax()
 )

      m1     m2     m3
0   True   True   True
1   True   True   True
2   True   True   True
3   True  False  False
4  False  False  False


(df.filter(like='m')
   .notna()[::-1].cummax()
   .mul(df['size'], axis=0)
 )

   m1  m2  m3
0   5   5   5
1   7   7   7
2   4   4   4
3   3   0   0
4   0   0   0

Answer 3

Once your df is cleaned you will not have empty cells, they most likely will be filled with 'nan' or zeros. Also in your picture you have an 'addition' error. If you add the numbers from the table the count will not match the count in that picture. This will add a column at the end of your df that will contain a count:

import pandas as pd

df = pd.DataFrame({'size':[5,7,4,3,6], 'm1': [5,6, 0, 3, 0], 'm2': [4,6,4,0,0], 'm3': [3, 0, 2, 0, 0]})
df1 = pd.DataFrame({'size':[df['size'].sum()], 'm1':[df['m1'].sum()], 'm2':[df['m2'].sum()], 'm3':[df['m3'].sum()]})

df = pd.concat([df, df1], ignore_index=True)

old_index = df.index.to_list()
df.rename(index={old_index[-1]:'Sum'}, inplace=True)
print(df)

OUTPUT:

     size  m1  m2  m3
0       5   5   4   3
1       7   6   6   0
2       4   0   4   2
3       3   3   0   0
4       6   0   0   0
Sum    25  14  14   5

I did not reproduce your index as it was irrelevant.

I personally would put it in a different DF instead of creating a row, but that is a personal choice.

EDITED

I am kinda confuse if you want to calculate upto first or last non NaN value, so here is a few options.

if you want to calculate upto first non nan value then do this:

import pandas as pd

import numpy as np

df = pd.DataFrame({'size':[5,7,4,3,6], 'm1': [5,6, np.nan, 3, np.nan], 'm2': [4,6,4,np.nan,np.nan], 'm3': [3, np.nan, 2, np.nan, np.nan]})

df.dropna(inplace=True)

df1 = pd.DataFrame({'size':[df['size'].sum()], 'm1':[df['m1'].sum()], 'm2':[df['m2'].sum()], 'm3':[df['m3'].sum()]})

df = pd.concat([df, df1], ignore_index=True)

old_index = df.index.to_list()
df.rename(index={old_index[-1]:'Sum'}, inplace=True)
print(df)

OUTPUT:

     size   m1   m2   m3
0       5  5.0  4.0  3.0
Sum     5  5.0  4.0  3.0

That is assuming you cleaned data.

OR

If you want to keep all data but still count only up to first not non value:

import pandas as pd
import numpy as np

df = pd.DataFrame({'size':[5,7,4,3,6], 'm1': [5, 6, np.nan, 3, np.nan], 'm2': [4,6,4,np.nan,np.nan], 'm3': [3, np.nan, 2, np.nan, np.nan]})

df2 = df.copy()

df2.dropna(inplace=True)

df1 = pd.DataFrame({'size':[df2['size'].sum()], 'm1':[df2['m1'].sum()], 'm2':[df2['m2'].sum()], 'm3':[df2['m3'].sum()]})
df = pd.concat([df, df1], ignore_index=True)

old_index = df.index.to_list()
df.rename(index={old_index[-1]:'Sum'}, inplace=True)
print(df)

OUTPUT:

     size   m1   m2   m3
0       5  5.0  4.0  3.0
1       7  6.0  6.0  NaN
2       4  NaN  4.0  2.0
3       3  3.0  NaN  NaN
4       6  NaN  NaN  NaN
Sum     5  5.0  4.0  3.0

Though I still don't get how you got your count.