title: Use dataframe name in dataframe.query
body: <p>I have two dataframes</p>
  <p>df1</p>
  <pre><code>Company     Region  ID
  Walmart     North   1
  Walmart     North   2
  OneStore    North   3
  OneStore    North   4
  OneStore    South   4
  Walmart     South   4
  OneStore    West    4
  Walmart     East    5
  </code></pre>
  <p>df2</p>
  <pre><code>Company     Region  ID  Sales
  Walmart     North   1   100
  Walmart     North   1   150
  Walmart     North   1   50
  Walmart     North   2   400
  OneStore    North   3   250
  OneStore    North   3   150
  OneStore    North   4   200
  OneStore    South   4   300
  Walmart     South   4   100
  Walmart     South   4   250
  OneStore    West    4   350
  OneStore    West    4   100
  Walmart     East    5   300
  Walmart     East    5   400
  </code></pre>
  <p>Final output required</p>
  <p>df1</p>
  <pre><code>Company     Region  ID  Sales
  Walmart     North   1   300
  Walmart     North   2   400
  OneStore    North   3   400
  OneStore    North   4   200
  OneStore    South   4   300
  Walmart     South   4   350
  OneStore    West    4   450
  Walmart     East    5   700
  </code></pre>
  <p>then i want to use a query like</p>
  <pre><code>df2.query(&quot;(df2['Company']==df1.at[0,'Company'])&amp;(df2['Region']==df1.at[0,'Region'])&amp;(df2['ID']==df1.at[0,'ID'])&quot;)['Sales'].sum()
  
  but i get an error: **UndefinedVariableError: name 'df2' is not defined**
  </code></pre>
  <p>if df2 is removed from query</p>
  <pre><code>df2.query(&quot;(Company==df1.at[0,'Company'])&amp;(Region==df1.at[0,'Region'])&amp;(ID==df1.at[0,'ID'])&quot;)['Sales'].sum()
  
  UndefinedVariableError: name 'df1' is not defined
  </code></pre>
  <p>if i use</p>
  <pre><code>df2.loc[(df2['Company']==df1.at[0,'Company'])&amp;(df2['Region']==df1.at[0,'Region'])&amp;(df2['ID']==df1.at[0,'ID']),'Sales'].sum()
  </code></pre>
  <p>then i get required result, (300 for first row)</p>
  <p>but i want to use query function as this is a sample data and i work with a large dataset where columns vary so i generate a query based on columns like</p>
  <pre><code>'&amp;'.join(&quot;(df2['%s']==df1.at[0,'%s'])&quot; % x for x in ['col1', 'col2', . . . . ])
  </code></pre>
  <p>How can i make the query to work for this condition</p>
  <p><em><strong>Update:</strong></em></p>
  <p><em>Thanks to <a href="https://stackoverflow.com/users/2956135/emma">Emma</a> for merge &amp; groupby,</em></p>
  <p>i got the output dataframe as required</p>
  <pre><code>df_1
  Company     Region  ID  Sales
  OneStore    North   3   400
  OneStore    North   4   200
  OneStore    South   4   300
  OneStore    West    4   450
  Walmart     East    5   700
  Walmart     North   1   300
  Walmart     North   2   400
  Walmart     South   4   350
  </code></pre>
  <p>Now i have another dataframe</p>
  <p>df3</p>
  <pre><code>Company     Region  ID  Stock   Units
  Walmart     North   1   Full        5
  Walmart     North   1   Full        7
  Walmart     North   2   Full        4
  Walmart     North   2   Restock     26
  Walmart     North   2   Restock     34
  OneStore    North   3   Full        2
  OneStore    North   3   Restock     26
  OneStore    North   4   Full        3
  Walmart     South   4   Full        5
  Walmart     South   4   Restock     74
  OneStore    West    4   Full        9
  OneStore    West    4   Full        7
  OneStore    West    4   Restock     53
  OneStore    West    5   Full        2
  Walmart     East    5   Full        2
  Walmart     East    5   Full        1
  Walmart     East    5   Restock     36
  </code></pre>
  <p>I need to get the sum of units where stock staus is restock and add this data to new column in df_1, i'm trying to use groupby by filtering df3 where stock status is restock, buy i'm getting error</p>
  <pre><code>df = df_1.merge(df3, on=['Company', 'Region', 'ID'], how='left')
  df_1.loc[:,'Units_req'] = df[df['Stock']=='Restock'].groupby(['Company', 'Region', 'ID', 'Sales', 'Profit'])['Units'].sum()
  
  TypeError: incompatible index of inserted column with frame index
  </code></pre>
  <p>Please help with best approach to this problem, i still have to add data from different tables based on multiple conditions, i still find it easy if anyone could help on how to use dataframe names in dataframe.query</p>