CODEWITHSUNDEEP
pythonpandasgroup-by
Peter Lucas
Peter Lucas

Reputation: 1991

Pandas GroupBy each month then subtotal counts based on string in a column

I am looking to get percentages of done trades to total trades per month. Previously my data was only for a single month and solved by:

total_trades = df['state'].count()
RFQ_Hit_Rate = done_trades / total_trades
RFQ_Hit_Rate = round(RFQ_Hit_Rate, 6)

There are now 12 months of data so I need to update the code. New data

dfHit_Rate_All = df[['Year_Month','state']].copy()
dfHit_Rate_All = dfHit_Rate_All.groupby(['Year_Month','state']).size().reset_index(name='count')

  Year_Month    state           Counts  
     2017-11    Customer Reject  1  
     2017-11    Customer Timeout 2  
     2017-11    Dealer Reject    3  
     2017-12    Dealer Timeout   4  
     2017-12    Done             5  
     2017-12    Done             6  
     2018-01    Tied Covered     7  
     2018-01    Tied Done        8  
     2018-01    Tied Traded Away 9  
     2018-02    Traded Away      10 
     2018-02    Done             11 
     2018-02    Customer Reject  12

For each month find the total trades, the total Done Trades and calculate the ratio. Note any string with 'Done' in it is a done trade i.e. [df['state'].str.contains('Done'):

Year_Month  Total_state_count   Total_state_count_Done  Done_To_Total_Ratio
2017-11               6                        0                0%
2017-12               15                       11               73%
2018-01               24                       8                33%
2018-02               33                       11               33%

Upvotes: 1

Views: 168

Answers (1)

jezrael
jezrael

Reputation: 862581

I think need aggregate by agg with tuples - new column name with aggregate functions:

agg = [('Total_state_count_Done',lambda x: x.str.contains('Done').sum()), 
       ('Total_state_count', 'size')]
df = df.groupby('Year_Month')['state'].agg(agg)

And for new column divide and multiple by 100:

df['Done_To_Total_Ratio'] = df['Total_state_count_Done'].div(df['Total_state_count']).mul(100)

print (df)
            Total_state_count_Done  Total_state_count  Done_To_Total_Ratio
Year_Month                                                                
2017-11                          0                  3             0.000000
2017-12                          2                  3            66.666667
2018-01                          1                  3            33.333333
2018-02                          1                  3            33.333333

If need convert last column to integers and add percentages:

df['Done_To_Total_Ratio'] = (df['Total_state_count_Done']
                                  .div(df['Total_state_count'])
                                  .mul(100)
                                  .astype(int)
                                  .astype(str)
                                  .add('%'))

print (df)
            Total_state_count_Done  Total_state_count Done_To_Total_Ratio
Year_Month                                                               
2017-11                          0                  3                  0%
2017-12                          2                  3                 66%
2018-01                          1                  3                 33%
2018-02                          1                  3                 33%

Upvotes: 1

Related Questions