Normalize column with JSON data in Pandas dataframe

user_9582610 0
Viewed 8986

I have a Pandas dataframe in which one column contains JSON data (the JSON structure is simple: only one level, there is no nested data):

ID,Date,attributes
9001,2020-07-01T00:00:06Z,"{"State":"FL","Source":"Android","Request":"0.001"}"
9002,2020-07-01T00:00:33Z,"{"State":"NY","Source":"Android","Request":"0.001"}"
9003,2020-07-01T00:07:19Z,"{"State":"FL","Source":"ios","Request":"0.001"}"
9004,2020-07-01T00:11:30Z,"{"State":"NY","Source":"windows","Request":"0.001"}"
9005,2020-07-01T00:15:23Z,"{"State":"FL","Source":"ios","Request":"0.001"}"

My Pandas dataframe

I would like to normalize the JSON content in the attributes column so the JSON attributes become each a column in the dataframe.

ID,Date,attributes.State, attributes.Source, attributes.Request
9001,2020-07-01T00:00:06Z,FL,Android,0.001
9002,2020-07-01T00:00:33Z,NY,Android,0.001
9003,2020-07-01T00:07:19Z,FL,ios,0.001
9004,2020-07-01T00:11:30Z,NY,windows,0.001
9005,2020-07-01T00:15:23Z,FL,ios,0.001

I have been trying using Pandas json_normalize which requires a dictionary. So, I figure I would convert the attributes column to a dictionary but it does not quite work out as expected for the dictionary has the form:

df.attributes.to_dict()

{0: '{"State":"FL","Source":"Android","Request":"0.001"}',
 1: '{"State":"NY","Source":"Android","Request":"0.001"}',
 2: '{"State":"FL","Source":"ios","Request":"0.001"}',
 3: '{"State":"NY","Source":"windows","Request":"0.001"}',
 4: '{"State":"FL","Source":"ios","Request":"0.001"}'}

And the normalization takes the key (0, 1, 2, ...) as the column name instead of the JSON keys.

I have the feeling that I am close but I can't quite work out how to do this exactly. Any idea is welcome.

Thank you!

pandas normalize dataframe python json
3 Answers

Normalize expects to work on an object, not a string.

import json
import pandas as pd
df_final = pd.json_normalize(df.attributes.apply(json.loads))
user_33658860

You shouldn’t need to convert to a dictionary first.

Try:

import pandas as pd

pd.json_normalize(df[‘attributes’])
user_140413760

I found an solution but I am not overly happy with it. I reckon it is very inefficient.

import pandas as pd
import json

# Import full dataframe
df = pd.read_csv(r'D:/tmp/sample_simple.csv', parse_dates=['Date'])

# Create empty dataframe to hold the results of data conversion
df_attributes = pd.DataFrame()

# Loop through the data to fill the dataframe
for index in df.index:
    row_json = json.loads(df.attributes[index])
    normalized_row = pd.json_normalize(row_json)
    df_attributes = df_attributes.append(normalized_row)

# Reset the index of the attributes dataframe
df_attributes = df_attributes.reset_index(drop=True)

# Drop the original attributes column
df = df.drop(columns=['attributes'])

# Join the results
df_final = df.join(df_attributes)

# Show results
print(df_final)
print(df_final.info())

Which gives me the expected result. However, as I said, there are several inefficiencies in it. For starters, the dataframe append in the for loop. According to the documentation the best practice is to make a list and then append but I could not figure out how to do that while keeping the shape I wanted. I welcome all critics and ideas.

user_95826100
Related