問題是，對于同一組中的每一行，計算當前行的去年內完成的案例數。數據集 (df) 如下所示：ID Date abc 07/12/16 abc 02/04/17 abc 02/13/17 abc 02/16/19 xyz 11/03/14 xyz 11/06/14 xyz 02/17/16我的想法：首先創建一個列lastyr：df['date'] - timedelta(days=365); 然后將每一行與整個組進行比較，計算組中有多少個日期 >= lastyr 和 < df['date']我試圖在 python 中定義一個函數，如：# Create the dataframed = {'ID': ['abc', 'abc', 'abc', 'abc', 'xyz', 'xyz', 'xyz'], 'Date': ['07/12/16', '02/04/17', '02/13/17', '02/16/19', '11/03/14', '11/06/14', '02/17/16']} df = pd.DataFrame(data=d)df['Date'] = df['Date'].apply(pd.to_datetime)df_1 = df # df_1 is same as df. I tried to compare each row in df to whole column in df_1.# Define and apply the functiondef lastyear(row): curr = row['Date'] lastyr = curr - datetime.timedelta(days=365) if df['ID'] == df_1['ID']: # The compare is for same ID. return (df_1['Date'] < curr) & (df_1['Date'] >= lastyr)df.apply(lastyear, axis=1).groupby(['ID']).count()但是它返回所有錯誤值。我認為這是因為它仍然比較兩個數據框中的每一行，但我不知道如何重寫它以將每一行與整列進行比較。所需的輸出是：Group Date Count # of cases happened in last yearabc 07/12/16 0abc 02/04/17 1abc 02/13/17 2abc 02/16/19 0xyz 11/03/14 0xyz 11/06/14 1xyz 02/17/16 0
2 回答
至尊寶的傳說
TA貢獻1789條經驗 獲得超10個贊
IIUC，這是我的回答：
df['Date'] = pd.to_datetime(df.Date)
df['delta'] = df.groupby('ID')['Date'].diff().dt.days
df['flag'] = (df.groupby('ID').delta.cumsum()<365).astype(int)
group_ids = df.flag.diff().ne(0).cumsum()
df['count'] = df['flag'].groupby([df['ID'], group_ids]).cumsum()
結果：（刪除不相關的列）
ID Date count
0 abc 2016-07-12 0
1 abc 2017-02-04 1
2 abc 2017-02-13 2
3 abc 2019-02-16 0
4 xyz 2014-11-03 0
5 xyz 2014-11-06 1
6 xyz 2016-02-17 0
慕哥9229398
TA貢獻1877條經驗 獲得超6個贊
我只是用稍微修改過的代碼復制了你的邏輯：
....
df['Date'] = pd.to_datetime(df.Date)
def lastyear(row):
curr = row.Date
lastyr = curr - pd.Timedelta(days=365)
return (df[(df.ID == row.ID) & (df.Date > lastyr) & (df.Date < curr)]).ID.size
df['Count'] = df.apply(lastyear, axis=1)
df
#Out[79]:
# ID Date Count
#0 abc 2016-07-12 0
#1 abc 2017-02-04 1
#2 abc 2017-02-13 2
#3 abc 2019-02-16 0
#4 xyz 2014-11-03 0
#5 xyz 2014-11-06 1
#6 xyz 2016-02-17 0
添加回答
舉報
0/150
提交
取消