How to get mean and std from a dictionary of dataframe per each key

user_9686037 0
Viewed 134

Here is my dilemma:

I got a dictionary of dataframes like this:

dict_df[key]

m1      m2  m3  m4  m5  m6  
10410   5   10  21  33  11
15387   3   10  33  45  13
19026   4   16  27  40  11
26083   5   21  16  29  9
27806   4   17  23  31  7
43820   2   12  27  40  18
49199   7   22  30  38  11
50094   4   9   13  18  4

Per each key, it returns a DF with the same column names.

For each key, I need to store the mean and standard deviation of a set of features (let's take for example m2, m3, m4).

In the end, I want to obtain something like this df below (the numbers are totally random):

key   m2_mean    m2_std   m3_mean   m3_std    m4_mean     m4_std
key1    12       55         793      438       44           95
key2    14       442        21       43        14           442
key3    44       1          66       11        42           42
key4    42       42         2        23        98           70

The dataset is not that big, so even if the code is slow should be fine.

Thanks for the help and have a good one!

transformation transpose dictionary python performance
2 Answers

First, let's define some sample data:

>>> df1 = pd.DataFrame({
        "col1": [1, 2, 3],
        "col2": [4, 5, 6],
    })
>>> df2 = pd.DataFrame({
        "col1": [7, 8, 9],
        "col2": [10, 11, 12],
    })
>>> dict_df = {
        "df1": df1,
        "df2": df2,
    }

Now, you can use .agg() to calculate the mean and std of your dataframe (I've used max for simplicity), .stack() to reduce the dataframe into a single series, and .to_dict() to generate a representation of this result as a string. Notice that we'll only use one of the dataframes (df1) to show this result:

>>> df1.agg(["mean", "max"]).stack().to_dict()
{('mean', 'col1'): 2.0, ('mean', 'col2'): 5.0, ('max', 'col1'): 3.0, ('max', 'col2'): 6.0}

With this dict representation, we can use pd.DataFrame.from_dict to build a single dataframe with the metrics for each value in dict_df:

>>> df = pd.DataFrame.from_dict({
        df_name: df[["col1", "col2"]].agg(["mean", "max"]).stack().to_dict()
        for df_name, df in dict_df.items()
    }, orient="index")
>>> df
    mean        max      
    col1  col2 col1  col2
df1  2.0   5.0  3.0   6.0
df2  8.0  11.0  9.0  12.0

The only important difference with your expected output is in the column names, but we can solve that manually:

>>> df.columns = ["_".join(column) for column in df.columns]
>>> df
     mean_col1  mean_col2  max_col1  max_col2
df1        2.0        5.0       3.0       6.0
df2        8.0       11.0       9.0      12.0

Code that would do the trick for you:

>>> target_columns = ["m2", "m3", "m4"]
>>> df = pd.DataFrame.from_dict({
        key: df[target_columns].agg(["mean", "std"]).stack().to_dict()
        for key, df in dict_df.items()
    }, orient="index")
>>> df.columns = ["_".join(column) for column in df.columns]
>>> df.index.name = "key"
user_32810970

Start with creating a dictionary for the aggregates, in the form of new_col_name: (dataframe_column_name, aggregare_function):

required_aggs = {f'{col_name}_{agg_name}': (col_name, agg_name)
                 for agg_name in ['mean', 'std']
                 for col_name in df}

Above gives following dictionary of the required aggregates, and the column names.

{
    'm1_mean': ('m1', 'mean'),
    'm1_std': ('m1', 'std'),
    'm2_mean': ('m2', 'mean'),
    'm2_std': ('m2', 'std'),
    'm3_mean': ('m3', 'mean'),
    'm3_std': ('m3', 'std'),
    'm4_mean': ('m4', 'mean'),
    'm4_std': ('m4', 'std'),
    'm5_mean': ('m5', 'mean'),
    'm5_std': ('m5', 'std'),
    'm6_mean': ('m6', 'mean'),
    'm6_std': ('m6', 'std')
}

Now, for each dataframe, assign the key column with the value of the key, then group by this column, and call .agg on groupby object passing unpacked version of above aggregate dictionary:

>>> df.assign(key=123).groupby('key').agg(**required_aggs)

       m1_mean  m2_mean  m3_mean  ...    m4_std    m5_std    m6_std
key                               ...                              
123  30228.125     4.25   14.625  ...  6.860862  8.447316  4.140393

PS: In above snippet, df is a single dataframe in dict_df[key], and value of this key is assumed to be 123. You need to do the same for each dataframes in the dictionary. Also, if you need to calculate aggregate of specific columns only, you just need to replace df in for col_name in df by list of columns e.g.: for col_name in ['m1', 'm2']

user_91363480
Related