CODEWITHSUNDEEP
pythonpandas
Daniel
Daniel

Reputation: 81

Python Pandas - Merge dataFrame on every item in a list

Let's say that I have Table 'sales':

          ProductId   Sales
0               [1]   $199
1               [2]   $299
2         [3, 4, 5]   $399
3         [6, 7, 8]   $499

and Table 'product':

        ID            Product
0       1                   A
1       2                   B
2       3                   C
3       4                   D
4       5                   E
5       6                   F
6       7                   G
7       8                   H

I want to return a new table 'sales_product'

            Product   Sales
0                 A   $199
1                 B   $299
2             C,D,E   $399
3             F,G,H   $499

So it can find all the matched ProductId and return Product value as a comma-joined string in the new table.

Any Python method can achieve this?

Upvotes: 2

Views: 216

Answers (1)

sushanth
sushanth

Reputation: 8302

Let's try this, create a lookup with ID & Product then map it back to sales dataset.

# {1: 'A', 2: 'B', 3: 'C'...}
lookup = product.set_index("ID")['Product'].to_dict()

sales['Product'] = sales.ProductId.apply(
    lambda x: ",".join([lookup[v] for v in x if lookup.get(v)])
)

  Product Sales
0       A  $199
1       B  $299
2   C,D,E  $399
3   F,G,H  $499

Upvotes: 3

Related Questions