Get mean and mode of dataframe depending on each column type

user_11302142 0
Viewed 186

Apologies if something similar has been asked before, I searched around but couldn't figure out a solution.

My dataset looks like such

data1 = {'Group':['Winner','Winner','Winner','Winner','Loser','Loser'],
        'Study': ['Read','Read','Notes','Cheat','Read','Read'],
        'Score': [1,.90,.80,.70,1,.90]}
df1 = pd.DataFrame(data=data1)

enter image description here

This dataframe spans for dozens of rows, and have a set of numeric columns, and a set of string columns. I would like to condense this into 1 row, where each entry is just the mean or mode of the column. If the column is numeric, take the mean, otherwise, take the mode. In my actual use case, the order of numeric and object columns are random, so I hope to use an iterative loop that checks for each column which action to take.

I tried this but it didn't work, it seems to be taking the entire Series as the mode.

for i in df1:
    if df1[i].dtype == 'float64':
        df1[i] = df1[i].mean()

Any help is appreciated, thank you!

pandas python
3 Answers

You can use describe with 'all' which calculates statistics depending upon the dtype. It determines the top (mode) for object and mean for numeric columns. Then combine.

s = df1.describe(include='all')
s = s.loc['top'].combine_first(s.loc['mean'])

#Group      Winner
#Study        Read
#Score    0.883333
#Name: top, dtype: object
user_43333590

np.number and select_dtypes

s = df1.select_dtypes(np.number).mean()
df1.drop(s.index, axis=1).mode().iloc[0].append(s)

Group      Winner
Study        Read
Score    0.883333
dtype: object

Variant

g = df1.dtypes.map(lambda x: np.issubdtype(x, np.number))
d = {k: d for k, d in df1.groupby(g, axis=1)}
pd.concat([d[False].mode().iloc[0], d[True].mean()])

Group      Winner
Study        Read
Score    0.883333
dtype: object
user_23366540

Here is a slight variation on your solution that gets the job done

res = {}
for col_name, col_type in zip(df1.columns, df1.dtypes):
    if pd.api.types.is_numeric_dtype(col_type):
        res[col_name] = df1[col_name].mean()
    else:
        res[col_name]= df1[col_name].mode()[0]

pd.DataFrame(res, index = [0])

returns

    Group   Study   Score
0   Winner  Read    0.883333

there could be multiple modes in a Series -- this solution picks the first one

user_145514260
Related