Explode cell containing dict into multiple rows in Pandas [duplicate]
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
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]