Parse a structured dictionary of entries into a prettified Excel sheet

user_18190509 0
Viewed 85

I have this python dictionary and I want to write this data to an Excel file.

NOTE: there are many categories and each category has many cars (I used 2 categories for simplicity)

data = {"Category": {"Diesel" : [{
                                "Name": "Land Rover", 
                                "Description":"No Description Found", 
                                "Price": "£ x" }],

                     "Electric" : [{"Name": "Tesla Model X", 
                                  "Description":"No Description Found", 
                                  "Price": "£ x" }]
                    }               
        }

Desired output:

Category Name Description Price
Diesel
Land Rover No Description Found £ x
Electric
Tesla Model X No Description Found £ x
pandas dataframe excel dictionary python
1 Answers

TL;DR - the best solution is in the end.

The most straightforward way would be to go over your data and append rows one by one:

df = pd.DataFrame(columns=['Category',  'Name', 'Description',  'Price'])
i = 0
for category, cars in data['Category'].items():
  df.loc[i, 'Category'] = category
  i += 1
  for car in cars:
    df.loc[i, ['Name',  'Description',  'Price']] = car
    i += 1

In the end, your data frame should look like:

   Category           Name           Description Price
0    Diesel            NaN                   NaN   NaN
1       NaN     Land Rover  No Description Found   £ x
2  Electric            NaN                   NaN   NaN
3       NaN  Tesla Model X  No Description Found   £ x

Finally, just write this data frame to disk as an Excel file:

df.to_excel(...)

By default, NaN cells will be empty in the Excel file, which is what you have in your example.

However, you can avoid loops and counters altogether by going for a pure pandas solution:

pd.concat(
    df
    for category, cars in data['Category'].items()
    for df in (pd.DataFrame({'Category': [category]}), pd.DataFrame(cars)))
user_21536360
Related