Sum data points from individual pandas dataframes in a summary dataframe based on custom (and possibly overlapping) bins
<p>I have many dataframes with individual counts (e.g. <code>df_boston</code> below). Each row defines a data point that is uniquely identified by its <code>marker</code> and its <code>point</code>. I have a summary dataframe (<code>df_inventory_master</code>) that has custom bins (the <code>point</code>s above map to the <code>Begin</code>-<code>End</code> coordinates in the master). I want to add a column to this dataframe for each individual city that sums the counts from that city in a new column. An example is shown.</p>

<p>Two quirks are that the the bins in the master frame can be overlapping (the count should be added to both) and that some counts may not fall in the master (the count should be ignored).</p>

<p>I can do this in pure Python but since the data are in dataframes it would be helpful and likely faster to do the manipulations in <code>pandas</code>. I'd appreciate any tips here!</p>

<p>This is the master frame:</p>

<pre><code>&gt;&gt;&gt; df_inventory_master = pd.DataFrame({'Marker': [1, 1, 1, 2],
...                                     'Begin': [100, 300, 500, 100],
...                                     'End': [200, 600, 900, 250]})
&gt;&gt;&gt; df_inventory_master
   Begin  End  Marker
0    100  200       1
1    300  600       1
2    500  900       1
3    100  250       2
</code></pre>

<p>This is data for one city:</p>

<pre><code>&gt;&gt;&gt; df_boston = pd.DataFrame({'Marker': [1, 1, 1, 1],
...                           'Point': [140, 180, 250, 500],
...                           'Count': [14, 600, 1000, 700]})
&gt;&gt;&gt; df_boston
   Count  Marker  Point
0     14       1    140
1    600       1    180
2   1000       1    250
3    700       1    500
</code></pre>

<p>This is the desired output.<br>
- Note that the count of 700 (Marker 1, Point 500) falls in 2 master bins and is counted for both.<br>
- Note that the count of 1000 (Marker 1, Point 250) does not fall in a master bin and is not counted.<br>
- Note that nothing maps to Marker 2 because <code>df_boston</code> does not have any Marker 2 data.  </p>

<pre><code>&gt;&gt;&gt; desired_frame
   Begin  End  Marker  boston
0    100  200       1     614
1    300  600       1     700
2    500  900       1     700
3    100  250       2       0
</code></pre>

<p>What I've tried: I looked at the <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.cut.html" rel="nofollow">pd.cut()</a> function, but with the nature of the bins overlapping, and in some cases absent, this does not seem to fit. I can add the column filled with 0 values to get part of the way there but then will need to find a way to sum the data in each frame, using bins defined in the master.</p>

<pre><code>&gt;&gt;&gt; df_inventory_master['boston'] = pd.Series([0 for x in range(len(df_inventory_master.index))], index=df_inventory_master.index)
&gt;&gt;&gt; df_inventory_master
   Begin  End  Marker  boston
0    100  200       1       0
1    300  600       1       0
2    500  900       1       0
3    100  250       2       0
</code></pre>

<p>Here is how I approached it, basically a *sql style left join * using the pandas merge operation, then apply() across the row axis, with a lambda to decide if the individual records are in the band or not, finally groupby and sum:    </p>

<pre><code>df_merged = df_inventory_master.merge(df_boston, on=['Marker'],how='left')

# logical overwrite of count
df_merged['Count'] = df_merged.apply(lambda x: x['Count'] if x['Begin'] &lt;= x['Point'] &lt;= x['End'] else 0 , axis=1 )
df_agged = df_merged[['Begin','End','Marker','Count']].groupby(['Begin','End','Marker']).sum()
df_agged_resorted = df_agged.sort_index(level = ['Marker','Begin','End'])
df_agged_resorted = df_agged_resorted.astype(np.int) 
df_agged_resorted.columns =['boston'] # rename the count column to boston.
print df_agged_resorted
</code></pre>

<p>And the result is</p>

<pre><code>                  boston
Begin End Marker        
100   200 1          614
300   600 1          700
500   900 1          700
100   250 2            0
</code></pre>

python python-3.x pandas dataframe