Transforming list with key-values into Excel with Pandas-CodePudding

If I have this structure:

 [{'name': 'Paul', 'supporterId': 16, 'email': '[email protected]', 'data': [{'name_dependent': 'George', 'email': '[email protected]', 'supporterId': 16},{'name_dependent': 'Ana', 'email': '[email protected]', 'supporterId': 2}]}]

How can I export it to excel selecting only the keys I want to turn into columns? For example I want only name,email,name dependent and email to be in my excel with its values...

CodePudding user response：

You can start with pd.json_normalize() and follow it up with some custom made columns.

df = pd.json_normalize(data).explode('data')
df['name_dependent'] = df['data'].map(lambda x: x['name_dependent'])
df['email'] = df['data'].map(lambda x: x['email'])

Outputting:

   name  supporterId             email name_dependent
0  Paul           16  [email protected]         George
0  Paul           16     [email protected]            Ana

CodePudding user response：

Try this

d = [["name1", 21, "[email protected]"],
     ["name2", 22, "[email protected]"], 
     ["name3", 23, "[email protected]"]
    ]
rows = [i for i in range(len(d[0]))]
cols = ["name", "id", "email"]
df = pd.DataFrame(d, rows, cols)

cols is your keys

Output :

    name  id   email

0  name1  21  [email protected]
1  name2  22  [email protected]
2  name3  23  [email protected]