Creating lambda function with conditions on one df to use in df.apply of another df
<p>Consider <code>df</code></p>

<pre><code>Index   A         B      C
0      20161001   0      24.5
1      20161001   3      26.5
2      20161001   6      21.5
3      20161001   9      29.5
4      20161001   12     20.5
5      20161002   0      30.5
6      20161002   3      22.5
7      20161002   6      25.5
...
</code></pre>

<p>Also consider <code>df2</code></p>

<pre><code>Index Threshold
0     25
1     27
2     29
3     30
4     25
5     30
..
</code></pre>

<p>I want to add a column <code>"Number of Rows"</code> to <code>df2</code> which contains the number of rows in <code>df</code> where <code>(C &gt; Threshold) &amp; (A &gt;= 20161001) &amp; (A &lt;= 20161002)</code> holds true. This is basically to imply that there are conditions on more than one column in <code>df</code></p>

<pre><code>Index Threshold  Number of Rows 
0     25         4
1     27         2
2     29         2
3     30         1
4     25         4
5     30         1
..
</code></pre>

<p>For <code>Threshold=25</code> in <code>df2</code>, there are 4 rows in <code>df</code> where <code>"C"</code> value crosses 25.</p>

<p>I tried something like:</p>

<pre><code>def foo(threshold,start,end):
    return len(df[(df['C'] &gt; threshold) &amp; (df['A'] &gt; start) &amp; (df['A'] &lt; end)])

df2['Number of rows'] = df.apply(lambda df2: foo(df2['Threshold'],start = 20161001, end = 20161002),axis=1)
</code></pre>

<p>But this is populating the <code>Number of Rows</code> column with 0. Why is this?</p>

<p>You could make use of Boolean Indexing and the <code>sum()</code> aggregate function</p>

<pre><code># Create the first dataframe (df)
df = pd.DataFrame([[20161001,0 ,24.5],
                   [20161001,3 ,26.5],
                   [20161001,6 ,21.5],
                   [20161001,9 ,29.5],
                   [20161001,12,20.5],
                   [20161002,0 ,30.5],
                   [20161002,3 ,22.5],
                   [20161002,6 ,25.5]],columns=['A','B','C'])

# Create the second dataframe (df2)

df2 = pd.DataFrame(data=[25,27,29,30,25,30],columns=['Threshold'])

start = 20161001
end = 20161002

df2['Number of Rows'] = df2['Threshold'].apply(lambda x : ((df.C &gt; x) &amp; (df.A &gt;= start) &amp; (df.A &lt;= end)).sum())

print(df2['Number of Rows'])

Out[]: 
       0    4
       1    2
       2    2
       3    1
       4    4
       5    1
       Name: Number of Rows, dtype: int64
</code></pre>

python pandas lambda