title: Trying to get average spend in a certain store in a certain town
body: <p>I have a dataframe of transactions:</p>
  <pre><code>   customer_id         town  amount     category
  0           n1     New York   12.50  Book Stores
  1           n2     New York    4.49  Book Stores
  2           n3     New York   11.70  Book Stores
  3           n4     New York   15.00     Cable TV
  4           n5     New York    7.00     Cable TV
  5           n6     New York    6.00     Cable TV
  6           n7     New York   15.00     Cable TV
  7           n8     New York    7.00     Cable TV
  8           n9     New York    7.00     Cable TV
  9          la1  Los Angeles   15.00  Book Stores
  10         la2  Los Angeles   15.99  Book Stores
  11         la3  Los Angeles   15.00  Book Stores
  12         la4  Los Angeles    7.00     Cable TV
  13         la5  Los Angeles   15.99     Cable TV
  14         la6  Los Angeles   15.00     Cable TV
  15         la7  Los Angeles    7.00     Cable TV
  </code></pre>
  <p><strong>Two towns:</strong> New York and Los Angeles.</p>
  <p><strong>Two categories of spend:</strong> Book Shops and Cable TV.</p>
  <p>As you can see there are 9 people living in New York.</p>
  <p>There are only three Book Shop transactions in New York so I would expect the average Book Shop spend to be: <code>($12.50 + $4.49 + $11.70) / 9 people = $3.18</code></p>
  <p>I am trying to convert this to a pandas function. I have tried many different <code>groupbys</code> and <code>pivots</code>, but it always treats the data as if there are only 3 people living in New York.</p>
  <p>Here is some of what I have tried:</p>
  <pre><code>print('\nAttempt 1...\n')
  print(new_df.groupby(['town','category'])['amount'].mean())
  print('\nAttempt 2...\n')
  print(new_df.groupby(['category','town'])['amount'].mean())
  print('\nAttempt 2...\n')
  print(pd.pivot_table(new_df,index=[&quot;category&quot;],values=[&quot;amount&quot;],columns=[&quot;town&quot;],aggfunc=[np.mean],fill_value=0))
  
  
  Attempt 1...
  
  town         category   
  Los Angeles  Book Stores   15.33
               Cable TV      11.25
  New York     Book Stores    9.56
               Cable TV       9.50
  Name: amount, dtype: float64
  
  Attempt 2...
  
  category     town       
  Book Stores  Los Angeles   15.33
               New York       9.56
  Cable TV     Los Angeles   11.25
               New York       9.50
  Name: amount, dtype: float64
  
  Attempt 2...
  
                     mean         
                   amount         
  town        Los Angeles New York
  category                        
  Book Stores       15.33     9.56
  Cable TV          11.25     9.50
  </code></pre>
  <p>I am getting $9.56 as an average each time for Book stores in New York.</p>
  <p>How can I get the average using the whole population of New York, and not just the three people who bought Books?</p>
  <p>If you want to use <code>pd.DataFrame.from_dict()</code>:</p>
  <pre><code>
  {'customer_id': {0: 'n1',
    1: 'n2',
    2: 'n3',
    3: 'n4',
    4: 'n5',
    5: 'n6',
    6: 'n7',
    7: 'n8',
    8: 'n9',
    9: 'la1',
    10: 'la2',
    11: 'la3',
    12: 'la4',
    13: 'la5',
    14: 'la6',
    15: 'la7'},
   'town': {0: 'New York',
    1: 'New York',
    2: 'New York',
    3: 'New York',
    4: 'New York',
    5: 'New York',
    6: 'New York',
    7: 'New York',
    8: 'New York',
    9: 'Los Angeles',
    10: 'Los Angeles',
    11: 'Los Angeles',
    12: 'Los Angeles',
    13: 'Los Angeles',
    14: 'Los Angeles',
    15: 'Los Angeles'},
   'amount': {0: 12.5,
    1: 4.49,
    2: 11.7,
    3: 15.0,
    4: 7.0,
    5: 6.0,
    6: 15.0,
    7: 7.0,
    8: 7.0,
    9: 15.0,
    10: 15.99,
    11: 15.0,
    12: 7.0,
    13: 15.99,
    14: 15.0,
    15: 7.0},
   'category': {0: 'Book Stores',
    1: 'Book Stores',
    2: 'Book Stores',
    3: 'Cable TV',
    4: 'Cable TV',
    5: 'Cable TV',
    6: 'Cable TV',
    7: 'Cable TV',
    8: 'Cable TV',
    9: 'Book Stores',
    10: 'Book Stores',
    11: 'Book Stores',
    12: 'Cable TV',
    13: 'Cable TV',
    14: 'Cable TV',
    15: 'Cable TV'}}
  </code></pre>