python pandas - how can I map values in 1 dataframe to indices in another without looping?
<p>I have 2 dataframes - "df_rollmax" is a derivative of "df_data" with the same shape. I am attempting to map the values of df_rollmax back to df_data and create a third df (df_maxdates) which contains the dates at which each value in df_rollmax originally showed up in df_data.</p>

<pre><code>list1 = [[21,101],[22,110],[25,113],[24,112],[21,109],[28,108],[30,102],[26,106],[25,111],[24,110]]
df_data = pd.DataFrame(list1,index=pd.date_range('2000-1-1',periods=10, freq='D'), columns=list('AB'))
df_rollmax = pd.DataFrame(df_data.rolling(center=False,window=5).max())
mapA = pd.Series(df_data.index, index=df_data['A'])
</code></pre>

<p>From a previous question, I see that a single date can be found with:</p>

<p><code>mapA[rollmax.ix['j','A']]</code> returns <code>Timestamp('2000-01-07 00:00:00')</code></p>

<p>But my real dataset is much larger and I would like to fill the third dataframe with dates without looping over every row and column.</p>

<p>Mapping back to the indices is a problem due to: <code>ValueError: cannot reindex from a duplicate axis</code> so this isn't working...</p>

<pre><code>df_maxdates = pd.DataFrame(index=df_data.index, columns=df_data.columns)
for s in df_data.columns:
    df_maxdates[s] = mapA.loc[df_rollmax[s]]
</code></pre>

<p>Using the last instance of the duplicate value would be fine, but <code>df.duplicated(keep='last')</code> isn't cooperating. </p>

<p>Greatly appreciate any and all wisdom.</p>

<p><a href="http://stackoverflow.com/questions/40078107/python-pandas-dataframe-cant-figure-out-how-to-lookup-an-index-given-a-value">Link to original question</a></p>

<p>Update - this is what df_maxdates would look like:</p>

<p><a href="https://i.stack.imgur.com/FVaB3.png" rel="nofollow"><img src="https://i.stack.imgur.com/FVaB3.png" alt="enter image description here"></a></p>

<p>You can use <a href="http://stackoverflow.com/a/40101614/5741205">this BrenBarn's solution</a>:</p>

<pre><code>W = 5  # window size

df = pd.DataFrame(columns=df_data.columns, index=df_data.index[W-1:])

for col in df.columns.tolist():
    df[col] = df_data.index[df_data[col].rolling(W)
                                    .apply(np.argmax)[(W-1):]
                                    .astype(int)
                             +
                             np.arange(len(df_data)-(W-1))]

df = pd.DataFrame(columns=df_data.columns, index=df_data.index[:W-1]).append(df)

In [226]: df
Out[226]:
                    A          B
2000-01-01        NaT        NaT
2000-01-02        NaT        NaT
2000-01-03        NaT        NaT
2000-01-04        NaT        NaT
2000-01-05 2000-01-03 2000-01-03
2000-01-06 2000-01-06 2000-01-03
2000-01-07 2000-01-07 2000-01-03
2000-01-08 2000-01-07 2000-01-04
2000-01-09 2000-01-07 2000-01-09
2000-01-10 2000-01-07 2000-01-09
</code></pre>

<p>or <a href="http://stackoverflow.com/a/40103020/5741205">this piRSquared's solution</a>:</p>

<pre><code>def idxmax(s, w):
    i = 0
    while i + w &lt;= len(s):
        yield(s.iloc[i:i+w].idxmax())
        i += 1

x = pd.DataFrame({'A':[np.nan]*4 + list(idxmax(df_data.A, 5)),
                  'B':[np.nan]*4 + list(idxmax(df_data.B, 5))},
                 index=df_data.index)
</code></pre>

<p>Demo:</p>

<pre><code>In [89]: x = pd.DataFrame({'A':pd.to_datetime([np.nan]*4 + list(idxmax(df_data.A, 5))),
    ...:                   'B':pd.to_datetime([np.nan]*4 + list(idxmax(df_data.B, 5)))},
    ...:                  index=df_data.index)
    ...:

In [90]: x
Out[90]:
                    A          B
2000-01-01        NaT        NaT
2000-01-02        NaT        NaT
2000-01-03        NaT        NaT
2000-01-04        NaT        NaT
2000-01-05 2000-01-03 2000-01-03
2000-01-06 2000-01-06 2000-01-03
2000-01-07 2000-01-07 2000-01-03
2000-01-08 2000-01-07 2000-01-04
2000-01-09 2000-01-07 2000-01-09
2000-01-10 2000-01-07 2000-01-09
</code></pre>

python pandas dataframe duplicates mapping