Average for past 2 months

user_1497720 0
Viewed 524

Given the table below, with only date and number columns given, I want to infer avg_last_2m column (which calculate average value for number column for last 2 months) which is given as follows:

enter image description here

For example, Given the date 20190909, last 2 month will be starting from date 20190709 to date 20190908, within that period, we have date 20190805 (with number =1), therefore average last 2 month would be 1/1=1.0.

Another example would be 20190930, last 2 month will be starting from date 20190730 to date 20190929, we have date 20190805 (with number = 1) and date 20190909 (with number=0), therefore average of last 2 month would be (1+0)/2=0.5.

How should we compute the column avg_last_2m, based on columns date and number? Efficiency is of some important here, as I would have around 100k rows of data in reality.

This is the code for the data frame

test_data=pd.DataFrame({'date':['20190606','20190610','20190708','20190805','20190909','20190930'],'number':[3,5,4,1,0,0],\
                       'avg_last_2m':[None,3,4,4,1,0.5]})
pandas python-3.x python
4 Answers

The issue here is calendar month subtraction. It's not a fixed window (number of days) so rolling isn't simple (or possible?).

It can be done with a full merge, but that quickly becomes impractical if your DataFrame is long. With 100K rows we're up to a 10B row DataFrame. Not impossible, but definitely pushing the limits. There are likely more intelligent algorithms that can do this merge without joining rows that will clearly not be within 2 months.

Setup

import pandas as pd
df = pd.DataFrame({'date':['20190606','20190610','20190708','20190805','20190909','20190930'],
                   'number':[3,5,4,1,0,0]})

df['date'] = pd.to_datetime(df.date, format='%Y%m%d')
#df = df.sort_values('date').reset_index(drop=True)  # Logic below requires sorting

Code

m = df.reset_index().assign(k=1)
m = m.merge(m, on='k').query('index_x > index_y')  # Full merge, no double count

# Only take average of observations within 2 months. 
m = m[m.date_x < (m.date_y + pd.offsets.DateOffset(months=2))].groupby('date_x').number_y.mean()

df['avg_last_2m'] = df.date.map(m)
#        date  number  avg_last_2m
#0 2019-06-06       3          NaN
#1 2019-06-10       5          3.0
#2 2019-07-08       4          4.0
#3 2019-08-05       1          4.0
#4 2019-09-09       0          1.0
#5 2019-09-30       0          0.5

We can trade memory for time with a very slow loop. Probably will take ~10 minutes.

def prev_2m(date, df):
    m = (df.date < date) & (df.date > (date - pd.offsets.DateOffset(months=2)))
    return df.loc[m, 'number'].mean()

df['avg_last_2m'] = df.date.apply(prev_2m, df=df)
user_43333590

I just had to try it, I really don't know, if it is the fastest or best performing way, but it works. Maybe someone has an idea on optimization or even a complete different approach?

import datetime
d = {'date':['20190606','20190610','20190708','20190805','20190909','20190930'],'number':[3,5,4,1,0,0]}
memory_dict = {}
memory_counter = {}
number_out = []
for date, number in reversed(list(zip(d['date'],d['number']))):
    dt = datetime.datetime.strptime(date, '%Y%m%d')
    for mem in list(memory_dict):
        if((mem-dt).days < 60):
            memory_dict[mem] += number
            memory_counter[mem] += 1
        else:
            number_out.append(memory_dict[mem]/memory_counter[mem])
            del memory_dict[mem]
    dt = dt - datetime.timedelta(days=1)
    memory_dict[dt] = 0
    memory_counter[dt] = 0
for mem in memory_dict:
    if (memory_counter[mem] != 0):
        number_out.append(memory_dict[mem]/memory_counter[mem])
    else:
        number_out.append(-1)
number_out.reverse()
print(number_out)

I originally thought, that it might work with list comprehensions, but I couldn't think of a way to do it. The problem just somehow intrigued me and I had to try it.

user_92271880

The below is working for me.

df=pd.DataFrame({'date':['20190606','20190610','20190708','20190805','20190909','20190930'],'number':[3,5,4,1,0,0], 'avg_last_2m':[None,3,4,4,1,0.5]})

df["date"]=pd.to_datetime(df.date, format="%Y%m%d")
df["date_minus_2m"]=df["date"]-pd.DateOffset(months=2)

def avg_2m (row):
    avg_2m = df[(df.date>=row["date_minus_2m"])&(df.date<=row["date"])]["number"].mean()
    return avg_2m
df["avg_2m"]=df.apply(avg_2m, axis=1)
user_90201630

This should do the trick

test_data=pd.DataFrame({'date':pd.to_datetime(['20190606','20190610','20190708','20190805','20190909','20190930']),'number':[3,5,4,1,0,0],\
                       'avg_last_2m':[None,3,4,4,1,0.5]})
offset =pd.offsets.DateOffset(months=2)

mean_k_months = test_data[test_data['date']>max(test_data['date'])-offset]['number'].mean()

In this case you would use the most recent date as orientation for the previous two months but that can be adapted easily.

user_112198250
Related