Convert List of Dictionaries to a Pandas Dataframe

Convert list of dictionaries to a pandas DataFrame

If ds is a list of dicts:

df = pd.DataFrame(ds)

Note: this does not work with nested data.

How to convert a list of dictionaries to a Pandas DataFrame

According to the source code, Game object have a method to_dict:

Try:

df = pd.DataFrame([game.to_dict() for game in api_response])

Note:

This problem has been discussed previously, see for example here. In order for pandas to convert this to a DataFrame we need to make sure that we're actually dealing with a list of dictionaries.

Usually we might print our api_response and look at the data. In this case, this is not enough. Because even though api_response looks (read more about __repr__ here) like a list of dictionaries, it's actually a list of Game objects.

We can learn this by printing the type of the first element in our list:

>>> print(type(api_response[0]))
<class 'cfbd.models.game.Game'>

Some classes will have a to_dict method attached to them. If they don't, you can use vars instead:

df = pd.DataFrame([vars(game) for game in api_response])

How to convert a list of dictionaries to a Pandas Dataframe with one of the values as column name?

From the column2, use tolist and recreate a dataframe that you stack to get one dictionary {'id':...,'value':...} per row.

s = pd.DataFrame(df['column2'].tolist()).stack()
print(s)
# 0  0    {'id': 1144801690551941, 'value': 20}
#    1    {'id': 8202109018383881, 'value': 26}
#    2    {'id': 3025222222235562, 'value': 37}
#    3    {'id': 5834245818862827, 'value': 35}
#    4    {'id': 4689782481420271, 'value': 27}
#    5    {'id': 7385168421196875, 'value': 56}
# 1  0    {'id': 1144801690551941, 'value': 25}
#    1    {'id': 8202109018383881, 'value': 26}

Then from there, use again tolist on this Series s and create a Dataframe, ensure to keep the original index. Append the column id just created with set_index, and unstack to get all id number as column header. You get the wanted shape for the id-value. Just need to join to column1.

res = (
    df[['column1']]
      .join(pd.DataFrame(s.tolist(), 
                         s.index.get_level_values(0)) # keep original index
              .set_index('id', append=True)
              ['value'].unstack()
              .rename_axis(columns=None))
)

and you get as expected

print(res)
   column1  1144801690551941  3025222222235562  4689782481420271  \
0        1                20                37                27   
1        2                25                38                21   
2        3                20                37                27   

   5834245818862827  7385168421196875  8202109018383881  
0                35                56                26  
1                35                53                26  
2                32                50                29  

Converting a list of lists of dictionaries to a Pandas DataFrame

Since every entry is a individual dict, you can join them using list+dict comprehension:

df = pd.DataFrame([{k: v for d in i for k, v in d.items()} for i in l])

print (df)

                       name      filter  number of cigarette  nicotine content  tar content  menthol  king size  price  units sold per week  profits per week
0           Export A Smooth  unfiltered                   25              10.5         15.0    False      False  18.99                   50            949.50
1           Export A Medium       white                   25              10.0         12.0    False      False  18.99                   39            740.61
2  Canadian Classics Select       brown                   25              11.1         11.0     True       True  19.09                   38            725.42

Converting list of dictionaries into Dataframe in sane way

You should wrangle the data/dictionary first and only then construct a DataFrame with it. It will make your life easier and is faster than trying to manipulate the data with pandas i.e. after the DataFrame is created.

import pandas as pd

data = [{
  "ticket_id": 4,
  "customer_id": 8,
  "created_at": "2022-05-01",
  "custom_fields": [
    {
      "id": 15,
      "value": "website"
    },
    {
      "id": 16,
      "value": "broken"
    },
    {
      "id": 23,
      "value": None
    },
  ],
  'group_id': 42
}]

custom_fields = data[0].pop('custom_fields')
data[0].update({rec['id']: rec['value'] for rec in custom_fields})

t_df = pd.DataFrame(data)

Output:

>>> t_df 

   ticket_id  customer_id  created_at  group_id       15      16    23
0          4            8  2022-05-01        42  website  broken  None

convert a dataframe column of list of dictionaries to multiple columns with dictionary keys as new column

You can try DataFrame()+explode()+drop_duplicates():

out=pd.DataFrame(df['col1'].explode().tolist()).drop_duplicates(subset=['id'])

output of out:

     id     values                status
0   35879   [fl/li, auto]           []
1   5056    [and]                   []
2   13431   [2]                     []
3   16102   [usb]                   []
4   4300    [13.0]                  [mp]
5   12461   [core]                  []
8   13430   [1080p (hd), 1080p]     []
9   12462   [1.3]                   [gh]
11  3555    [opt]                   []

---

Related Topics