I'm looking for a good way to align dataframes each having a timestamp that "includes" seconds without loosing data. Specifically, my problem looks as follows:
Here d1 is my "main" dataframe.
ind1 = pd.date_range("20120101", "20120102",freq='S')[1:20]
data1 = np.random.randn(len(ind1))
df1 = pd.DataFrame(data1, index=ind1)
Eg. df1 could look like:
0
2012-01-01 00:00:01 2.738425
2012-01-01 00:00:02 -0.323905
2012-01-01 00:00:03 1.861855
2012-01-01 00:00:04 0.480284
2012-01-01 00:00:05 0.340270
2012-01-01 00:00:06 -1.139052
2012-01-01 00:00:07 -0.203018
2012-01-01 00:00:08 -0.398599
2012-01-01 00:00:09 -0.568802
2012-01-01 00:00:10 -1.539783
2012-01-01 00:00:11 -1.778668
2012-01-01 00:00:12 -1.488097
2012-01-01 00:00:13 0.889712
2012-01-01 00:00:14 -0.620267
2012-01-01 00:00:15 0.075169
2012-01-01 00:00:16 -0.091302
2012-01-01 00:00:17 -1.035364
2012-01-01 00:00:18 -0.459013
2012-01-01 00:00:19 -2.177190
In addition I have another dataframe, say df2:
ind21 = pd.date_range("20120101", "20120102",freq='S')[2:7]
ind22 = pd.date_range("20120101", "20120102",freq='S')[12:19]
data2 = np.random.randn(len(ind21+ind22))
df2 = pd.DataFrame(data2, index=ind21+ind22)
df2 looks like (note the non-periodic timestamps):
0
2012-01-01 00:00:02 -1.877779
2012-01-01 00:00:03 1.772659
2012-01-01 00:00:04 0.037251
2012-01-01 00:00:05 -1.195782
2012-01-01 00:00:06 -0.145339
2012-01-01 00:00:12 -0.220673
2012-01-01 00:00:13 -0.581469
2012-01-01 00:00:14 -0.520756
2012-01-01 00:00:15 -0.562677
2012-01-01 00:00:16 0.109325
2012-01-01 00:00:17 -0.195091
2012-01-01 00:00:18 0.838294
Now, I join both to df and get:
df = df1.join(df2, lsuffix='A')
0A 0
2012-01-01 00:00:01 2.738425 NaN
2012-01-01 00:00:02 -0.323905 -1.877779
2012-01-01 00:00:03 1.861855 1.772659
2012-01-01 00:00:04 0.480284 0.037251
2012-01-01 00:00:05 0.340270 -1.195782
2012-01-01 00:00:06 -1.139052 -0.145339
2012-01-01 00:00:07 -0.203018 NaN
2012-01-01 00:00:08 -0.398599 NaN
2012-01-01 00:00:09 -0.568802 NaN
2012-01-01 00:00:10 -1.539783 NaN
2012-01-01 00:00:11 -1.778668 NaN
2012-01-01 00:00:12 -1.488097 -0.220673
2012-01-01 00:00:13 0.889712 -0.581469
2012-01-01 00:00:14 -0.620267 -0.520756
2012-01-01 00:00:15 0.075169 -0.562677
2012-01-01 00:00:16 -0.091302 0.109325
2012-01-01 00:00:17 -1.035364 -0.195091
2012-01-01 00:00:18 -0.459013 0.838294
2012-01-01 00:00:19 -2.177190 NaN
This is fine, however, I would like to replace the NaN values in column 0 with the "minute level" value of df2. So only in cases where I don't have an exact match on the "seconds level", I would like to go back to the minute level. This could be a simple average over all values for that specific minute (here: 2012-01-01 00:00:00).
Thx for any help!
