CODEWITHSUNDEEP
pythonpandasalteryx
ba90343
ba90343

Reputation: 9

Adding information from a smaller table to a large one with Pandas

I would like to add the regional information to the main table that contains entity and account columns. In this way, each row in the main table should be duplicated, just like the append tool in Alteryx.

Is there a way to do this operation with Pandas in Python?

Thanks!

enter image description here

Upvotes: 0

Views: 199

Answers (1)

dboy
dboy

Reputation: 1057

Unfortunately no build-in method exist, as you'll need to build cartesian product of those DataFrame check that fancy explanation of merge DataFrames in pandas

But for your specific problem, try this:

import pandas as pd
import numpy as np

df1 = pd.DataFrame(columns=['Entity', 'Account'])
df1.Entity = ['Entity1', 'Entity1']
df1.Account = ['Sales', 'Cost']

df2 = pd.DataFrame(columns=['Region'])
df2.Region = ['North America', 'Europa', 'Asia']

def cartesian_product_simplified(left, right):
    la, lb = len(left), len(right)
    ia2, ib2 = np.broadcast_arrays(*np.ogrid[:la,:lb])

    return pd.DataFrame(
        np.column_stack([left.values[ia2.ravel()], right.values[ib2.ravel()]]))

resultdf = cartesian_product_simplified(df1, df2)
print(resultdf)

output:

         0      1              2
0  Entity1  Sales  North America
1  Entity1  Sales         Europa
2  Entity1  Sales           Asia
3  Entity1   Cost  North America
4  Entity1   Cost         Europa
5  Entity1   Cost           Asia

as expected.

Upvotes: 0

Related Questions