Transform Json strings in dataframe to multiple columns

Question

I am trying to convert a column that has json strings into multiple columns from the json data. Example data :

       1
  0
id1    {"c1": ["a","b"], "c2": [.1, .2], "c3": ["3","1"]}
id2    {"c1": ["c","d"], "c2": [.7, .4], "c3": ["8","4"]}

How would I turn it into :

    c1  c2  c3
id1 a  .1   3
    b  .2   1
id2 c  .7   8
    d  .4   4

How to achieve this with pandas functions only?

Are source data json and then call pd.DataFrame(json) ?

jezrael
– jezrael

2019-02-19 12:48:38 +00:00
Commented Feb 19, 2019 at 12:48 — jezrael
– jezrael, Commented Feb 19, 2019 at 12:48

jezrael · Accepted Answer · 2019-02-19 12:54:01Z

2

Use dictionary comprehension with ast.literal_eval for convert strings to dictioanries and lists:

import ast

df = pd.concat({k: pd.DataFrame(ast.literal_eval(v)) for k, v in df[1].items()})
print (df)
      c1   c2 c3
id1 0  a  0.1  3
    1  b  0.2  1
id2 0  c  0.7  8
    1  d  0.4  4

If need remove MultiIndex:

df = df.reset_index(level=1, drop=True)
print (df)
    c1   c2 c3
id1  a  0.1  3
id1  b  0.2  1
id2  c  0.7  8
id2  d  0.4  4

answered Feb 19, 2019 at 12:54

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Transform Json strings in dataframe to multiple columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related