Use two rows from a pandas dataframe to select a location in another dataframe
<p>I Have first dataframe <code>df1</code> as below. Here <code>col_b</code> is any number between h1 and h24 and doesn't contain all of 1 to 24 for each corresponding date: </p>

<pre><code>Date        col_b
20101101    h1
20101101    h2
20101101    h3
20101102    h1
20101102    h3
20101103    h2
20101104    h1
20101105    h2
20101105    h3
20101106    h6
20101106    h8
20101106    h24
20101107    h15
</code></pre>

<p>And second dataframe <code>df2</code> as:</p>

<pre><code>date        h1 h2 h3 h4 h5 h6 ... h24
20101101    4  6  45 6  7  8  ...  5
20101102    .........................
20101103    .........................
</code></pre>

<p>I need to select values from <code>df2</code> to a list where rows from <code>df1</code> match with the location in <code>df2</code>. </p>

<p>Currently, I am using <code>iterrows</code> to select the row values from <code>df1</code> and assigning values in <code>df2</code> as 
     <code>df2.loc[df2['Date] ==row[0], row[1]]</code> for each row in <code>df1</code>.</p>

<p>This is hectic and consuming lot of time. Is there a pythonic or Pandas way to do it ?</p>

<p>use <code>DataFrame.lookup()</code>:</p>

<pre><code>import numpy as np
import pandas as pd

df2 = pd.DataFrame(np.random.randint(0, 10, (5, 3)), 
                  columns=list("ABC"), 
                  index=pd.date_range("2016/01/01", "2016/05/01", freq="MS"))

df = pd.DataFrame({"date":df2.index[np.random.randint(0, 5, 10)],
                   "key": df2.columns[np.random.randint(0, 3, 10)]})

df["value"] = df2.lookup(df["date"], df["key"])
print(df)
</code></pre>

<p>the result:</p>

<pre><code>        date key  value
0 2016-01-01   C      2
1 2016-05-01   A      8
2 2016-01-01   A      8
3 2016-04-01   B      1
4 2016-04-01   C      2
5 2016-03-01   A      2
6 2016-03-01   A      2
7 2016-04-01   B      1
8 2016-05-01   A      8
9 2016-03-01   B      5
</code></pre>

python pandas dataframe