2

How do I explode contents in a cell containing a dict, to multiple rows in Pandas?

ID  CODES
A   {"1407273790":5,"1801032636":20,"1174813554":1,"1215470448":2,"1053754655":4,"1891751228":1}
B   {"1497066526":19,"1639360563":16,"1235107087":11,"1033522925":18}
C   {"1154348191":8,"1568410355":4}

How do I explode the codes within " " to multiple rows? The output I am looking is like the table below

ID  CODES
A   1407273790
A   1801032636
A   1174813554
A   1215470448
A   1053754655
A   1891751228
B   1497066526
B   1639360563
B   1235107087
B   1033522925
C   1154348191
C   1568410355
smci
  • 32,567
  • 20
  • 113
  • 146
Santoo
  • 355
  • 2
  • 10

1 Answers1

4

You can use explode after getting the keys(). keys() returns a tuple, so I enclose with [*] to transform to a list, which is the format required for explode. You can also use list():

df = pd.DataFrame({'ID' : ['A', 'B', 'C'],
'CODES' : [{"1407273790":5,"1801032636":20,"1174813554":1,"1215470448":2,"1053754655":4,"1891751228":1},
{"1497066526":19,"1639360563":16,"1235107087":11,"1033522925":18},
{"1154348191":8,"1568410355":4},]})
df['CODES'] = df['CODES'].apply(lambda x: [*x.keys()]) # or lambda x: list(x.keys()))
df = df.explode('CODES')
df
Out[1]: 
  ID       CODES
0  A  1407273790
0  A  1801032636
0  A  1174813554
0  A  1215470448
0  A  1053754655
0  A  1891751228
1  B  1497066526
1  B  1639360563
1  B  1235107087
1  B  1033522925
2  C  1154348191
2  C  1568410355

Per SammyWemmy's comment, you can try the performance of two methods with:

%timeit df['CODES'].apply(lambda x: list(x.keys()))
%timeit [entry.keys() for entry in df.CODES]
David Erickson
  • 16,433
  • 2
  • 19
  • 35
  • 3
    A list comprehension could help with speed, or at the least, as an alternative. Same end point though : ``df.assign(CODES=[entry.keys() for entry in df.CODES]).explode("CODES")`` – sammywemmy Jan 19 '21 at 21:03
  • 1
    @David Erickson and sammywemmy Appreciate it. Thank you very much. – Santoo Jan 20 '21 at 01:24