0

I have a table like this:

Group Item
A a, b, c
B b, c, d

And I want to convert to like this:

Item Group
a A
b A, B
c A, B
d B

What is the best way to achieve this?

Thank you!!

Sayandip Dutta
  • 15,602
  • 4
  • 23
  • 52
palapara
  • 15
  • 1

4 Answers4

0

If you are working in pandas, you can use 'explode' to unpack items, and can use 'to_list' lambda for the grouping stage.

Here is some info on 'explode' method https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.explode.html.

import pandas as pd
df = pd.DataFrame(data={'Group': ['A', 'B'], 'Item': [['a','b','c'], ['b','c','d']]})

Exploding

df.explode('Item').reset_index(drop=True).to_dict(orient='records')
[{'Group': 'A', 'Item': 'a'},
 {'Group': 'A', 'Item': 'b'},
 {'Group': 'A', 'Item': 'c'},
 {'Group': 'B', 'Item': 'b'},
 {'Group': 'B', 'Item': 'c'},
 {'Group': 'B', 'Item': 'd'}]

Exploding and then using 'to_list' lambda

df.explode('Item').groupby('Item')['Group'].apply(lambda x: x.tolist()).reset_index().to_dict(orient='records')
[{'Item': 'a', 'Group': ['A']},
 {'Item': 'b', 'Group': ['A', 'B']},
 {'Item': 'c', 'Group': ['A', 'B']},
 {'Item': 'd', 'Group': ['B']}]
oli5679
  • 1,709
  • 1
  • 22
  • 34
0

Not the most efficient, but very short:

>>> table = {'A': ['a', 'b', 'c'], 'B': ['b', 'c', 'd']}
>>> reversed_table = {v: [k for k, vs in table.items() if v in vs] for v in set(v for vs in table.values() for v in vs)}
>>> print(reversed_table)
{'b': ['A', 'B'], 'c': ['A', 'B'], 'd': ['B'], 'a': ['A']}
Lucas Moeskops
  • 5,445
  • 3
  • 28
  • 42
0

With dictionaries, you wouldtypically approach it like this:

table = {'A': ['a', 'b', 'c'], 'B': ['b', 'c', 'd']}

revtable = dict()
for v,keys in table.items():
    for k in keys:
        revtable.setdefault(k,[]).append(v)

print(revtable)
# {'a': ['A'], 'b': ['A', 'B'], 'c': ['A', 'B'], 'd': ['B']}
Alain T.
  • 40,517
  • 4
  • 31
  • 51
-1

Assuming that your tables are in the form of a pandas dataframe, you could try something like this:

import pandas as pd
import numpy as np

# Create initial dataframe
data = {'Group': ['A', 'B'], 'Item': [['a','b','c'], ['b','c','d']]}
df = pd.DataFrame(data=data)

    Group   Item
0   A   [a, b, c]
1   B   [b, c, d]

# Expand number of rows based on list column ("Item") contents
list_col = 'Item'
df = pd.DataFrame({
      col:np.repeat(df[col].values, df[list_col].str.len())
      for col in df.columns.drop(list_col)}
    ).assign(**{list_col:np.concatenate(df[list_col].values)})[df.columns]

    Group   Item
0   A       a
1   A       b
2   A       c
3   B       b
4   B       c
5   B       d

*Above snippet taken from here, which includes a more detailed explanation of the code

# Perform groupby operation 
df = df.groupby('Item')['Group'].apply(list).reset_index(name='Group')

    Item    Group
0   a     [A]
1   b     [A, B]
2   c     [A, B]
3   d     [B]
mschoder
  • 44
  • 3