Python - Convert the value of a column from a DataFrame to different columns

user_10933668 0
Viewed 48

See if you can help me, I currently have a JSON file (the content of this file is actually the response of a GET request so I cannot modify it):

[
    {
        
        "date": "1620157078",
        "value": "{\"license_plate\": \"1111 AAA\", \"brand\": \"Peugeot\"}"
    },
    {
        "date": "1620157080",
        "value": "{\"license_plate\": \"2222 BBB\", \"brand\": \"Audi\"}"
    }
]

And what I'm doing is converting it into a dataframe to be able to work with it in the following way:

import json
import pandas as pd

with open('test.json', 'r') as f:
    file = f.read()
    file_json = json.loads(file)

df = pd.DataFrame(file_json)

df

DataFrame content:

    date        value
0   1620157078  {"license_plate": "1111 AAA", "brand": "Peugeot"}
1   1620157080  {"license_plate": "2222 BBB", "brand": "Audi"}

And what I need is to convert the value of the value field which is a string in more columns, to end up having one like this:

    date        license_plate   brand
0   1620157078  1111 AAA        Peugeot
1   1620157080  2222 BBB        Audi

I hope you can help me, I have been for almost a whole day trying to make that modification but I have been able. Thanks in advance.

pandas dataframe python json
3 Answers

Your json has value field as strings, not dict. You can use ast.literal_eval to turn them into dictionaries:

from ast import literal_eval
df = pd.DataFrame(file_json)

(df.drop('value', axis=1)
   .join(pd.DataFrame(df['value'].apply(literal_eval).to_list()))
)
user_42384080

A built-in and (Pandas-upstream) solution consists of recreating your file_json's dictionaries using list comprehension and dictionary unpacking.

>>> from ast import literal_eval
>>> file_json_2 = [{'date': d['date'], **literal_eval(d['value'])} for d in file_json]

and then, as you already do

>>> pd.DataFrame(file_json_2)
         date license_plate    brand
0  1620157078      1111 AAA  Peugeot
1  1620157080      2222 BBB     Audi
user_41940790 
df = pd.concat([df, df.pop("value").apply(json.loads).apply(pd.Series)], axis=1)
print(df)

Prints:

         date license_plate    brand
0  1620157078      1111 AAA  Peugeot
1  1620157080      2222 BBB     Audi
user_100359850
Related