How to create a data frame from the all possible combination of values of each of the categories listed in the large dictionary

If you have a sufficiently large ^[1] Spark cluster, each list in dictionary can be used as a Spark dataframe and then all these dataframes can be cross-joined:

def to_spark_dfs(dict):
    for key in dict:
        l=[[e] for e in dict[key]]
        yield spark.createDataFrame(l, schema=[key])

dfs=to_spark_dfs(my_dict)

from functools import reduce
res=reduce(lambda df1,df2: df1.crossJoin(df2),dfs)

If the original my_dict is not too large

my_dict= {
    "A":[0,1,2],
    "B":[4,5,6],
    "C":[0,1,2],
    "D":[0,1], 
    "Y":[0,1,2],
    "Z":[0,1],
}

the code produces the expected result:

res.show()
#+---+---+---+---+---+---+
#|  A|  B|  C|  D|  Y|  Z|
#+---+---+---+---+---+---+
#|  0|  4|  0|  0|  0|  0|
#|  0|  4|  0|  0|  0|  1|
#|  0|  4|  0|  0|  1|  0|
#|  0|  4|  0|  0|  1|  1|
#...

res.count()
#324

^[1] Using the numbers given in the comment (80 keys and approx 30 values per key) you would need a really large Spark cluster: 30 ^ 80 gives 1.5*10^118 different combination. This is more than the estimated number of atoms (10^80) in the known, observable universe.

In this case, we have a huge number of possible combinations. For example, if columns (A, B, C... Z) can take values [1...10] then total count of rows are equal 10^26, or 100000000000000000000000000.

In my mind there are 2 main directions to solve this issue:

• Horizontal scaling: calculate and store results using frameworks for distributed computing (such as Apache Spark or Hadoop)
• Vertical scaling: optimize CPU/RAM utilization using:
  • Vectorization (e.g. avoid loops)
  • Data types with minimal impact to RAM allocation (use as minimal precision as you need, use factorize() for strings)
  • mini-batching and download intermediate results (data frames) from RAM to disc in zipped format (e.g. parquet)
  • benchmark the execution time and object size in RAM.

Let me introduce the code that implements some of the concepts of the vertical scaling approach.

Define the following functions:

• create_data_frame_baseline(): data frame generator with loop, not optimal data types (baseline)
• create_data_frame_no_loop(): no loop, not optimal data types
• create_data_frame_optimize_data_type(): no loop, optimal data types.

import itertools as it
import pandas as pd
import numpy as np
from string import ascii_uppercase


def create_letter_dict(cols_n: int = 10, levels_n: int = 6) -> dict:
    letter_dict = {letter: list(range(levels_n)) for letter in ascii_uppercase[0:cols_n]}
    return letter_dict


def create_data_frame_baseline(dict: dict) -> pd.DataFrame:
    df = pd.DataFrame(columns=dict.keys())
    for row in it.product(*dict.values()):
        df.loc[len(df.index)] = row
    
    return df


def create_data_frame_no_loop(dict: dict) -> pd.DataFrame:
    return pd.DataFrame(
        list(it.product(*dict.values())),
        columns=dict.keys()
    )


def create_data_frame_optimize_data_type(dict: dict) -> pd.DataFrame:
    return pd.DataFrame(
        np.int8(list(it.product(*dict.values()))),
        columns=dict.keys()
    )

Benchmarks:

import sys
import timeit

cols_n = 7
levels_n = 5
iteration_n = 2


# Baseline

def create_data_frame_baseline_test():
    my_dict = create_letter_dict(cols_n, levels_n)
    df = create_data_frame_baseline(my_dict)

    assert(df.shape == (levels_n**cols_n, cols_n))
    print(sys.getsizeof(df))

    return df

print(timeit.Timer(create_data_frame_baseline_test).timeit(number=iteration_n))


# No loop, not optimal data types 

def create_data_frame_no_loop_test():
    my_dict = create_letter_dict(cols_n, levels_n)
    df = create_data_frame_no_loop(my_dict)

    assert(df.shape == (levels_n**cols_n, cols_n))
    print(sys.getsizeof(df))

    return df

print(timeit.Timer(create_data_frame_no_loop_test).timeit(number=iteration_n))


# No loop, optimal data types.

def create_data_frame_optimize_data_type_test():
    my_dict = create_letter_dict(cols_n, levels_n)
    df = create_data_frame_optimize_data_type(my_dict)

    assert(df.shape == (levels_n**cols_n, cols_n))
    print(sys.getsizeof(df))

    return df

print(timeit.Timer(create_data_frame_optimize_data_type_test).timeit(number=iteration_n))

Outputs*:

Using create_data_frame_optimize_data_type_test() I generated* 100M rows in less than 100 seconds.

* Ubuntu Server 20.04, Intel(R) Xeon(R) 8xCPU @ 2.60GHz, 32GB RAM

In your case you can not generate all possible combination at once, by using the list() but do it in loop, for example:

import itertools as it
import pandas as pd
from string import ascii_uppercase

N = 36
my_dict = {x: list(range(N)) for x in ascii_uppercase}
df = pd.DataFrame(columns=my_dict.keys())

for row in it.product(*my_dict.values()):
    df.loc[len(df.index)] = row

but of cause it take a long time