CODEWITHSUNDEEP
pythonpandasmulti-index
giogix
giogix

Reputation: 799

Calculating aggregate values in a pandas dataframe with multiple columns

I have a Pandas DataFrame with multiple columns.

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index)
print(df)

first        bar                 baz                 foo                 qux  \
second       one       two       one       two       one       two       one   
A      -0.093829 -0.159939 -0.386961 -0.367417  0.625646  1.286186  0.429855   
B       0.440266  0.345161  1.798363 -1.265215  0.204303 -1.492993 -1.714360   
C       0.689076 -1.211060 -0.265888  0.769467 -0.706941  0.086907 -0.892892 

first             
second       two  
A      -1.006210  
B      -0.275578  
C      -0.563757

I want to calculate the mean and standard deviation, of each column, grouping by the upper column. Once I have calculated the mean and standard deviation I want to double the columns in the lower level, adding to the column name the information related to the statistical operation (mean or standard deviation) as "column name" + "_" + "std/mean".

group_cols = df.groupby(df.columns.get_level_values('first'), axis=1)
list_stat_dfs = []
for key, group in group_cols:
    group_descr = group.describe().loc[['mean', 'std'], :]  # Get mean and std from single site
    group_descr.loc[:, (key, 'stats')] = group_descr.index
    group_descr.loc[:, (key, 'first')] = key
    group_descr.columns = group_descr.columns.droplevel(0)  # Remove upper level column (site_name)
    group_descr = group_descr.pivot(columns='stats', index='first')  # Rows to columns
    col_prod = list(product(group_descr.columns.levels[0], group_descr.columns.levels[1]))
    cols = ['_'.join((col[0], col[1])) for col in col_prod]
    group_descr.columns = pd.MultiIndex.from_product(([key], cols))  # From multiple columns to single column
    group_descr.reset_index(inplace=True)
    list_stat_dfs.append(group_descr)

group_descr = pd.concat(list_stat_dfs, axis=1)
print(group_descr)

first       bar                              first       baz            \
         one_mean   one_std  two_mean  two_std        one_mean   one_std   
0   bar  0.507185  1.799053 -0.249692  1.41507   baz -0.147664  0.595927  

                     first       foo                               first  \
   two_mean   two_std        one_mean   one_std  two_mean   two_std         
0  0.160018  1.405113   foo -0.433644  1.245972  0.254995  0.846983   qux 

        qux                                
   one_mean   one_std  two_mean   two_std  
0  0.667629  0.315417 -0.757989  0.683273

As you can see, I have been able to manage it with a for loop and some line of code. Can someone do the same thing in a more optimized way. I am quite sure that with Pandas, the same thing can be done with few lines of code.

Upvotes: 1

Views: 799

Answers (1)

jezrael
jezrael

Reputation: 862921

I think you need get mean and std of df, then concat together and reshape by unstack:

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
tuples = list(zip(*arrays))

np.random.seed(1000)
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index)
print(df)
first        bar                 baz                 foo                 qux  \
second       one       two       one       two       one       two       one   
A      -0.804458  0.320932 -0.025483  0.644324 -0.300797  0.389475 -0.107437   
B       0.595036 -0.464668  0.667281 -0.806116 -1.196070 -0.405960 -0.182377   
C      -0.138422  0.705692  1.271795 -0.986747 -0.334835 -0.099482  0.407192   

first             
second       two  
A      -0.479983  
B       0.103193  
C       0.919388  

df = pd.concat([df.mean(), df.std()], keys=('mean','std')).unstack(1)
df.index =  [[0] * len(df.index), ['_'.join((col[1], col[0])) for col in df.index]]
df = df.unstack()
print (df)
first       bar                                     baz                      \
       one_mean   one_std  two_mean   two_std  one_mean   one_std  two_mean   
0     -0.115948  0.700018  0.187319  0.596511  0.637865  0.649139 -0.382846   

first                 foo                                     qux           \
        two_std  one_mean   one_std  two_mean   two_std  one_mean  one_std   
0      0.894129 -0.610567  0.507346 -0.038656  0.401191  0.039126  0.32095   

first                      
       two_mean   two_std  
0      0.180866  0.702911

Upvotes: 2

Related Questions