CODEWITHSUNDEEP
pythonpandasdictionarymulti-index
Bram Vanroy
Bram Vanroy

Reputation: 28534

Adding values to multiindex dataframe by column name as well as multi index

I am still quite confused about the workings of multiindex in Pandas. I created a multi index as follows:

import pandas as pd
import numpy as np

arrays = [np.array(['pearson', 'pearson', 'pearson', 'pearson', 'spearman', 'spearman',
                    'spearman', 'spearman', 'kendall', 'kendall', 'kendall', 'kendall']),
          np.array(['PROFESSIONAL', 'PROFESSIONAL', 'STUDENT', 'STUDENT',
                    'PROFESSIONAL', 'PROFESSIONAL', 'STUDENT', 'STUDENT',
                    'PROFESSIONAL', 'PROFESSIONAL', 'STUDENT', 'STUDENT']),
          np.array(['r', 'p', 'r', 'p', 'rho', 'p', 'rho', 'p', 'tau', 'p', 'tau', 'p'])]

tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['correlator', 'expertise', 'coeff-p'])

then I made an empty DataFrame out of them and added a column name 'pair':

results_df = pd.DataFrame(index=index)
results_df.columns.names = ['pair']

Filled with some toy data (results_df['attr1-attr2'] = [1,2,3,4,5,6,7,8,9,10,11,12]), it looks like this: 

pair                             attr1-attr2
correlator expertise    coeff-p             
pearson    PROFESSIONAL r                  1
                        p                  2
           STUDENT      r                  3
                        p                  4
spearman   PROFESSIONAL rho                5
                        p                  6
           STUDENT      rho                7
                        p                  8
kendall    PROFESSIONAL tau                9
                        p                 10
           STUDENT      tau               11
                        p                 12

However, instead of the dummy I want to add values from a dictionary. The dictionary has entries that look like this, for each attr-attr pair:

'attr-attr': {
  'pearson': {
    'STUDENT': {
      'r': VALUE,
      'p': VALUE
    },
    'PROFESSIONAL': {
      'r': VALUE,
      'p': VALUE
    }
  },
  'spearman': {
    'STUDENT': {
      'r': VALUE,
      'p': VALUE
    },
    'PROFESSIONAL': {
      'r': VALUE,
      'p': VALUE
    }
  }
  'kendall': {
    'STUDENT': {
      'r': VALUE,
      'p': VALUE
    },
    'PROFESSIONAL': {
      'r': VALUE,
      'p': VALUE
    }
  }
}

Actual example data below for you to work with:

correlations = {'NormNedit-NormEC_TOT': {'pearson': {'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}, 'kendall': {'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormLiteral-NormEC_TOT': {'pearson': {'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}, 'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'kendall': {'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormHTra-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'kendall': {'STUDENT': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormScatter-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}, 'kendall': {'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormCrossS-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}, 'PROFESSIONAL': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}, 'PROFESSIONAL': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'kendall': {'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}, 'NormPdur-NormEC_TOT': {'pearson': {'STUDENT': {'r': 0.13615071018351657, 'p': 0.0002409555504769095}, 'PROFESSIONAL': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'spearman': {'STUDENT': {'rho': 0.10867061294616957, 'p': 0.003437711066527592}}, 'kendall': {'PROFESSIONAL': {'tau': 0.08185775947238913, 'p': 0.003435247172206748}}}}

So for each 'attr-attr' (topmost key) as column name, I want to add its values to the corresponding row in the multi index. However, I can't seem to find a way to do this in an efficient way. Missing values should be np.nan. I tried looping the dictionary and using query()[] but that didn't work.

for attr, attr_d in correlations.items():
    for correl, correl_d in attr_d.items():
        for split, split_d in correl_d.items():
            results_df.query(f"correlator == {correl} and expertise == {split} and coeff_p == 'p'")[attr] = split_d['p']
            results_df.query(f"correlator == {correl} and expertise == {split} and coeff_p != 'p'")[attr] = split_d['r'] if 'r' in split_d else split_d['rho'] if 'rho' in split_d else split['tau']

> pandas.core.computation.ops.UndefinedVariableError: name 'pearson' is not defined

I am aware that the data is relatively complex, so if something is not clear please let me know.

Upvotes: 2

Views: 486

Answers (1)

ASGM
ASGM

Reputation: 11391

You can adapt Wouter Overmeire's answer to this question to make a multi-indexed dataframe out of your nested dictionary:

d = correlations
df = pd.DataFrame.from_dict({(i,j,k): d[i][j][k]
   for i in d.keys() 
   for j in d[i].keys()
   for k in d[i][j].keys()
   }, orient='index').stack()

Then if you want the columns to come from the highest level of the nested dictionary (the attr-attr level), you can unstack the result:

df = df.unstack(level=0)

Note: I think there's a mistake in your sample data, where 'PROFESSIONAL': {'STUDENT': .... If that's not a mistake, and I'm just misunderstanding something, let me know.

Upvotes: 1

Related Questions