Every product/combination of nested dictionaries saved to DataFrame

Question

I'm new to Python and initializing parameters for a X number of model runs. I need to create every possible combination from N dictionaries, each dictionary having nested data.

I know that I need to use itertools.product somehow, but I'm stuck on how to navigate the dictionaries. Maybe I shouldn't even be using dictionaries but json or something. I also know that this will create A LOT of parameters/runs.

EDIT: added clarification from comment. I want to create a function that takes n dictionaries ---eg. def func(dict*) ---- as input and creates every possible combination of all of those individual key/ value pairs across all of the dictionaries, returning one big DF with all combinations.

My data looks like this:

DICTIONARY 1

{
    "chisel": [
        {"type": "chisel"},
        {"depth": [152, 178, 203]},
        {"residue incorporation": [0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0]},
        {"timing": ["10-nov", "10-apr"]},
    ],
    "disc": [
        {"type": "disc"},
        {"depth": [127, 152, 178, 203]},
        {"residue incorporation": [0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0]},
        {"timing": ["10-nov", "10-apr"]},
    ],
    "no_till": [
        {"type": "user_defined"},
        {"depth": [0]},
        {"residue incorporation": [0.0]},
        {"timing": ["10-apr"]},
    ],
}

DICTIONARY 2

{
    "nh4_n":
        {
            "kg/ha":[110, 115, 120, 125, 130, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 185, 190, 195, 200, 205, 210, 215, 220, 225],
            "fertilize_on":"10-apr"
        },
    "urea_n":
        {
            "kg/ha":[110, 115, 120, 125, 130, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 185, 190, 195, 200, 205, 210, 215, 220, 225],
            "fertilize_on":"10-apr"
        }
}

DICTIONARY 3

{
    "maize": {
        "sow_crop": 'maize',
        "cultivar": ['B_105', 'B_110'],
        "planting_dates": [
            '20-apr', '27-apr', '4-may', '11-may', '18-may', '25-may', '1-jun', '8-jun', '15-jun'],
        "sowing_density": [8],
        "sowing_depth": [51],
        "harvest": ['maize'],
    }
}

For example, with the three dictionaries above, I would take the dict 'chisel' and itertools.product it somehow with each nested dictionary in dict 2(eg. 'nh4_n') and each nested dict in dict 3 (in this case there is only one, so with each different cultivar, planting date, etc.). I also want to use the keys in each key-value pair as a DF column heading.

Answer 1

Issues:

• The main issue, is the inconsistency of the data dict formats:

  1. unlike dict 1 & 3, the top key of dict 2 is not the value of a sub-key
  2. unlike dict 2 & 3, which have dicts as values of the top key, dict 1 has a list of dicts for the top level value.
  3. some 2nd level values are strings and some are lists

---

Step 1: Fix the data:

Functions:

fix_list_dicts:

def fix_list_dicts(data: dict) -> dict:
    """
    Given a dict where the values are a list of dicts:
    (1) convert the value to a dict of dicts
    (2) if any second level value is a str, convert it to a list
    """
    data_new = dict()
    for k, v in data.items():
        v_new = dict()
        for x in v:
            for k1, v1 in x.items():
                if type(v1) != list:
                    x[k1] = [v1]
            v_new.update(x)
        data_new[k] = v_new
    return data_new

add_top_key_as_value:

def add_top_key_as_value(data: dict, new_key: str) -> dict:
    """
    Given a dict of dicts, where top key is not a 2nd level value:
    (1) add new key: value pair to second level
    """
    for k, v in data.items():
        v.update({new_key: k})
        data[k] = v
    return data

str_value_to_list:

def str_value_to_list(data: dict) -> dict:
    """
    Given a dict of dicts:
    (1) Convert any second level value from str to list
    """    
    for k, v in data.items():
        for k2, v2 in v.items():
            if type(v2) != list:
                data[k][k2] = [v2]
    return data 

Implementation:

from pprint import pprint as pp

Dictionary 1:

d1 = fix_list_dicts(d1)
pp(d1)

{'chisel': {'depth': [152, 178, 203],
            'residue incorporation': [0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0],
            'timing': ['10-nov', '10-apr'],
            'type': ['chisel']},
 'disc': {'depth': [127, 152, 178, 203],
          'residue incorporation': [0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0],
          'timing': ['10-nov', '10-apr'],
          'type': ['disc']},
 'no_till': {'depth': [0],
             'residue incorporation': [0.0],
             'timing': ['10-apr'],
             'type': ['user_defined']}}

Dictionary 2:

d2 = add_top_key_as_value(d2, 'fertilizer')
d2 = str_value_to_list(d2)

{'nh4_n': {'fertilize_on': ['10-apr'],
           'fertilizer': ['nh4_n'],
           'kg/ha': [110, 115, 120, 125, 130, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 185, 190, 195, 200, 205, 210, 215, 220, 225]},
 'urea_n': {'fertilize_on': ['10-apr'],
            'fertilizer': ['urea_n'],
            'kg/ha': [110, 115, 120, 125, 130, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 185, 190, 195, 200, 205, 210, 215, 220, 225]}}

Dictionary 3:

d3 = str_value_to_list(d3)

{'maize': {'cultivar': ['B_105', 'B_110'],
           'harvest': ['maize'],
           'planting_dates': ['20-apr', '27-apr', '4-may', '11-may', '18-may', '25-may', '1-jun', '8-jun', '15-jun'],
           'sow_crop': ['maize'],
           'sowing_density': [8],
           'sowing_depth': [51]}}

---

Step 2: Combine the data into DataFrames:

Functions:

import pandas as pd

combine_the_data:

def combine_the_data(data: list) -> dict:
    """
    Given a list of dicts:
    (1) convert each dict into DataFrame
    (2) set the indices to 0
    (3) add each DataFrame to df_dict
    """
    df_dict = dict()
    for i, d in enumerate(data):
        df = pd.DataFrame.from_dict(d, orient='index')
        df.index = [0 for _ in range(len(df))]
        df_dict[f'd_{i}'] = df

    return df_dict

merge_df_dict:

def merge_df_dict(data: dict) -> pd.DataFrame:
    """
    Given a dict of DataFrames
    (1) merge them on the index
    """
    df = pd.DataFrame()
    for _, v in data.items():
        df = df.merge(v, how='outer', left_index=True, right_index=True)
    return df

Implementation:

data = [d1, d2, d3]
df_dict = combine_the_data(data)

df_dict['d_0']

df_dict['d_1']

df_dict['d_2']

df = merge_df_dict(df_dict)

---

Step 3: Use pd.DataFrame.explode to EXPLODE all the lists:

• I don't know what other new features are part of pandas v0.25, but explode is the finest of them.
• Don't have pandas v0.25? Then get it!

df.reset_index(drop=True, inplace=True)  # the DataFrame must have a unique 0...x index

for col in df.columns:
    df = df.explode(col).reset_index(drop=True)

Final Output of all combinations:

Value Counts and Expectations:

Given:

• len(kg/ha) = 24
• len(cultivar) = 2
• len(plantint_dates) = 9

• Number of user_defined rows = 2

• Total combinations for user_defined = 864

• I didn't manually calculate the other two types, but since user_defined has the correct number of combinations, I expect the others do as well.

df.type.value_counts()

disc            48384
chisel          36288
user_defined      864
Name: type, dtype: int64