Json normalize an array of objects

Question

i need some help to normalize a json structure and convert it to dataframe

this is my example:

# importing the libraries used
import pandas as pd

# initializing the data
data = {
    'company': 'XYZ pvt ltd',
    'location': 'London',
    'info': [
        {
            'president': 'Rakesh Kapoor',
            'contacts': {
                'email': 'contact@xyz.com',
                'tel': '9876543210'
            }
        },
        {
            'president': 'Julio Vasquez',
            'contacts': {
                'email': 'julio@test.com',
                'tel': '987987654'
            }
        }
    ]
}

print(pd.json_normalize(data))

and this the output:

What I have to do to see a table like:

The final idea is make some sql querys over the last table.

Regards

Answer 1

You could use record_path and meta parameters of json_normalize:

out = pd.json_normalize(data, record_path=['info'], meta=['company', 'location'], record_prefix='info.')

Output:

  info.president info.contacts.email info.contacts.tel      company location
0  Rakesh Kapoor     contact@xyz.com        9876543210  XYZ pvt ltd   London
1  Julio Vasquez      julio@test.com         987987654  XYZ pvt ltd   London