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)

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

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

Desired output is the following

CodePudding user response：

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

First, you need to fix your data - if you want to have multiple cars per category, you need to have a list of dicts for each one:

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

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

Then you can 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.

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

columns = ['Category',  'Name', 'Description',  'Price']
pd.concat(
    cat_df
    for category, cars in data['Category'].items()
    for cat_df in (
        [pd.DataFrame({'Category': [category]}, columns=columns),
         pd.DataFrame(cars).assign(Category=np.nan)]))

In fact, in this particular case, you can get away with just:

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