Convert python list of dictionaries to dataframe in pandas

Question

I have a list of dictionaries:

     fruit_list = [
            {
                "id": 1,
                "fruit": {
                    "parent_id": 1,
                    "name": "Banana",
                    "origin": "Brazil"
                }
            },
            {
                "id": 2,
                "fruit": {
                    "parent_id": 1,
                    "name": "Banana",
                    "plural_name": "Bananas",
                    "origin": "Africa"
                }
            },
            {
                "id": 3,
                "fruit": {
                    "parent_id": 2,
                    "name": "Orange",
                    "origin": "Africa"
                }
            },
            {
                "id": 4,
                "fruit": {
                    "parent_id": 2,
                    "name": "Orange",
                    "origin": "Africa"
                }
            },
            {
                "id": 5,
                "fruit": {
                    "parent_id": 3,
                    "name": "Apple",
                    "plural_name": "Apples",
                    "origin": "Africa"
                }
            },
            {
                "id": 6,
                "fruit": {
                    "parent_id": 3,
                    "name": "Apple",
                    "plural_name": "Apples",
                    "origin": "Brazil"
                }
            }
        ]

I want to create a pandas dataframe out of it that looks like this:

parent_id    Brazil    Africa
---------------------------------
1            Banana    Banana
1                      Bananas
2            Orange    Orange
3            Apple     Apple
3            Apples    Apples

It should be able to put out the plural name and add the parent id for that fruit. The problem I am having is that I am only able to get out the "normal" name. Anyone have any tips on doing this in a dynamic and elegant way? Maybe getting the fruits based on their parent ids?

i dont think u have orange in Brazil

sammywemmy
– sammywemmy

2020-04-29 12:44:10 +00:00
Commented Apr 29, 2020 at 12:44 — sammywemmy
– sammywemmy, Commented Apr 29, 2020 at 12:44
haha that does not matter... It is just an example

Artulo
– Artulo

2020-04-29 13:23:21 +00:00
Commented Apr 29, 2020 at 13:23 — Artulo
– Artulo, Commented Apr 29, 2020 at 13:23

Krerkkiat Chusap · Accepted Answer · 2020-04-29 13:28:47Z

1

import pandas as pd

data = {}
for fruit in fruit_list:
    parent_id = str(fruit['fruit']['parent_id'])
    if parent_id not in data.keys():
        data[parent_id] = dict()

    if 'plural_name' in fruit['fruit'].keys():
        if parent_id + '_p' not in data.keys():
            data[parent_id + '_p'] = dict()

        data[parent_id + '_p']['parent_id'] = parent_id
        data[parent_id + '_p'][fruit['fruit']['origin']] = fruit['fruit']['plural_name']

        data[parent_id]['parent_id'] = parent_id
        data[parent_id][fruit['fruit']['origin']] = fruit['fruit']['name']
    else:
        data[parent_id]['parent_id'] = parent_id
        data[parent_id][fruit['fruit']['origin']] = fruit['fruit']['name']

list_of_data = []
for key, value in data.items():
    list_of_data.append(value)

df = pd.DataFrame(list_of_data)

using the data provided the output is

>>> df
  parent_id  Brazil   Africa
0         1  Banana   Banana
1         1     NaN  Bananas
2         2     NaN   Orange
3         3   Apple    Apple
4         3  Apples   Apples

NaN can later be replaced to an empty string with

df.fillna('')

answered Apr 29, 2020 at 13:28

Krerkkiat Chusap

3466 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Artulo Over a year ago

I am trying to test your solution, but I keep getting : TypeError: 'list' object is not callable on data[parent_id] = dict() the first 5 lines. I dont think you can assign parent id like that to the list, if that is what you are trying to do?

Krerkkiat Chusap Over a year ago

It seems that you name a variable dict which overwrites the default name that is the dictionary constructor. Change the dict name of your variable to something else. The code is working fine on its own with just the fruit_list added.

Artulo Over a year ago

Oh gosh. That was embarrassing. Thanks! It works now. Thanks :)!

Collectives™ on Stack Overflow

Convert python list of dictionaries to dataframe in pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related