how to ignore nan values and calculate mean of last 3 months

Question

I have uploaded 6 months data Jul - Dec

This is my dictionary data

print(UPI_TransferIn)

output:

[{'UPI TransferIn amt': 3000.0, 'date': '11-10-2018', 'No of UPI Transaction': 1}, {'UPI TransferIn amt': 560.0, 'date': '25-09-2018', 'No of UPI Transaction': 1}, {'UPI TransferIn amt': 3000.0, 'date': '14-09-2018', 'No of UPI Transaction': 1}, {'UPI TransferIn amt': 6984.0, 'date': '09-07-2018', 'No of UPI Transaction': 1}]

My Code

    Avg_per_month = df.groupby(pd.Grouper(key='date', freq='1M')).mean()                                                             
    Avg_of_3_Month = df.groupby(pd.Grouper(key='date', freq='1M')).mean().last("3M")
    total_Avg_of_3_Months = Avg_of_3_Month['UPI TransferIn amt'].mean()              

    print("\nAverage UPI Transaction-In per month :\n ", Avg_per_month)                            
    print("Total Average UPI Transaction-In in last 3 months : ", total_Avg_of_3_Months)    

Result I got:

--------------

Average UPI Transaction-In per month :
          No of UPI Transaction  UPI TransferIn amt
Date                                             
Jul-18                      1              6984.0
Aug-18                      0                 NaN
Sep-18                      2              1780.0
Oct-18                      1              3000.0
--------------
Total Average UPI Transaction-In in last 3 months :  2390.0
--------------

I want the average to be calculated between Jul, Sep, Oct currently Its been calculated for Sep and Oct even when I wrote last("3M").

Expected result:

--------------
Total Average UPI Transaction-In in last 3 months :  3921.33333
--------------

Answer 1

Use dropna for remove NaNs rows and get last 3 by tail:

Avg_of_3_Month = (df.groupby(pd.Grouper(key='date', freq='1M'))
                    .mean()
                    .dropna(subset=['UPI TransferIn amt'])
                    .tail(3))

total_Avg_of_3_Months = Avg_of_3_Month['UPI TransferIn amt'].mean()