How do I convert a Pandas Column to .JSON?

I have data of string in a pandas dataframe column. I need to convert it to either parsable json string or dict type so that I can read / extract values from it.

Mock / sample DataFrame:

df = pd.DataFrame({'col1': [6010, 6015, 6020, 6025],
                   'json_col': ["{'Id': '060',
                                  'Date': '20210908',
                                  'value': {'Id': '060',
                                            'Code': '06037'}
                                  }",

                                  "{'Id': '061',
                                    'Date': '20210908',
                                    'value': {'Id': '060',
                                              'Code': '06038'}
                                   }",

                                   np.nan,

                                   "{'Id': '063',
                                     'Date': '20210908',
                                     'value': {'Id': '060',
                                               'Code': '06040'}
                                   }"],
            })

Here’s what I tried:

df['json_col'] = df[df['json_col'].notnull()].map(lambda x: dict(eval(x)))

TypeError: eval() arg 1 must be a string, bytes or code object

Note, there are some missing values in the column and the function need to be able to handle them.

Answer

When you apply a mask like df[df['json_col'].notnull()], this result includes all columns – even though you used a specific column to determine the mask – because you’re simply telling it which rows to use (the ones where that column isn’t null). The 'col1' column values presumably aren’t strings in your actual data. This way worked for me:

df['json_col'] = df[df['json_col'].notnull()]['json_col'].map(lambda x: dict(eval(x)))

Alternately, you can pull the test into the lambda:

df['json_col'] = df['json_col'].map(lambda x: dict(eval(x)) if pd.notnull(x) else x)

That said, please use ast.literal_eval for this kind of evaluation, if you can’t just make sure of having real JSON ahead of time.