for row in sheet.iter_rows(min_row=2, values_only=True):
data.append(row)
Group the data by year and quarter
data_by_year_and_quarter = {}
for row in data:
year = row[2]
quarter = row[0]
if year not in data_by_year_and_quarter:
data_by_year_and_quarter[year] = {}
data_by_year_and_quarter[year][quarter] = row[1]
for year in sorted(data_by_year_and_quarter.keys()):
year_data = data_by_year_and_quarter[year]
data = [year_data.get(quarter, None) for quarter in x_labels]
chart.add(str(year), data)
Here is my code
import pygal from openpyxl import load_workbook
Define a function to format the fiscal period labels
def format_fiscal_period(fiscal_period): quarter, year = fiscal_period.split('-') quarter = 'Q' + quarter[1] return f"{quarter}-{year}"
Load the Excel file
wb = load_workbook(filename='C:/Users/wajiha/Test_Jupyter/Assets.xlsx')
Select the sheet you want to read data from
sheet = wb['Data']
Get the data from the sheet
data = []
for row in sheet.iter_rows(min_row=2, values_only=True): data.append(row)
Group the data by year and quarter
data_by_year_and_quarter = {} for row in data: year = row[2] quarter = row[0] if year not in data_by_year_and_quarter: data_by_year_and_quarter[year] = {} data_by_year_and_quarter[year][quarter] = row[1]
Set the x-labels to be the years
x_labels_major = ['2012', '2013', '2014', '2015']
x_labels = ['','2012','Q1-12','Q2-12','Q3-12','Q4-12','2013','Q1-13','Q2-13','Q3-13','Q4-13','2014','Q1-14','Q2-14','Q3-14', 'Q4-14','2015','Q1-15','Q2-15','Q3-15','Q4-15']
x_labels =[format_fiscal_period(quarter)]
Create a Pygal chart
chart = pygal.Bar(x_label_rotation=90) chart.title = 'Data from Excel' chart.x_labels = x_labels chart.x_labels_major = [x_labels[1], x_labels[6], x_labels[11], x_labels[16]]
Add the data to the chart
for year in sorted(data_by_year_and_quarter.keys()): year_data = data_by_year_and_quarter[year] data = [year_data.get(quarter, None) for quarter in x_labels] chart.add(str(year), data)
Render the chart
chart.render_to_file('chart.svg')