CODEWITHSUNDEEP
pythonpandasdataframecount
Joe
Joe

Reputation: 37

Generate count column in dataframe based on multiple criteria pandas

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

Answers (1)

yatu
yatu

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

Related Questions