Concatenation / concatenation of two data frames with different frequency series indices in Pandas?
Using pandas 0.15.1. Suppose I have the following two data frames:
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 would like to concatenate two data frames such that the day values propagate to every minute row that has a matching day.
And since there is actually no data in the line minutes at 00:00:00, I don't want that time to be included in the resulting frame. 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? Should I use union, concat or join?
+3
source to share
1 answer
beginning with:
>>> 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
You can do:
>>> 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
+3
source to share