Qfyilyi

I have a dataframe df looks like the following. I want to calculate the average of the last 3 non nan columns. If there are less than three non-missing columns then the average number is missing.

name day1 day2 day3 day4  day5 day6 day7

A    1     1   nan   2    3    0   3

B    nan   nan nan   nan  nan  nan 3

C    1     1   0     1    1    1   1

D    1     1   0     1    nan  1   4

The expect output should looks like the following

name day1 day2 day3 day4  day5 day6 day7    expected 

A    1     1   nan   2    3    0   3        2     <-  1/3*(day5 + day6 + day7)

B    nan   nan nan   nan  nan  nan 3        nan   <-  less than 3 non-missing

C    1     1   0     1    1    1   1        1     <-  1/3*(day5 + day6 + day7)

D    1     1   0     1    nan  1   4        2    <-  1/3 *(day4 + day6 + day7)

I know how to calculate the average of the last three column and count how many non-missing observation are there.
df.iloc[:, 5:7].count(axis=1) average of the last three column
df.iloc[:, 5:7].count(axis=1) number of non-nan in the last three column

If there are less than 3 non-missing observation, I know how to set the average value to missing using df.iloc[:, 1:7].count(axis=1) <= 3.

But I am struggling to find a way to calculate the average of the last three non-missing columns. Can anyone teach me how to solve this please?

Vectorized one using justify -

N = 3 # last N entries for averaging

avg = np.mean(justify(df.values,invalid_val=np.nan,axis=1, side='right')[:,-N:],1)

df['expected'] = avg

You can use pd.DataFrame.apply with a custom function. This is only partially vectorised.

def mean_calculator(row):

    non_nulls = row.notnull()

    if non_nulls.sum() < 3:

        return np.nan

    return row[non_nulls].values[-3:].mean()



df['expected'] = df.iloc[:, 1:].apply(mean_calculator, axis=1)



print(df)



  name  day1  day2  day3  day4  day5  day6  day7  expected

0    A   1.0   1.0   NaN   2.0   3.0   0.0     3       2.0

1    B   NaN   NaN   NaN   NaN   NaN   NaN     3       NaN

2    C   1.0   1.0   0.0   1.0   1.0   1.0     1       1.0

3    D   1.0   1.0   0.0   1.0   NaN   1.0     4       2.0

You can start by calculating the expected column using applying the following function:

expected = df.apply(lambda x: x[~x.isnull()][-3:].mean(), axis = 1)

And insert these values in the columns that have at least 3 valid values:

m = df.isnull().sum(axis=1) > 3

df.loc[~m,'expected'] = expected.mask(m)



       day1  day2  day3  day4  day5  day6  day7  expected

name                                                    

A      1.0   1.0   NaN   2.0   3.0   0.0     3       2.0

B      NaN   NaN   NaN   NaN   NaN   NaN     3       NaN

C      1.0   1.0   0.0   1.0   1.0   1.0     1       1.0

D      1.0   1.0   0.0   1.0   NaN   1.0     4       2.0