What's the fastest way to vectorise creation of combinatorial DataFrame?-CodePudding

How can I create the master DataFrame through some vectorised process? If it's not possible, what's the most time efficient (not concerned about memory) method to execute this operation?

Can the for-loop be replaced for something more efficient?

As you can see, combinations very quickly produces very large number, thus I need a fast way to produce this DataFrame.

Please see below a minimum reproducible example:

%%time

import pandas as pd
import string
import numpy as np
from itertools import combinations

# create dummy data
cols = list(string.ascii_uppercase)
dummy = pd.DataFrame()
for col in cols:
    dummy = dummy.append([[col, 0]   np.random.randint(2, 100, size=(1, 10)).tolist()[0]])
    dummy = dummy.append([[col, 1]   np.random.randint(2, 100, size=(1, 10)).tolist()[0]])
    dummy = dummy.append([[col, 2]   np.random.randint(2, 100, size=(1, 10)).tolist()[0]])
dummy.columns=['name', 'id', 'v1', 'v2', 'v3', 'v4', 'v5', 'v1', 'v6', 'v7', 'v8', 'v9']

# create all possible unique combinations
combos = list(combinations(cols, 2))

# generate DataFrame with all combinations
master = pd.DataFrame()
for i, combo in enumerate(combos):
    A = dummy[dummy.name == combo[0]]
    B = dummy[dummy.name == combo[1]]
    joined = pd.merge(A, B, on=["id"], suffixes=('_A', '_B'))
    joined = joined.sort_values("id")
    joined['pair_id'] = i
    master = pd.concat([master, joined])

Output:

CPU times: total: 1.8 s
Wall time: 1.8 s

Thanks!

CodePudding user response：

Since your data is structural, you can drop down to numpy to take advantage of vectorized operations.

names = list(string.ascii_uppercase)
ids = [0, 1, 2]
columns = pd.Series(["v1", "v2", "v3", "v4", "v5", "v1", "v6", "v7", "v8", "v9"])

# Generate the random data
data = np.random.randint(2, 100, (len(names), len(ids), len(columns)))

# Pair data for every 2-combination of names
arr = [np.hstack([data[i], data[j]]) for i,j in combinations(range(len(names)), 2)]

# Assembling the data to final dataframe
idx = pd.MultiIndex.from_tuples([
    (p,a,b,i) for p, (a, b) in enumerate(combinations(names,2)) for i in ids
], names=["pair_id", "name_A", "name_B", "id"])
cols = pd.concat([columns   "_A", columns   "_B"])

master = pd.DataFrame(np.vstack(arr), index=idx, columns=cols)

Original code: 4s. New code: 7ms