Reputation: 37
I have a dataframe like this:
Location Action
House1 Quote
House2 Offer
House3 Quote
House2 Quote
House2 Quote
House3 Offer
I want to add two columns, one that shows the count of quotes to any given house, and one that shows the count of offers to any given house.
I have tried some options with groupby and transform, but can't figure out how to specify the criteria in the 'Action' column that I want to count.
Ideally the output would be this:
Location Action Quotes Offers
House1 Quote 1 0
House2 Offer 2 1
House3 Quote 1 1
House2 Quote 2 1
House2 Quote 2 1
House3 Offer 1 1
Upvotes: 3
Views: 56
Reputation: 88305
You can do a pd.crosstab
and merge
back on Location
:
df.merge(pd.crosstab(df['Location'],df['Action']), on='Location')
Location Action Offer Quote
0 House1 Quote 0 1
1 House2 Offer 1 2
2 House2 Quote 1 2
3 House2 Quote 1 2
4 House3 Quote 1 1
5 House3 Offer 1 1
Upvotes: 5