Finding the slope of the recent three data point from each group

user_10523971 0
Viewed 166

I have a data frame as shown below

    df=pd.DataFrame({'item':['a','a','a','a','a','b','b','b','b'],
                 'date':['2020-03-14','2020-03-21','2020-03-28',
                 '2020-04-04','2020-04-11','2020-08-08','2020-08-15',
                  '2020-08-22','2020-08-29'],
                  'value':[1,2,3,4,6,2,4,8,16]})

I want to create new data frame df_slope as follows

df_output=pd.DataFrame({'item':['a','b'],
              'slope':[1.5,6.0]})

here slope is calculated as round(np.polyfit([1,2,3],[y1,y2,y3], 1)[0],2) where y1,y2,y3 is the latest three data points. How do I create this data frame? Thanks in advance.

Update

How do I handle insufficient data here? i.e if we don't have 3 data points, say just 2 data points I still want to compute the slope.

pandas python
2 Answers

If necessary first sorting data per groups by DataFrame.sort_values and then use GroupBy.agg with custom lambda function and select last 3 values of column value:

f = lambda x: round(np.polyfit([1,2,3],x.tail(3), 1)[0],2)
df = df.sort_values(['item','date']).groupby('item')['value'].agg(f).reset_index()
print (df)
  item  value
0    a    1.5
1    b    6.0

Or:

f = lambda x: round(np.polyfit([1,2,3],x.iloc[-3:], 1)[0],2)
df = df.sort_values(['item','date']).groupby('item')['value'].agg(f).reset_index()
print (df)
  item  value
0    a    1.5
1    b    6.0

EDIT: If need working also for groups with length 2 and 1 use:

def f(x):
    lens = len(x)
    a = np.arange(1, min(lens, 3) + 1)
    return round(np.polyfit(a,x.tail(3), 1)[0],2)
        
 
df = df.sort_values(['item','date']).groupby('item')['value'].agg(f).reset_index()
user_29010020

Assuming your data is sorted correctly you can also use:

list_item = []
list_slope = []
for item in df["item"].unique():
    y1,y2,y3 = df.groupby("item").get_group(item)["value"][-3:]
    list_item.append(item)
    list_slope.append(round(np.polyfit([1,2,3],[y1,y2,y3], 1)[0],2))

df_output = pd.DataFrame({"item": list_item, "slope": list_slope})
user_118888690
Related