I wanted to export two graphs (one is a column bar chart and the second is line chart). The axes and legend are placed properly but the data is not populating in the graph
Input Data Format
Date Org Vol VolCum OrgCum
2020-03-01 36.447 37.2 37.2 36.447
2020-03-02 38.742 39.6 76.8 75.189
2020-03-03 67.17 69.1 145.9 142.359
2020-03-04 39.875 40.9 186.8 182.234
2020-03-05 69.4 71.1 257.9 251.634
2020-03-06 39.488 40.3 298.2 291.122
2020-03-07 69.447 67.3 365.5 360.569
2020-03-08 78.55 81.9 447.4 439.119
It contains data of two columns and cumulative data for these columns. There are multiple excel worksheets in a similar way in the input file
My code:
import pandas as pd
import xlsxwriter
df = pd.ExcelFile('dataset.xlsx')
sheets = df.sheet_names
writer = pd.ExcelWriter('dataset_with_graphs.xlsx', engine='xlsxwriter')
for sheet in sheets:
dataset = pd.read_excel(df,sheet)
worksheet = workbook.add_worksheet()
data = [
[int(i) for i in df1['Date'].values]
[int(i) for i in df1['Org'].values],
[int(i) for i in df1['Vol'].values],
[int(i) for i in df1['VolCum'].values],
[int(i) for i in df1['OrgCum'].values]
]
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
worksheet.write_column('D2', data[3])
worksheet.write_column('E2', data[4])
chart1 = workbook.add_chart({'type': 'column'})
chart1.add_series({
'name': '= '+i+' !$B$1',
'categories': '= '+i+' !$A$2:$A$23',
'values': '= '+i+' !$B$2:$B$23',
})
chart1.add_series({
'name': '= '+i+' !$C$1',
'categories': '= '+i+' !$A$2:$A$23',
'values': '= '+i+' !$C$2:$C$23',
})
chart1.set_title ({'name': 'Results of data analysis'
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Volume'})chart1.set_style(11)
worksheet.insert_chart('G3', chart1)
workbook.close()
Expected Output
Same Data and create a bar chart for Org and Vol. Create a line chart for VolCum and OrgCum for all the sheets present in the excel file