How to fix json_normalize when it cannot iterate over column to flatten?

Question

I have a dataframe that looks like this:

ID       phone_numbers
1        [{u'updated_at': u'2017-12-02 15:29:54', u'created_at': u'2017-12-0 
          2 15:29:54', u'sms': 0, u'number': u'1112223333', u'consumer_id': 
          12345, u'organization_id': 1, u'active': 1, u'deleted_at': 
           None, u'type': u'default', u'id': 1234}]

I want to take the phone_numbers column and flatten the information inside of it so I can query say the 'id' field.

When I try;

json_normalize(df.phone_numbers)

I get error:

AttributeError: 'str' object has no attribute 'itervalues'

I am not sure why this error is being produced and why I can not flatten this column.

EDIT:

originally JSON string being read from a response object(r.text):

https://docs.google.com/document/d/1Iq4PMcGXWx6O48sWqqYnZjG6UMSZoXfmN1WadQLkWYM/edit?usp=sharing

EDIT:

Converted a column I need to flatten into JSON through this command

a = df.phone_numbers.to_json()

{"0":[{"updated_at":"2018-04-12 12:24:04","created_at":"2018-04-12 12:24:04","sms":0,"number":"","consumer_id":123,"org_id":123,"active":1,"deleted_at":null,"type":"default","id":123}]}

Answer 1

Use list comprehension with flatenning and adding new element ID to dictionary:

df = pd.DataFrame({'ID': [1, 2], 'phone_numbers': [[{'a': '2017', 'b': '2017', 'sms': 1}, 
                                                    {'a': '2018', 'b': '2017', 'sms': 2}], 
                                                  [{'a': '2017', 'b': '2017', 'sms': 3}]]})
print (df)
   ID                                      phone_numbers
0   1  [{'a': '2017', 'b': '2017', 'sms': 1}, {'a': '...
1   2             [{'a': '2017', 'b': '2017', 'sms': 3}]

df = pd.DataFrame([dict(y, ID=i) for i, x in df.values.tolist() for y in x])
print (df)  

   ID     a     b  sms
0   1  2017  2017    1
1   1  2018  2017    2
2   2  2017  2017    3

EDIT:

df = pd.DataFrame({'phone_numbers':{"0":[{"type":"default","id":123}]}})

df = pd.DataFrame([y for x in df['phone_numbers'].values.tolist() for y in x])
print (df) 
    id     type
0  123  default

Answer 2

I am not sure but I think that json normalize expect as first argument a json, not a pd.series, convert the series to a dict or list of dict first. You could use to_dict()

json_normalize(df.phone_numbers.to_dict())

Answer 3

Team:

I think I found the answer to this one. If you dig; you'll see that the output of pd.json_normalize after the first iteration is a STRING. I had success by tricking pandas back into thinking it was a list of dictionaires.

fb_customers = test_schema_a["FBCustomers"].to_list()
fb_customers_b = list(itertools.chain(*fb_customers))
test_schema_b = pd.DataFrame(fb_customers_b)

Answer 4

Simple one liner to flatten a json column / series in pandas.

import json
import pandas as pd

df_flattened_col = pd.json_normalize(df['JSON_COLUMN'].apply(json.loads).tolist())

⭐ Worked for me ⭐

Answer 5

The easiest solution for me was to first load the file with the built-in json package, then use that object with normalize:

Reading from a string:

import json
json_str = '{ "name":"John", "age":30, "city":"New York"}'
data = json.loads(json_str)
df = json_normalize(data)
print(df)

And reading from a file:

import json
                
with open(filepath, 'r') as file:
    data = json.load(file)
    df = json_normalize(data)
    print(df)