问如果date介于2个日期之间，则列中的Python Pandas总和值
EN

Stack Overflow用户

提问于 2018-01-04 21:28:34

回答 3查看 12.5K关注 0票数 8

我有一个数据帧df，它可以用下面的命令创建：

data={'id':[1,1,1,1,2,2,2,2],
      'date1':[datetime.date(2016,1,1),datetime.date(2016,1,2),datetime.date(2016,1,3),datetime.date(2016,1,4),
               datetime.date(2016,1,2),datetime.date(2016,1,4),datetime.date(2016,1,3),datetime.date(2016,1,1)],
      'date2':[datetime.date(2016,1,5),datetime.date(2016,1,3),datetime.date(2016,1,5),datetime.date(2016,1,5),
               datetime.date(2016,1,4),datetime.date(2016,1,5),datetime.date(2016,1,4),datetime.date(2016,1,1)],
      'score1':[5,7,3,2,9,3,8,3],
      'score2':[1,3,0,5,2,20,7,7]}
df=pd.DataFrame.from_dict(data)

And looks like this:
   id       date1       date2  score1  score2
0   1  2016-01-01  2016-01-05       5       1
1   1  2016-01-02  2016-01-03       7       3
2   1  2016-01-03  2016-01-05       3       0
3   1  2016-01-04  2016-01-05       2       5
4   2  2016-01-02  2016-01-04       9       2
5   2  2016-01-04  2016-01-05       3      20
6   2  2016-01-03  2016-01-04       8       7
7   2  2016-01-01  2016-01-01       3       7

我需要做的是分别为score1和score2创建一个列，该列根据usedate是否在date1和date2之间创建两个列，这两个列分别将score1和score2的值相加。通过获取介于date1最小值和date2最大值之间的所有日期来创建usedate。我使用它来创建日期范围：

drange=pd.date_range(df.date1.min(),df.date2.max())

生成的dataframe newdf应该如下所示：

     usedate  score1sum  score2sum
0 2016-01-01          8          8
1 2016-01-02         21          6
2 2016-01-03         32         13
3 2016-01-04         30         35
4 2016-01-05         13         26

为了澄清起见，在usedate 2016-01-01上，score1sum是8，这是通过查看df中的行来计算的，其中2016-01-01介于date1和date2之间，其中包括row0(5)和row8(3)。在usedate 2016-01-04上，score2sum为35，这是通过查看df中的行计算得出的，其中2016-01-04位于date1和date2之间(包括它们)，它们的总和为row0(1)、row3(0)、row4(5)、row5(2)、row6(20)、row7(7)。

也许是某种groupby，或者是melt，然后是groupby

python

pandas

dataframe

pandas-groupby

melt

回答 3

Stack Overflow用户

回答已采纳

发布于 2018-01-05 04:01:31

您可以将apply与lambda函数一起使用：

df['date1'] = pd.to_datetime(df['date1'])

df['date2'] = pd.to_datetime(df['date2'])

df1 = pd.DataFrame(index=pd.date_range(df.date1.min(), df.date2.max()), columns = ['score1sum', 'score2sum'])

df1[['score1sum','score2sum']] = df1.apply(lambda x: df.loc[(df.date1 <= x.name) & 
                                                            (x.name <= df.date2),
                                                            ['score1','score2']].sum(), axis=1)

df1.rename_axis('usedate').reset_index()

输出：

     usedate  score1sum  score2sum
0 2016-01-01          8          8
1 2016-01-02         21          6
2 2016-01-03         32         13
3 2016-01-04         30         35
4 2016-01-05         13         26

票数 6

Stack Overflow用户

发布于 2018-01-05 03:44:20

方法1:列表理解

这是不优雅的，但嘿，它是有效的！(编辑:在下面添加了第二种方法。)

# Convert datetime.date to pandas timestamps for easier comparisons
df['date1'] = pd.to_datetime(df['date1'])
df['date2'] = pd.to_datetime(df['date2'])

# solution
newdf = pd.DataFrame(data=drange, columns=['usedate'])
# for each usedate ud, get all df rows whose dates contain ud,
# then sum the scores of these rows
newdf['score1sum'] = [df[(df['date1'] <= ud) & (df['date2'] >= ud)]['score1'].sum() for ud in drange]
newdf['score2sum'] = [df[(df['date1'] <= ud) & (df['date2'] >= ud)]['score2'].sum() for ud in drange]

# output
newdf
     usedate  score1sum  score2sum
  2016-01-01          8          8
  2016-01-02         21          6
  2016-01-03         32         13
  2016-01-04         30         35
  2016-01-05         13         26

方法2:一个带有transform (或apply)的助手函数

newdf = pd.DataFrame(data=drange, columns=['usedate'])

def sum_scores(d):
    return df[(df['date1'] <= d) & (df['date2'] >= d)][['score1', 'score2']].sum()

# apply works here too, and is about equally fast in my testing
newdf[['score1sum', 'score2sum']] = newdf['usedate'].transform(sum_scores)

# newdf is same to above

计时是可比较的

# Jupyter timeit cell magic
%%timeit 
newdf['score1sum'] = [df[(df['date1'] <= d) & (df['date2'] >= d)]['score1'].sum() for d in drange]
newdf['score1sum'] = [df[(df['date1'] <= d) & (df['date2'] >= d)]['score2'].sum() for d in drange]

100 loops, best of 3: 10.4 ms per loop

# Jupyter timeit line magic
%timeit newdf[['score1sum', 'score2sum']] = newdf['usedate'].transform(sum_scores) 

100 loops, best of 3: 8.51 ms per loop

票数 1

Stack Overflow用户

发布于 2021-10-17 03:11:22

来自pyjanitor的conditional_join在抽象/便利性方面可能很有帮助：

# pip install pyjanitor
import pandas as pd
import janitor as jn

drange = pd.DataFrame(drange, columns=['dates'])
df['date1'] = pd.to_datetime(df['date1'])
df['date2'] = pd.to_datetime(df['date2'])

(drange.conditional_join(df, 
                         ('dates', 'date1', '>='), 
                         ('dates', 'date2', '<='))
.droplevel(0, 1)
.select_columns('dates', 'score*')
.groupby('dates')
.sum()
.add_suffix('num')
) 
            score1num  score2num
dates                           
2016-01-01          8          8
2016-01-02         21          6
2016-01-03         32         13
2016-01-04         30         35
2016-01-05         13         26