Trying to get average spend in a certain store in a certain town

user_11222963 0
Viewed 48

I have a dataframe of transactions:

   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

Two towns: New York and Los Angeles.

Two categories of spend: Book Shops and Cable TV.

As you can see there are 9 people living in New York.

There are only three Book Shop transactions in New York so I would expect the average Book Shop spend to be: ($12.50 + $4.49 + $11.70) / 9 people = $3.18

I am trying to convert this to a pandas function. I have tried many different groupbys and pivots, but it always treats the data as if there are only 3 people living in New York.

Here is some of what I have tried:

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=["category"],values=["amount"],columns=["town"],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

I am getting $9.56 as an average each time for Book stores in New York.

How can I get the average using the whole population of New York, and not just the three people who bought Books?

If you want to use pd.DataFrame.from_dict():


{'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'}}
pandas python
3 Answers

try calculating each aggregated value on its own: ie

amounts = df.groupby(['town', 'category']).amount.sum()
ncostumers = df.groupby(['town']).size()
o = amounts.unstack(level=0) / ncostumers
print(o)

town    Los Angeles New York
category        
Book Stores 6.570000    3.187778
Cable TV    6.427143    6.333333
user_112051490

You can use pd.pivot_table using aggfunc as sum with pd.Series.value_counts and divide them df.div

(df.pivot_table(index = 'category', columns='town', values='amount',aggfunc='sum').
    div(df['town'].value_counts())
)

             Los Angeles  New York
category
Book Stores     6.570000  3.187778
Cable TV        6.427143  6.333333

EDIT:

Changing town as index and categories as columns.

(df.pivot_table(index = 'town', columns='category', values='amount',aggfunc='sum').
    div(df['town'].value_counts(),axis=0)
)

category     Book Stores  Cable TV
Los Angeles     6.570000  6.427143
New York        3.187778  6.333333
user_124164530

First aggregate sum, get MultiIndex, first level is Town, which is divided by counts by Series.value_counts and last reshape by Series.unstack:

df1 = (df.groupby(['town', 'category'])['amount'].sum()
         .div(df['town'].value_counts(), level=0)
         .unstack(level=0))
print (df1)
town         Los Angeles  New York
category                          
Book Stores     6.570000  3.187778
Cable TV        6.427143  6.333333

Similar solution, only changed order of column in first groupby, then is change level for division and in unstack is used second level, so no parameter level:

df1 = (df.groupby(['category', 'town'])['amount'].sum()
         .div(df['town'].value_counts(), level=1)
         .unstack())
print (df1)
town         Los Angeles  New York
category                          
Book Stores     6.570000  3.187778
Cable TV        6.427143  6.333333
user_29010020
Related