您如何清洁并向前填充 pandas 一天多一分钟的时间序列? [英] How do you clean and forward fill a multiple day 1 minute time series with pandas?

问题描述

我有一个csv文件，其中包含1天多天的库存数据.每天从9:30到16:00.

I have a csv file with 1 minute stock data spanning multiple days. Each day runs from 9:30 to 16:00.

缺少时间序列中的某些分钟: (此处缺少2013-09-16 09:32:00和2013-09-17 09:31:00)

Some of the minutes in the time series are missing: (here 2013-09-16 09:32:00 and 2013-09-17 09:31:00 are missing)

2013-09-16 09:30:00,461.01,461.49,461,461,183507
2013-09-16 09:31:00,460.82,461.6099,460.39,461.07,212774
2013-09-16 09:33:00,460.0799,460.88,458.97,459.2401,207880
2013-09-16 09:34:00,458.97,460.08,458.8,460.04,148121
...
2013-09-16 15:59:00,449.72,450.0774,449.59,449.95,146399
2013-09-16 16:00:00,450.12,450.12,449.65,449.65,444594
2013-09-17 09:30:00,448,448,447.5,447.96,173624
2013-09-17 09:32:00,450.6177,450.9,449.05,449.2701,268715
2013-09-17 09:33:00,451.39,451.96,450.58,450.7061,197019
...
...

对于大熊猫，我该如何向前填充系列，以便每分钟都在场?我应该看起来像这样:

With pandas, how do I forward fill the series so every minute is present? I should look like this:

2013-09-16 09:30:00,461.01,461.49,461,461,183507
2013-09-16 09:31:00,460.82,461.6099,460.39,461.07,212774
2013-09-16 09:32:00,460.82,461.6099,460.39,461.07,212774 <-- forward filled
2013-09-16 09:33:00,460.0799,460.88,458.97,459.2401,207880
2013-09-16 09:34:00,458.97,460.08,458.8,460.04,148121
...
2013-09-16 15:59:00,449.72,450.0774,449.59,449.95,146399
2013-09-16 16:00:00,450.12,450.12,449.65,449.65,444594
2013-09-17 09:30:00,448,448,447.5,447.96,173624
2013-09-17 09:31:00,448,448,447.5,447.96,173624 <-- forward filled
2013-09-17 09:32:00,450.6177,450.9,449.05,449.2701,268715
2013-09-17 09:33:00,451.39,451.96,450.58,450.7061,197019
...

还需要考虑是否缺少多个连续分钟...

It also needs to account for if multiple consecutive minutes are missing...

推荐答案

所以我将前4行复制到了数据框中:

So I copied your first 4 lines into a dataframe:

Out[49]:
                    0         1         2       3         4       5
0 2013-09-16 09:30:00  461.0100  461.4900  461.00  461.0000  183507
1 2013-09-16 09:31:00  460.8200  461.6099  460.39  461.0700  212774
2 2013-09-16 09:33:00  460.0799  460.8800  458.97  459.2401  207880
3 2013-09-16 09:34:00  458.9700  460.0800  458.80  460.0400  148121

然后

df1 = df.set_index(keys=[0]).resample('1min', fill_method='ffill')
df1

Out[52]:
                            1         2       3         4       5
0                                                                
2013-09-16 09:30:00  461.0100  461.4900  461.00  461.0000  183507
2013-09-16 09:31:00  460.8200  461.6099  460.39  461.0700  212774
2013-09-16 09:32:00  460.8200  461.6099  460.39  461.0700  212774
2013-09-16 09:33:00  460.0799  460.8800  458.97  459.2401  207880
2013-09-16 09:34:00  458.9700  460.0800  458.80  460.0400  148121

这还将处理多个缺失值并向前填充它们.

This will also deal with multiple missing values and forward fill them.

所以，如果我有类似数据

So if I have data like

2013-09-17 09:30:00,448,448,447.5,447.96,173624
2013-09-17 09:33:00,451.39,451.96,450.58,450.7061,197019

并执行与之前给出的相同的操作:

and do the same thing as before this gives:

Out[55]:
                          1       2       3         4       5
0                                                            
2013-09-17 09:30:00  448.00  448.00  447.50  447.9600  173624
2013-09-17 09:31:00  448.00  448.00  447.50  447.9600  173624
2013-09-17 09:32:00  448.00  448.00  447.50  447.9600  173624
2013-09-17 09:33:00  451.39  451.96  450.58  450.7061  197019

这里的关键是您必须具有datetimeindex，如果要将其保留为列，则只需在set_index中设置drop=False.

The key thing here is you must have a datetimeindex, if you want to keep it as a column then you can just set drop=False in set_index.

这篇关于您如何清洁并向前填充 pandas 一天多一分钟的时间序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！