0

I have currently a pandas dataframe with 100+ columns, that was achieved from pd.normalize_json() and there is one particular column (children) that looks something like this:

name       age    children                                               address... 100 more columns
Mathew      20     [{name: Sam, age:5}, {name:Ben, age: 10}]                    UK
Linda       30     []                                                           USA

What I would like for the dataframe to look like is:

name       age    children.name    children.age                     address... 100 more columns
Mathew      20     Sam                  5                                        UK
Mathew      20     Ben                  10                                       UK
Linda       30                                                                   USA

There can be any number of dictionaries within the list. Thanks for the help in advance!

Janet
  • 33
  • 1
  • 5
  • 1
    https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.explode.html – Mehdi Khademloo Jun 25 '21 at 08:27
  • @MehdiKhademloo hi, thank you for the link. I managed to put the various dictionaries on separate rows. Could you kindly tell me how I can now split the dictionary on each row up? Along with retaining the key naming of the dictionary as how I've shown above. – Janet Jun 25 '21 at 09:06
  • https://stackoverflow.com/questions/59718909/explode-dict-from-pandas-column – Mehdi Khademloo Jun 25 '21 at 09:12
  • @MehdiKhademloo thanks for the link, but it didn't solve the fact that I have rows with no values. Doing pd.json_normalize on a column with NaN values didn't work and i can't drop them, since the entire row would vanish – Janet Jun 25 '21 at 10:29

0 Answers0