Generate count column in dataframe based on multiple criteria pandas

user_13054685 0
Viewed 53

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
pandas dataframe count python
1 Answers

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
user_96986840
Related