Python Pandas - Merge dataFrame on every item in a list

Question

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?

sushanth · Accepted Answer · 2020-07-19 06:28:38Z

3

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

edited Jul 19, 2020 at 6:28

answered Jul 19, 2020 at 6:12

sushanth

8,2923 gold badges20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python Pandas - Merge dataFrame on every item in a list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related