Joining Dataframes on DatetimeIndex by Seconds and Minutes for NaNs
<p>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:</p>

<p>Here <code>d1</code> is my "main" dataframe.</p>

<pre><code>ind1  = pd.date_range("20120101", "20120102",freq='S')[1:20] 
data1 = np.random.randn(len(ind1)) 
df1   = pd.DataFrame(data1, index=ind1)
</code></pre>

<p>Eg. df1 could look like:</p>

<pre><code>                            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
</code></pre>

<p>In addition I have another dataframe, say df2:</p>

<pre><code>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)
</code></pre>

<p>df2 looks like (note the non-periodic timestamps):</p>

<pre><code>                           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
</code></pre>

<p>Now, I join both to df and get:</p>

<pre><code>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
</code></pre>

<p>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).</p>

<p>Thx for any help!</p>

<p>Use the DateTimeIndex attribute <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DatetimeIndex.minute.html" rel="nofollow"><code>.minute</code></a> to perform grouping and later fill the missing values with it's mean across each group(every minute):</p>

<pre><code>df['0'] = df.groupby(df.index.minute)['0'].transform(lambda x: x.fillna(x.mean()))
</code></pre>

python pandas datetimeindex datetime64