How to format and write excel using pandas

Question

Lets assume that we have nested python dictionaries which should be written in single excel sheet file.

Following are sample dictionaries which can be used.

Car = [
    {
        "carbmodel": "Model A",
        "attributes": {
            "make": "Brand X",
            "year": 2023,
            "color": "Red",
            "engine": "V6",
            "transmission": "Automatic",
            "features": ["GPS", "Bluetooth", "Leather Seats"]
        }
    },
    {
        "carbmodel": "Model B",
        "attributes": {
            "make": "Brand Y",
            "year": 2022,
            "color": "Blue",
            "engine": "Inline-4",
            "transmission": "Manual",
            "features": ["Sunroof", "Backup Camera", "Alloy Wheels"]
        }
    },
    {
        "carbmodel": "Model C",
        "attributes": {
            "make": "Brand Z",
            "year": 2023,
            "color": "Black",
            "engine": "V8",
            "transmission": "Automatic",
            "features": ["Apple CarPlay", "Heated Seats", "Navigation"]
        }
    }
]

bikes = [
    {
        "carbmodel": "Bike X",
        "attributes": {
            "make": "Brand P",
            "year": 2023,
            "color": "Green",
            "engine": "250cc",
            "transmission": "Manual",
            "features": ["ABS", "LED Lights", "Digital Display"]
        }
    },
    {
        "carbmodel": "Bike Y",
        "attributes": {
            "make": "Brand Q",
            "year": 2022,
            "color": "Orange",
            "engine": "200cc",
            "transmission": "Manual",
            "features": ["Disc Brakes", "Tubeless Tires", "Kick Start"]
        }
    },
    {
        "carbmodel": "Bike Z",
        "attributes": {
            "make": "Brand R",
            "year": 2023,
            "color": "Black",
            "engine": "300cc",
            "transmission": "Manual",
            "features": ["Fuel Injection", "Mono Suspension", "Dual Exhaust"]
        }
    }
]

This should be written in excel file using pandas dataframe in following format which is shown in screenshot.

Really appreciate any help, thank you in advance.

This is used code block for testing the scenario.

import xlsxwriter

# Create a new Excel file
workbook = xlsxwriter.Workbook('merged_cells_and_data.xlsx')
worksheet = workbook.add_worksheet()

# Add some content to the merged cells
cell_format = workbook.add_format({
    'bold': True,
    'align': 'center',
    'valign': 'vcenter',
    'bg_color': 'yellow'
})

worksheet.merge_range('A1:E1', 'Merged Cells Example', cell_format)

# Nested dictionary
useful_info = {
    'personal_info': {
        'name': 'John Doe',
        'age': 30,
        'location': 'New York, USA'
    },
    'contact_info': {
        'email': 'johndoe@example.com',
        'phone': '123-456-7890'
    },
    'employment': {
        'company': 'ABC Corporation',
        'position': 'Software Engineer',
        'years_experience': 5
    },
    'skills': ['Python', 'JavaScript', 'SQL', 'Problem Solving'],
    'education': {
        'university': 'University of XYZ',
        'degree': 'Bachelor of Science in Computer Science',
        'graduation_year': 2018
    }
}

# Write nested dictionary starting from the 2nd row
row = 1
for key, value in useful_info.items():
    worksheet.write(row, 0, key)
    if isinstance(value, dict):
        for inner_key, inner_value in value.items():
            worksheet.write(row, 1, inner_key)
            worksheet.write(row, 2, inner_value)
            row += 1
    else:
        worksheet.write(row, 1, value)
    row += 1

# Close the workbook
workbook.close()

print("Excel file with merged cells and nested dictionary data created successfully.")

#Merge Cell issue with Multiple pandas #dataframe created from nested dictionaries 
mport xlsxwriter

Nested json added to dataframe

f1 = pd.DataFrame(sample_1).T 12= pd.DataFrame(sample_2).T

rint(df1.shape[0])

rint(df1.shape[1])

ef multiple_dfs(df_list, sheets, file_name, spaces): writer = pd.ExcelWriter(file_name, engine='xlsxwriter') # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook("merge1.xlsx") worksheet = workbook.add_worksheet("Validation")

# Increase the cell size of the merged cells to highlight the formatting.

worksheet.set_column("A:E", 12)

worksheet.set_row(3, 30)

worksheet.set_row(6, 30)

worksheet.set_row(7, 30)
# Create a format to use in the merged range. merge_format = workbook.add_format(

"bold": 1,

"border": 1,

"align":"center",

"valign": "vcenter",

"fg_color": "yellow".

}

# Merge 3 cells.

worksheet.merge_range("A1:E1", "Merged Range", merge_format) # Create a format to use in the merged range.

workbook.close()

with pd.ExcelWriter(file_name) as writer:

  row = 3

  for dataframe in df_list:

    dataframe.to_excel (writer,sheet_name=sheets, startrow row, startcol=0)

   row row + len(dataframe.index) + spaces + 1

    #writer.close()

    #workbook.close()

dfs = [df1, df2]

## run function

multiple_dfs(dfs, 'Validation', 'merge1.xlsx', 2)

Answer 1

The only missing part of your test code is converting list to string before writing to Excel.

# Write nested dictionary starting from the 2nd row
row = 1
for key, value in useful_info.items():
    worksheet.write(row, 0, key)
    if isinstance(value, dict):
        for inner_key, inner_value in value.items():
            worksheet.write(row, 1, inner_key)
            worksheet.write(row, 2, inner_value)
            row += 1
    else:
        if isinstance(value, list):
            value = ', '.join(value)
        worksheet.write(row, 1, value)
    row += 1

Writing data to Excel with Pandas is similar to using XlsxWriter. Here is a sample snippet:

import pandas as pd
df = pd.DataFrame({'Data': [1, 2, 3],
                   'Total': [4, 5, 6]}) 
writer = pd.ExcelWriter(r'd:/temp/output.xlsx', engine='xlsxwriter') 
# Convert index to column if needed
df.reset_index().to_excel(writer, sheet_name='Sheet1', index=False)  
# Access the XlsxWriter workbook and sheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add formatting - merge, font, border
style = {'bold': True, 'font_color': 'blue', 'font_size': 18, 'font_name': 'Arial' }
worksheet.merge_range('A1:B1', 'Report', workbook.add_format(style))
writer.close()