Reassign Pandas DataFrame with groupby and mean

user_13131079 0
Viewed 61

How can I use groupby and mean on a DataFrame, keeping all non-numeric columns?
Example:

     ID    label_1    label_2    label_3    label_4
0     1    0.582152     13          A       False
1     1    0.177475     3           A       False
2     2    0.263141     13          B       True
3     2    0.630196     3           B       True

Expected output:

     ID    label_1    label_2    label_3    label_4
0     1    0.379814     8.0         A       False
1     2    0.446669     8.0         B       True

Is there any simple way of doing this? I could get the results I want following this answer but it groups by all non-numerical columns and it doesn't keep the original column order. I wanted to group the data by 'ID' and get the mean of all numeric columns, keeping all other non-numeric columns. Here is my code:

# Group data and get mean
data = data.groupby(list(data.select_dtypes(exclude=np.number))).agg(np.mean).reset_index()
# Reordering the columns to the original order
data = data[[*data.select_dtypes(include=np.number).columns,
    *data.select_dtypes(exclude=np.number).columns]]
pandas python
2 Answers

something like ths

df.groupby('ID').agg({'label_1':'mean', 'label_2':'mean', 'label_3':'first', 'label_4':'first'}).reset_index()

use agg with different functions for different columns. Produces


    ID      label_1     label_2 label_3 label_4
0   1       0.379814    8       A       False
1   2       0.446668    8       B       True

To generate the mapping of columns to aggregation functions programmatically based on the type of the column, you can use

from pandas.api.types import is_numeric_dtype
num_str_f = {True: 'mean', False:'first'}
agg_map = {col: num_str_f[is_numeric_dtype(dtp)]  for col,dtp in zip(df.columns,df.dtypes)}
agg_map

to get

{'ID': 'mean',
 'label_1': 'mean',
 'label_2': 'mean',
 'label_3': 'first',
 'label_4': 'mean'}

so that then you use

df.groupby('ID', as_index = False).agg(agg_map).reset_index()

with the same result as above

user_145514260

Try check the column type inside the agg

df.groupby('ID').agg(lambda x : x.head(1) if x.dtype=='object' else x.mean())
user_79645270
Related