Pandas: using groupby to get mean for each data category

Question

I have a dataframe that looks like this:

>>> df[['data','category']]
Out[47]: 
          data     category
  0       4610            2
 15       4610            2
 22       5307            7
 23       5307            7
 25       5307            7
...        ...          ...

Both data and category are numeric so I'm able to do this:

>>> df[['data','category']].mean()
Out[48]: 
data        5894.677985
category      13.805886
dtype: float64

And i'm trying to get the mean for each category. It looks straight forward but when I do this:

>>> df[['data','category']].groupby('category').mean()

or

>>> df.groupby('category')['data'].mean()

It returns an error like this:

DataError: No numeric types to aggregate

There's no error if I replace both functions above with .count().

What do I do wrongly? What's the correct way to get the mean of each category?

Solution 1

Can you do a df.dtypes ? In the example below type is Int as it works fine.

    import pandas as pd

    ##group by 1 columns
    df = pd.DataFrame({' data': [4610, 4611, 4612, 4613], 'Category': [2, 2,    7, 7]})
    print df.groupby('Category'). mean()


    ##Mutiple columns to group by
    df1 = pd.DataFrame({' data': [4610, 4611, 4612, 4613], 'Category': [2,    2, 7, 7], 'Category2' : ['A','B','A','B']})
    key=['Category','Category2']
    print df1.groupby( key).mean()

 Category Category2       
 2        A           4610
          B           4611
 7        A           4612
          B           4613

Solution 2

As mentioned, you don't give an example of the testTime and passing_site data, but I'm guessing that they're floating rate numbers. As I'm sure you can imagine, you can't group on floating numbers. Rather, you would need to group on integers or categories of some type.

try something like:

df.groupby(['data', 'category'])['passing_site', 'testTime'].mean()

You're grouping on 'data' and 'category', and then calculating the mean for the numerical columns 'passing_site' and 'testTime'.

Comments

[Roman Pekar]

what output you want to get? do you want to get average time or what?

[Andy Hayden]

Could you give an example DataFrame which demonstrates this? (The column names in your example don't match up.) What version of pandas are you using?

[Bob Haffner]

Like Andy said, your column names don't match up. I'm guessing testTime is not a numeric. Check your dtypes