[Python] Export Python charts excel with grid layout

I'm trying to write a Python script that generates a series of charts from Excel. The Excel file has the price of different cars on each day.

Column 1 is the Date; Column 2 is price for Green cars; Column 3 is the price for Red cars etc. The following code only outputs graphs for the first and last columns (first column being a car price and not the date column) and the two graphs are spaced apart. For example first chart is displayed in column D and the second chart in column AFD.

How can I amend my code so that it appears in a nice grid format (two graphs per row)?

import pandas as pd
import matplotlib.pyplot as plt
import xlsxwriter
import io

def generate_seasonal_charts(file_path, start_date, end_date, output_file):

df = pd.read_excel(file_path)

df['Date'] = pd.to_datetime(df['Date'])

df = df[(df['Date'] >= start_date) & (df['Date'] <= end_date)]

df.set_index('Date', inplace=True)

workbook = xlsxwriter.Workbook(output_file)
worksheet = workbook.add_worksheet()

# Define image dimensions and positions
image_width = 800
image_height = 400
x_offset = 0
y_offset = 0
y_spacing = image_height + 20

# Create charts for each column
for i, column in enumerate(df.columns[1:]):
fig, ax = plt.subplots(figsize=(10, 6))
ax.plot(df.index, df[column])
ax.set_xlabel('Date')
ax.set_ylabel('Value')
ax.set_title(f'Seasonal Data for {column}')
plt.grid(True)

# Save the plot to a buffer
buf = io.BytesIO()
fig.savefig(buf, format='png')
buf.seek(0)

# Embed the image in the Excel worksheet
if i == 0:
worksheet.insert_image(x_offset, y_offset, f"Chart {i+1}", {'image_data': buf, 'x_scale': image_width / fig.canvas.get_width_height()[0], 'y_scale': image_height / fig.canvas.get_width_height()[1]})
else:
y_offset += y_spacing
worksheet.insert_image(x_offset, y_offset, f"Chart {i+1}", {'image_data': buf, 'x_scale': image_width / fig.canvas.get_width_height()[0], 'y_scale': image_height / fig.canvas.get_width_height()[1]})

plt.close(fig)

workbook.close()

file_path = "chart testing.xlsx"
output_file = "output.xlsx"
start_date = '2018-01-01'
end_date = '2023-12-31'

generate_seasonal_charts(file_path, start_date, end_date, output_file)

Continue reading...