Is there a way to count and calculate mean for text columns using groupby?

user_3608833 0
Viewed 120

I have been using pandas.groupby to pivot data and create descriptive charts and tables for my data. While doing groupby for three variables, I keep running into a DataError: No numeric types to aggregate error while working with the cancelled column.

To describe my data, Year and Month contain yearly and monthly data for multiple columns (multiple years, all months), Type contains the type of order item (Clothes, Appliances, etc.), and cancelled contains yes or no string values to determine whether a order was cancelled or not.

I am hoping to plot a graph and show a table to show what the cancellation rate (and success rate) is by order item. The following is what I'm using so far

df.groupby(['Year', 'Month', 'Type'])['cancelled'].mean()

But this doesn't seem to be working.

Sample

Year    Month        Type          cancelled 
2012      1        electronics       yes
2012      10         fiber           yes
2012      9         clothes          no
2013      4        vegetables        yes
2013      5        appliances        no
2016      3        fiber             no
2017      1        clothes           yes
pandas-groupby pandas python
2 Answers

Use:

df = pd.DataFrame({
         'Year':[2020] * 6,
         'Month':[7,8,7,8,7,8],
         'cancelled':['yes','no'] * 3,
         'Type':list('aaaaba')
})
print (df)

Get counts per Year, Month, Type columns:

df1 = df.groupby(['Year', 'Month', 'Type','cancelled']).size().unstack(fill_value=0)
print (df1)
cancelled        no  yes
Year Month Type         
2020 7     a      0    2
           b      0    1
     8     a      3    0

And then divide by sum of values for ratio:

df2 = df1.div(df1.sum()).mul(100)
print (df2)
cancelled           no        yes
Year Month Type                  
2020 7     a       0.0  66.666667
           b       0.0  33.333333
     8     a     100.0   0.000000
user_29010020

It's possible I have misunderstood what you want your output to look like, but to find the cancellation rate for each item type, you could do something like this:

# change 'cancelled' to numeric values
df.loc[df['cancelled'] == 'yes', 'cancelled'] = 1
df.loc[df['cancelled'] == 'no', 'cancelled'] = 0

# get the mean of 'cancelled' for each item type
res = {}
for t in df['Type'].unique():
    res[t] = df.loc[df['Type'] == t, 'cancelled'].mean()

# if desired, put it into a dataframe
results = pd.DataFrame([res], index=['Rate']).T

Output:

              Rate
electronics   1.0
fiber         0.5
clothes       0.5
vegetables    1.0
appliances    0.0

Note: If you want to specify specific years or months, you can do that with loc as well, but given that your example data did not have any repeats within a given year or month, this would return your original dataframe for your given example.

user_108010980
Related