df['yes'] = df['WIN'].map(lambda x: 1 if x == 'Yes' else np.nan)
数据框 df2
是数据框 df
的副本,并将列 DATE
设置为索引(用于重新采样)。不必要的列将被删除。
df2 = df.set_index('DATE')
df2 = df2.drop(['NumOfDaysSinceLastWin','WIN', 'WIN1'], axis=1)
然后,如果行为“是”,则将按天重新采样df2
,值为1
,如果为“否”,则值为0
。(更好的解释请参见下文。)
df2 = df2.groupby('ID').resample("D", how='count')
df2 = df2.reset_index()
数据框 df2
将按 ID
进行分组,并对这些组使用函数 rolling_sum
。
df2['rollsum'] = df2.groupby('ID')['yes'].transform(pd.rolling_sum, window=30, min_periods=1)
为了更好地理解df2
的所有行,将显示它们。
with pd.option_context('display.max_rows', 999, 'display.max_columns', 5):
print df2
ID DATE yes rollsum
0 A 2015-06-05 2 2
1 A 2015-06-06 0 2
2 A 2015-06-07 3 5
3 A 2015-06-08 0 5
4 B 2015-06-07 0 0
5 B 2015-06-08 0 0
6 B 2015-06-09 0 0
7 B 2015-06-10 0 0
8 B 2015-06-11 0 0
9 B 2015-06-12 0 0
10 B 2015-06-13 0 0
11 B 2015-06-14 0 0
12 B 2015-06-15 0 0
13 B 2015-06-16 0 0
14 B 2015-06-17 0 0
15 B 2015-06-18 0 0
16 B 2015-06-19 0 0
17 B 2015-06-20 0 0
18 B 2015-06-21 0 0
19 B 2015-06-22 0 0
20 B 2015-06-23 0 0
21 B 2015-06-24 0 0
22 B 2015-06-25 0 0
23 B 2015-06-26 0 0
24 B 2015-06-27 0 0
25 B 2015-06-28 0 0
26 B 2015-06-29 0 0
27 B 2015-06-30 0 0
28 B 2015-07-01 0 0
29 B 2015-07-02 0 0
30 B 2015-07-03 0 0
31 B 2015-07-04 0 0
32 B 2015-07-05 0 0
33 B 2015-07-06 0 0
34 B 2015-07-07 0 0
35 B 2015-07-08 0 0
36 B 2015-07-09 0 0
37 B 2015-07-10 0 0
38 B 2015-07-11 0 0
39 B 2015-07-12 0 0
40 B 2015-07-13 0 0
41 B 2015-07-14 0 0
42 B 2015-07-15 0 0
43 B 2015-07-16 0 0
44 B 2015-07-17 0 0
45 B 2015-07-18 0 0
46 B 2015-07-19 0 0
47 B 2015-07-20 0 0
48 B 2015-07-21 0 0
49 B 2015-07-22 0 0
50 B 2015-07-23 0 0
51 B 2015-07-24 0 0
52 B 2015-07-25 0 0
53 B 2015-07-26 0 0
54 B 2015-07-27 0 0
55 B 2015-07-28 0 0
56 B 2015-07-29 0 0
57 B 2015-07-30 0 0
58 B 2015-07-31 0 0
59 B 2015-08-01 0 0
60 B 2015-08-02 0 0
61 B 2015-08-03 0 0
62 B 2015-08-04 0 0
63 B 2015-08-05 0 0
64 B 2015-08-06 0 0
65 B 2015-08-07 1 1
66 C 2015-05-15 1 1
67 C 2015-05-16 0 1
68 C 2015-05-17 0 1
69 C 2015-05-18 0 1
70 C 2015-05-19 0 1
71 C 2015-05-20 0 1
72 C 2015-05-21 0 1
73 C 2015-05-22 0 1
74 C 2015-05-23 0 1
75 C 2015-05-24 0 1
76 C 2015-05-25 0 1
77 C 2015-05-26 0 1
78 C 2015-05-27 0 1
79 C 2015-05-28 0 1
80 C 2015-05-29 0 1
81 C 2015-05-30 0 1
82 C 2015-05-31 0 1
83 C 2015-06-01 0 1
84 C 2015-06-02 0 1
85 C 2015-06-03 0 1
86 C 2015-06-04 0 1
87 C 2015-06-05 0 1
88 C 2015-06-06 0 1
89 C 2015-06-07 0 1
90 C 2015-06-08 0 1
91 C 2015-06-09 0 1
92 C 2015-06-10 0 1
93 C 2015-06-11 0 1
94 C 2015-06-12 0 1
95 C 2015-06-13 0 1
96 C 2015-06-14 0 0
97 C 2015-06-15 0 0
98 C 2015-06-16 0 0
99 C 2015-06-17 0 0
100 C 2015-06-18 0 0
101 C 2015-06-19 0 0
102 C 2015-06-20 0 0
103 C 2015-06-21 0 0
104 C 2015-06-22 0 0
105 C 2015-06-23 0 0
106 C 2015-06-24 0 0
107 C 2015-06-25 0 0
108 C 2015-06-26 0 0
109 C 2015-06-27 0 0
110 C 2015-06-28 0 0
111 C 2015-06-29 0 0
112 C 2015-06-30 0 0
113 C 2015-07-01 0 0
114 C 2015-07-02 0 0
115 C 2015-07-03 0 0
116 C 2015-07-04 0 0
117 C 2015-07-05 0 0
118 C 2015-07-06 0 0
119 C 2015-07-07 0 0
120 C 2015-07-08 0 0
121 C 2015-07-09 0 0
122 C 2015-07-10 0 0
123 C 2015-07-11 0 0
124 C 2015-07-12 0 0
125 C 2015-07-13 0 0
126 C 2015-07-14 0 0
127 C 2015-07-15 0 0
128 C 2015-07-16 0 0
129 C 2015-07-17 0 0
130 C 2015-07-18 0 0
131 C 2015-07-19 0 0
132 C 2015-07-20 0 0
133 C 2015-07-21 0 0
134 C 2015-07-22 0 0
135 C 2015-07-23 0 0
136 C 2015-07-24 0 0
137 C 2015-07-25 0 0
138 C 2015-07-26 0 0
139 C 2015-07-27 0 0
140 C 2015-07-28 0 0
141 C 2015-07-29 0 0
142 C 2015-07-30 0 0
143 C 2015-07-31 0 0
144 C 2015-08-01 0 0
145 C 2015-08-02 0 0
146 C 2015-08-03 2 2
不必要的列 yes
将被删除。
df2 = df2.drop(['yes'], axis=1 )
输出正在与第一个数据框 df
合并。
df2 = pd.merge(df,df2,on=['DATE', 'ID'], how='inner')
print df2
ID DATE WIN WIN1 NumOfDaysSinceLastWin yes rollsum
0 A 2015-06-07 Yes 1 0 1 2
1 A 2015-06-07 Yes 1 0 1 2
2 B 2015-08-07 No 0 0 NaN 1
3 B 2015-08-07 Yes 1 0 1 1
4 C 2015-05-15 Yes 1 0 1 1
5 C 2015-05-30 No 0 15 NaN 1
6 C 2015-07-30 No 0 76 NaN 0
7 C 2015-08-03 Yes 1 80 1 1
如果列count
中的值不为null
,则它们将被添加到列count
中。函数rolling_sum
计算具有值'YES'
的原始df
中的行数,因此它必须进行减法运算。这些值(1
)位于列WIN1
中。
df2.loc[df['count'].notnull() , 'WIN1'] = df2['count']
df2['NumOfWinsInThePast30days'] = df2['rollsum'] - df2['WIN1']
删除不必要的列。
df2 = df2.drop(['yes','WIN1', 'rollsum', 'count'], axis=1 )
print df2
ID DATE WIN NumOfDaysSinceLastWin NumOfWinsInThePast30days
0 A 2015-06-05 Yes 0 0
1 A 2015-06-05 Yes 0 0
2 A 2015-06-07 Yes 2 2
3 A 2015-06-07 Yes 2 2
4 A 2015-06-07 Yes 2 2
5 A 2015-06-08 No 1 5
6 B 2015-06-07 No 0 0
7 B 2015-06-07 No 0 0
8 B 2015-08-07 Yes 0 0
9 C 2015-05-15 Yes 0 0
10 C 2015-05-30 No 15 1
11 C 2015-07-30 No 76 0
12 C 2015-08-03 Yes 80 0
13 C 2015-08-03 Yes 80 0
最后全部在一起:
import pandas as pd
import numpy as np
import io
temp=u"""ID,DATE,WIN
A,2015/6/5,Yes
A,2015/6/7,Yes
A,2015/6/7,Yes
A,2015/6/8,No
B,2015/6/7,No
B,2015/8/7,Yes
C,2015/5/15,Yes
C,2015/5/30,No
C,2015/7/30,No
C,2015/8/03,Yes"""
temp2=u"""ID,DATE,WIN
A,2015/6/5,Yes
A,2015/6/5,Yes
A,2015/6/7,Yes
A,2015/6/7,Yes
A,2015/6/7,Yes
A,2015/6/8,No
B,2015/6/7,No
B,2015/6/7,No
B,2015/8/7,Yes
C,2015/5/15,Yes
C,2015/5/30,No
C,2015/7/30,No
C,2015/8/03,Yes
C,2015/8/03,Yes"""
df = pd.read_csv(io.StringIO(temp2), parse_dates = [1])
df['WIN1'] = df['WIN'].map(lambda x: 1 if x == 'Yes' else 0)
df['cumsum'] = df['WIN1'].cumsum()
df['min'] = df.groupby(['ID','cumsum'])['DATE'].transform('min')
df['count'] = df.groupby(['cumsum'])['cumsum'].transform('count')
df1 = df[~df['WIN'].isin(['No'])]
df['date1'] = df1.groupby(['ID'])['DATE'].apply(lambda d: d.shift())
print df
df.loc[(df['count'] >= 1) & (df['date1'].isnull()), 'date1'] = df['min']
print df
df['count'] = df1.groupby(['ID', 'DATE', 'WIN1'])['WIN1'].transform('count')
df.loc[df['count'] == 1 , 'count'] = np.nan
df.loc[df['count'].notnull() , 'date2'] = df['date1']
print df
def repeat_value(grp):
grp['date2'] = grp['date2'].min()
return grp
df = df.groupby(['ID', 'DATE']).apply(repeat_value)
df.loc[df['date2'].notnull() , 'date1'] = df['date2']
print df
df['NumOfDaysSinceLastWin'] = (df['DATE'] - df['date1']).astype('timedelta64[D]')
df = df.drop(['cumsum','min','date1', 'date2'], axis=1 )
print df
df['yes'] = df['WIN'].map(lambda x: 1 if x == 'Yes' else np.nan)
df2 = df.set_index('DATE')
df2 = df2.drop(['NumOfDaysSinceLastWin','WIN', 'WIN1','count'], axis=1)
df2 = df2.groupby('ID').resample("D", how='count')
df2 = df2.reset_index()
df2['rollsum'] = df2.groupby('ID')['yes'].transform(pd.rolling_sum, window=30, min_periods=1)
df2 = df2.drop(['yes'], axis=1 )
df2 = pd.merge(df,df2,on=['DATE', 'ID'], how='inner')
print df2
df2.loc[df['count'].notnull() , 'WIN1'] = df2['count']
df2['NumOfWinsInThePast30days'] = df2['rollsum'] - df2['WIN1']
df2 = df2.drop(['yes','WIN1', 'rollsum', 'count'], axis=1 )
print df2
rolling_sum
函数 - http://pandas.pydata.org/pandas-docs/stable/generated/pandas.rolling_sum.html - BulatA
的第三次胜利,NumOfDaysSinceLastWin
不应该是0吗?那个日期与上一次胜利相同。要么这样,要么列的标题应该是NumOfDaysSinceFirstWin
,对吧? - dawg