How to find mean of quantitative variable from categorical variable in a dataframe?

Question

Let's say I have the following data frame in pandas:

data = {'State':['CA', 'CA', 'CA', 'CA', 'NY', 'NY', 'TX'],
        'Cost':[20, 30, 40, 50, 60, 70, 70]}
test = pd.DataFrame(data)
print(test.head(7))

which would be the following

  State  Cost
0    CA    20
1    CA    30
2    CA    40
3    CA    50
4    NY    60
5    NY    70
6    TX    70

In this scenario, the mean cost of California would be 35, the mean cost of new york would be 65, and the mean cost of texas would be 70.

Here is my question: what would be the query in pandas in which we could find the mean cost of a state given that state?

score 1 · Accepted Answer · answered May 29 '19 at 01:17

Use groupby and mean:

print(test.groupby('State').mean())

Which outputs:

       Cost
State      
CA       35
NY       65
TX       70

If you want a cleaner DataFrame:

print(test.groupby('State', as_index=False).mean())

Which gives:

  State  Cost
0    CA    35
1    NY    65
2    TX    70

How to find mean of quantitative variable from categorical variable in a dataframe?

1 Answers1