将 PANDAS 数据框从每月转换为每天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29612705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting PANDAS dataframe from monthly to daily
提问by Gregory Saxton
I have a data frame with monthly data for 2014 for a series of 317 stock tickers (317 tickers x 12 months = 3,804 rows in DF). I would like to convert it to a daily dataframe (317 tickers x 365 days = 115,705 rows). So, I believe I need to upsample or reindex while spreading the monthly values over every day in the month, but I can't get it to work properly.
我有一个包含 2014 年月度数据的数据框，其中包含一系列 317 个股票代码（317 个股票代码 x 12 个月 = DF 中的 3,804 行）。我想将其转换为每日数据帧（317 个代码 x 365 天 = 115,705 行）。因此，我相信我需要在将每月值分散到该月的每一天时进行上采样或重新索引，但我无法使其正常工作。
The dataframe is currently in this format:
数据框目前采用以下格式：
>>> df
month ticker b c
2014-1 AAU 10 .04 #different values every month for each ticker
2014-2 AAU 20 .03
2014-3 AAU 13 .06
.
2014-12 AAU 11 .03
.
.
.
2014-1 ZZY 11 .11
2014-2 ZZY 6 .03
.
2014-12 ZZY 17 .09
And this is what I'd like:
这就是我想要的：
>>> df
day ticker b c
2014-01-01 AAU 10 .04 #same values every day in month for each ticker
2014-01-02 AAU 10 .04
2014-01-03 AAU 10 .04
.
2014-01-31 AAU 10 .04
2014-02-01 AAU 20 .03
2014-02-02 AAU 20 .03
.
2014-02-28 AAU 20 .03
.
.
.
2014-12-30 ZZY 17 .09
2014-12-31 ZZY 17 .09
I have tried doing a groupby combined with resampling by day, but the updated dataframe will start with the date '2014-01-13' rather than January 1st, and end with '2014-12-01' rather than December 31st. I have also tried to change the month values from, for instance, '2014-1' to '2014-01-01', etc., but the resampled dataframe still ends on '2014-01-01'. There has to be an easier way to go about this, so I'd appreciate any help. I've been going around in circles all day on this.
我曾尝试将 groupby 与按天重新采样相结合，但更新后的数据框将从日期“2014-01-13”而不是 1 月 1 日开始，并以“2014-12-01”而不是 12 月 31 日结束。我还尝试将月份值从“2014-1”更改为“2014-01-01”等，但重新采样的数据帧仍以“2014-01-01”结束。必须有一个更简单的方法来解决这个问题，所以我很感激任何帮助。我一整天都在绕圈子。
回答by unutbu
First, parse the month-datestrings into Pandas timestamps:
首先，将月份日期字符串解析为 Pandas 时间戳：
df['month'] = pd.to_datetime(df['month'], format='%Y-%m')
# month ticker b c
# 0 2014-01-01 AAU 10 0.04
# 1 2014-02-01 AAU 20 0.03
# 2 2014-03-01 AAU 13 0.06
# 3 2014-12-01 AAU 11 0.03
# 4 2014-01-01 ZZY 11 0.11
# 5 2014-02-01 ZZY 6 0.03
# 6 2014-12-01 ZZY 17 0.09
Next, pivot the DataFrame, using the month as the index and the ticker as a column level:
接下来，旋转 DataFrame，使用月份作为索引，将股票代码作为列级别：
df = df.pivot(index='month', columns='ticker')
# b c
# ticker AAU ZZY AAU ZZY
# month
# 2014-01-01 10 11 0.04 0.11
# 2014-02-01 20 6 0.03 0.03
# 2014-03-01 13 NaN 0.06 NaN
# 2014-12-01 11 17 0.03 0.09
By pivoting now, we will be able to forward-fill each column more easily later.
通过现在旋转，我们将能够在以后更轻松地向前填充每一列。
Now find the start and end dates:
现在找到开始和结束日期：
start_date = df.index.min() - pd.DateOffset(day=1)
end_date = df.index.max() + pd.DateOffset(day=31)
Interestingly, note that adding pd.DateOffset(day=31)will not always result in a date that ends on day 31. If the month is February, adding pd.DateOffset(day=31)returns the last day in February:
有趣的是，请注意添加pd.DateOffset(day=31)并不总是导致日期在第 31 天结束。如果月份是二月，添加pd.DateOffset(day=31)返回二月的最后一天：
In [130]: pd.Timestamp('2014-2-28') + pd.DateOffset(day=31)
Out[130]: Timestamp('2014-02-28 00:00:00')
That's nice, since that means adding pd.DateOffset(day=31)will always give us the last valid day in the month.
这很好，因为这意味着添加pd.DateOffset(day=31)将始终为我们提供当月的最后一个有效日期。
Now we can reindex and forward-fill the DataFrame:
现在我们可以重新索引和前向填充 DataFrame：
dates = pd.date_range(start_date, end_date, freq='D')
dates.name = 'date'
df = df.reindex(dates, method='ffill')
which yields
这产生
In [160]: df.head()
Out[160]:
b c
ticker AAU ZZY AAU ZZY
date
2014-01-01 10 11 0.04 0.11
2014-01-02 10 11 0.04 0.11
2014-01-03 10 11 0.04 0.11
2014-01-04 10 11 0.04 0.11
2014-01-05 10 11 0.04 0.11
In [161]: df.tail()
Out[161]:
b c
ticker AAU ZZY AAU ZZY
date
2014-12-27 11 17 0.03 0.09
2014-12-28 11 17 0.03 0.09
2014-12-29 11 17 0.03 0.09
2014-12-30 11 17 0.03 0.09
2014-12-31 11 17 0.03 0.09
To move the ticker out of the column index and back into a column:
要将代码移出列索引并移回列中：
df = df.stack('ticker')
df = df.sortlevel(level=1)
df = df.reset_index()
So putting it all together:
所以把它们放在一起：
import pandas as pd
df = pd.read_table('data', sep='\s+')
df['month'] = pd.to_datetime(df['month'], format='%Y-%m')
df = df.pivot(index='month', columns='ticker')
start_date = df.index.min() - pd.DateOffset(day=1)
end_date = df.index.max() + pd.DateOffset(day=31)
dates = pd.date_range(start_date, end_date, freq='D')
dates.name = 'date'
df = df.reindex(dates, method='ffill')
df = df.stack('ticker')
df = df.sortlevel(level=1)
df = df.reset_index()
yields
产量
In [163]: df.head()
Out[163]:
date ticker b c
0 2014-01-01 AAU 10 0.04
1 2014-01-02 AAU 10 0.04
2 2014-01-03 AAU 10 0.04
3 2014-01-04 AAU 10 0.04
4 2014-01-05 AAU 10 0.04
In [164]: df.tail()
Out[164]:
date ticker b c
450 2014-12-27 ZZY 17 0.09
451 2014-12-28 ZZY 17 0.09
452 2014-12-29 ZZY 17 0.09
453 2014-12-30 ZZY 17 0.09
454 2014-12-31 ZZY 17 0.09
回答by pitcheverlasting
Let's do a synthetic experiment. Say we have a daily time series data:
让我们做一个合成实验。假设我们有一个每日时间序列数据：
dates = pd.date_range(start, end, freq='D')
ts = pd.Series(data, index=dates)
Generate a monthly time series by averaging all data within a month:
通过平均一个月内的所有数据生成月度时间序列：
ts_mon = ts.resample('MS', how='mean')
Now try to upsample this monthly time series back to daily time series, with uniform values within a month. The first method that borrows a step from @unutbu using reindex work well:
现在尝试将这个月度时间序列上采样回每日时间序列，在一个月内具有统一的值。使用 reindex 从@unutbu 借用步骤的第一种方法效果很好：
ts_daily = ts_mon.reindex(dates, method='ffill')
Out:
2000-01-01 100.21
2000-01-02 100.21
...
2000-12-30 80.75
2000-12-31 80.75
The second method using resample doesn't work, as it returns the first day of the last month:
使用 resample 的第二种方法不起作用，因为它返回上个月的第一天：
ts_daily = ts_mon.resample('D').ffill()
Out:
2000-01-01 100.21
2000-01-02 100.21
...
2000-11-30 99.33
2000-12-01 80.75
