在 Pandas 中合并/组合两个具有不同频率时间序列索引的数据帧?

问题描述

使用熊猫 0.15.1.假设我有以下两个数据框:

Using pandas 0.15.1. Suppose I have the following two dataframes:

daily
2014-11-20 00:00:00 Rain
2014-11-21 00:00:00 Cloudy
2014-11-22 00:00:00 Sunny

.

minutely
2014-11-20 12:45:00     51
2014-11-20 12:46:00     43
2014-11-20 12:47:00     44
...
2014-11-21 12:45:00     44
2014-11-21 12:46:00     46
2014-11-21 12:47:00     48
...
2014-11-22 12:45:00     38
2014-11-22 12:46:00     32
2014-11-22 12:47:00     37

我想组合这两个数据框，以便将日期值传播到具有相应日期的每一分钟行.

I'd like to combine the two dataframes such that the day values get propagated to each minute row that have the corresponding day.

由于分钟行在 00:00:00 实际上没有数据，我不希望该时间包含在结果数据框中.期望的输出:

And since the minute rows do not actually have data at 00:00:00 I do not want that time included in the resulting dataframe. Desired output:

2014-11-20 12:45:00     51  Rain
2014-11-20 12:46:00     43  Rain
2014-11-20 12:47:00     44  Rain
...
2014-11-21 12:45:00     44  Cloudy
2014-11-21 12:46:00     46  Cloudy
2014-11-21 12:47:00     48  Cloudy
...
2014-11-22 12:45:00     38  Sunny
2014-11-22 12:46:00     32  Sunny
2014-11-22 12:47:00     37  Sunny

我怎样才能做到这一点?我需要使用合并、连接或连接吗?

How can I achieve this? Do I need to use merge, concat, or join?

推荐答案

开头:

>>> left
                     minutely
2014-11-20 12:45:00        51
2014-11-20 12:46:00        43
2014-11-20 12:47:00        44
2014-11-21 12:45:00        44
2014-11-21 12:46:00        46
2014-11-21 12:47:00        48
2014-11-22 12:45:00        38
2014-11-22 12:46:00        32
2014-11-22 12:47:00        37
>>> right
             daily
2014-11-20    Rain
2014-11-21  Cloudy
2014-11-22   Sunny

你可以这样做:

>>> left['day'] = left.index.date
>>> right.index = right.index.date
>>> left.join(right, on='day', how='left')
                     minutely         day   daily
2014-11-20 12:45:00        51  2014-11-20    Rain
2014-11-20 12:46:00        43  2014-11-20    Rain
2014-11-20 12:47:00        44  2014-11-20    Rain
2014-11-21 12:45:00        44  2014-11-21  Cloudy
2014-11-21 12:46:00        46  2014-11-21  Cloudy
2014-11-21 12:47:00        48  2014-11-21  Cloudy
2014-11-22 12:45:00        38  2014-11-22   Sunny
2014-11-22 12:46:00        32  2014-11-22   Sunny
2014-11-22 12:47:00        37  2014-11-22   Sunny