python - Pandas 在回溯窗口中找到每个用户的第一个订单的最小日期

Question

对于每个用户，我希望找到他们最早访问的日期，该日期位于自首次订购日期起的 90 天回溯窗口内。

data = {"date":{"145586":"2016-08-02","247940":"2016-10-04","74687":"2017-01-05","261739":"2016-10-05","121154":"2016-10-07","82658":"2016-12-01","196680":"2016-12-06","141277":"2016-12-15","189763":"2016-12-18","201564":"2016-12-20","108930":"2016-12-23"},"fullVisitorId":{"145586":643786734868244401,"247940":7634897085866546110,"74687":7634897085866546110,"261739":7634897085866546110,"121154":7634897085866546110,"82658":7634897085866546110,"196680":7634897085866546110,"141277":7634897085866546110,"189763":643786734868244401,"201564":643786734868244401,"108930":7634897085866546110},"sessionId":{"145586":"0643786734868244401_1470168779","247940":"7634897085866546110_1475590935","74687":"7634897085866546110_1483641292","261739":"7634897085866546110_1475682997","121154":"7634897085866546110_1475846055","82658":"7634897085866546110_1480614683","196680":"7634897085866546110_1481057822","141277":"7634897085866546110_1481833373","189763":"0643786734868244401_1482120932","201564":"0643786734868244401_1482246921","108930":"7634897085866546110_1482521314"},"orderNumber":{"145586":0.0,"247940":0.0,"74687":1.0,"261739":0.0,"121154":0.0,"82658":0.0,"196680":0.0,"141277":0.0,"189763":1.0,"201564":0.0,"108930":0.0}}

test = pd.DataFrame(data=data)
test.date = pd.to_datetime(test.date)

lookback = test[test['orderNumber']==1]['date'].apply(lambda x: x - timedelta(days=90))
lookback.name = 'window_min'
ids = test['fullVisitorId']
ids = ids.reset_index()
ids = ids.set_index('index')
lookback = lookback.reset_index()
lookback['fullVisitorId'] = lookback['index'].map(ids['fullVisitorId'])
lookback = lookback.set_index('fullVisitorId')
test['window'] = test['fullVisitorId'].map(lookback['window_min'])
test = test[test['window']<test['date']]
test.loc[test.groupby('fullVisitorId')['date'].idxmin()]

这可行，但我觉得应该有一种更干净的方法......

Answer 1

这个怎么样？基本上，我们分配一个新列(order-90days)来帮助我们过滤掉那些错误的。

我们应用 groupby 并选择第 1 个(第 0-n 个)元素。

import pandas as pd

data = {"date":{"145586":"2016-08-02","247940":"2016-10-04","74687":"2017-01-05","261739":"2016-10-05","121154":"2016-10-07","82658":"2016-12-01","196680":"2016-12-06","141277":"2016-12-15","189763":"2016-12-18","201564":"2016-12-20","108930":"2016-12-23"},"fullVisitorId":{"145586":643786734868244401,"247940":7634897085866546110,"74687":7634897085866546110,"261739":7634897085866546110,"121154":7634897085866546110,"82658":7634897085866546110,"196680":7634897085866546110,"141277":7634897085866546110,"189763":643786734868244401,"201564":643786734868244401,"108930":7634897085866546110},"sessionId":{"145586":"0643786734868244401_1470168779","247940":"7634897085866546110_1475590935","74687":"7634897085866546110_1483641292","261739":"7634897085866546110_1475682997","121154":"7634897085866546110_1475846055","82658":"7634897085866546110_1480614683","196680":"7634897085866546110_1481057822","141277":"7634897085866546110_1481833373","189763":"0643786734868244401_1482120932","201564":"0643786734868244401_1482246921","108930":"7634897085866546110_1482521314"},"orderNumber":{"145586":0.0,"247940":0.0,"74687":1.0,"261739":0.0,"121154":0.0,"82658":0.0,"196680":0.0,"141277":0.0,"189763":1.0,"201564":0.0,"108930":0.0}}

test = pd.DataFrame(data=data)
test.date = pd.to_datetime(test.date)
test.sort_values(by='date', inplace=True)

firstorder = test[test.orderNumber > 0].set_index('fullVisitorId').date
test['firstorder_90'] = test.fullVisitorId.map(firstorder - pd.Timedelta(days=90))

test.query('date >= firstorder_90').groupby('fullVisitorId', as_index=False).nth(0)

我们得到:

             date        fullVisitorId                       sessionId  \
121154 2016-10-07  7634897085866546110  7634897085866546110_1475846055   
189763 2016-12-18   643786734868244401  0643786734868244401_1482120932   

        orderNumber firstorder_90  
121154          0.0    2016-10-07  
189763          1.0    2016-09-19