使用 pandas 将每日数据重新采样到每月一次(日期格式) [英] Resample Daily Data to Monthly with Pandas (date formatting)

问题描述

我正尝试在Pandas DataFrame中每天对一些数据进行重新采样.我是熊猫的新手，也许我需要先格式化日期和时间，然后才能执行此操作，但是我找不到关于如何使用导入的时间序列数据的正确方法的良好教程.我发现的一切都是自动从Yahoo或Quandl导入数据.

I am trying to resample some data from daily to monthly in a Pandas DataFrame. I am new to pandas and maybe I need to format the date and time first before I can do this, but I am not finding a good tutorial out there on the correct way to work with imported time series data. Everything I find is automatically importing data from Yahoo or Quandl.

这是我在DataFrame中拥有的内容: 数据框细分屏幕截图

Here is what I have in my DataFrame: dataframe segment screenshot

这是我用来创建DataFrame的代码:

Here is the code I used to create my DataFrame:

#Import excel file into a Pandas DataFrame
df = pd.read_excel(open('2016_forex_daily_returns.xlsx','rb'), sheetname='Sheet 1')

#Calculate the daily returns
df['daily_ret'] = df['Equity'].pct_change()

# Assume an average annual risk-free rate over the period of 5%
df['excess_daily_ret'] = df['daily_ret'] - 0.05/252

有人可以帮助我了解我对DataFrame中日期"和时间"列的处理方式，以便我可以重新采样吗?

Can someone help me understand what I need to do with the "Date" and "Time" columns in my DataFrame so I can resample?

推荐答案

要创建DataFrame，可以使用:

df = pd.read_excel('2016_forex_daily_returns.xlsx', sheetname='Sheet 1')
print (df)
        Date      Time  Equity
0 2016-01-03  22:16:22  300.38
1 2016-01-04  22:16:00  300.65
2 2016-01-05  14:26:02  301.65
3 2016-01-06  19:08:13  302.10
4 2016-01-07  18:39:00  302.55
5 2016-01-08  22:16:04  308.24
6 2016-01-11  02:49:39  306.69
7 2016-01-14  15:46:39  307.93
8 2016-01-19  15:56:31  308.18

我认为您可以先投射 to_datetime 列date，然后使用 resample 具有一些汇总功能，例如sum或mean:

I think you can first cast to_datetime column date and then use resample with some aggregating functions like sum or mean:

df.Date = pd.to_datetime(df.Date)
df1 = df.resample('M', on='Date').sum()
print (df1)
             Equity  excess_daily_ret
Date                                 
2016-01-31  2738.37          0.024252

df2 = df.resample('M', on='Date').mean()
print (df2)
                Equity  excess_daily_ret
Date                                    
2016-01-31  304.263333          0.003032

df3 = df.set_index('Date').resample('M').mean()
print (df3)
                Equity  excess_daily_ret
Date                                    
2016-01-31  304.263333          0.003032

这篇关于使用 pandas 将每日数据重新采样到每月一次(日期格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！