大熊猫部分数据选择中的问题

Question

我有以下从csv文件中提取的熊猫数据库：

df1=pd.read_csv(path,parse_dates=True) 

df1的打印给出：

                  control      Avg_return  
2019-09-07          True            0    
2019-06-06          True            0
2019-02-19          True            0
2019-01-17          True            0
2018-12-20          True            0
2018-11-27          True            0
2018-10-12          True            0
   ...              ...            ...

在加载2 csv文件之后

df2=pd.read_csv(path,parse_dates=True)

df2的打印给出：

                  return
2010-01-01          NaN
2010-04-01     0.010920
2010-05-01    -0.004404
2010-06-01    -0.025209
2010-07-01    -0.023280
   ...            ...

我的代码的目的是：

1. 从df1
2. 中取一个日期，从第1点中的日期减去6天。
3. 从第1点的日期减去244天。
4. 从df2

H 113计算这些回报的平均值中提取这两个日期的所有返回值，并将其储存在Avg_ return <代码>H 214G 215中。

我做了这个：

for i in range(0,df1_row):                   
#I go through my data df1 

    if (control.iloc[i]==True):                        
#I check if control_1 is true

      date_1=df1.index[i]-pd.to_timedelta(6, unit='d')    
# I remove 6 days from my date

      date_2=df1.index[i]-pd.to_timedelta(244, unit='d')  
# I remove 244 days from my date

      df1.loc[i,"Average_return"] = df2[[date_1:date_2],["return"]].mean()

# I want to make the mean of the return between my date-6 days and my date-244 days

不幸的是，它给了我这个错误：

df1.loc[i,"Average_return"] = df2[[date1:date2],["return"]].mean()
                                        ^
SyntaxError: invalid syntax

有人能帮我吗？)

Answer 1

这是一种不同的方法，无需遍历所有行：

# make sure your index is a datetime index
df1.index = pd.to_datetime(df1.index)    

df1['date_1'] = df1.index - pd.to_timedelta(6, unit='d') 
df1['date_2'] = df1.index  - pd.to_timedelta(244, unit='d') 

df1['Average_return'] = df1.apply(lambda r: df2.loc[r['date_1']: r['date_2'], 'return'].mean(), axis=1)

Answer 2

以下内容看上去有点难看，但我认为效果很好:)

假df：

import numpy as np
import pandas as pd    

cols = ['date', 'control', 'Avg_return']
data = [
    [pd.to_datetime('2019-09-07'), True, 0], 
    [pd.to_datetime('2019-06-06'), True, 0]
]
df1 = pd.DataFrame(data, columns=cols)

cols2 = ['date', 'return']
data2 = [
    [pd.to_datetime('2010-01-01'), np.nan], 
    [pd.to_datetime('2010-04-01'), 0.010920], 
    [pd.to_datetime('2019-09-01'), 1]
]
df2 = pd.DataFrame(data2, columns=cols2)

起草的解决办法：

df1['date_minus_6'] = df1['date'] - dt.timedelta(days=6)
df1['date_minus_244'] = df1['date'] - dt.timedelta(days=244)

for i in range(0, df1.shape[0]):
    for j in range(0, df2.shape[0]):
        if df2['date'].iloc[j] == df1['date_minus_6'].iloc[i]:
               df1['Avg_return'].iloc[i] = (
                   df1['Avg_return'].iloc[i] + df2['return'].iloc[j]
               ).mean()
        elif df2['date'].iloc[j] == df1['date_minus_244'].iloc[i]:
               df1['Avg_return'].iloc[i] = (
                   df1['Avg_return'].iloc[i] + df2['return'].iloc[j]
               ).mean()

输出：

    date    control Avg_return  date_minus_6    date_minus_244
0   2019-09-07  True    1.0 2019-09-01  2019-01-06
1   2019-06-06  True    0.0 2019-05-31  2018-10-05

Answer 3

import csv
import pandas as pd

df1=pd.read_csv('dsf1.csv',parse_dates=True)
df2=pd.read_csv('dsf2.csv',parse_dates=True)

df1.columns = ['date', 'control', 'return']
df2.columns = ['date', 'return']

df1['date'] = pd.to_datetime(df1['date'])
df2['date'] = pd.to_datetime(df2['date'])
for i in range(0, df1.shape[0]):
    if df1['control'][i] == True:
        date_1 = df1['date'][0] - pd.to_timedelta(6, unit='d')
        date_2 = df2['date'][0] - pd.to_timedelta(244, unit='d')
        #I'm not sure if average_return has the correct condition, but adjust as you see fit
        df1.loc[i, 'average_return'] = (df1[df1['date'] > date_1]['return'] -  df2[df2['date'] > date_2]['return']).mean()
print df1