We use a building, and have a .csv files of the power used every half hour for the last three months. We wanted to produce charts of the showing usage, for every Monday, and for every week throughout the year. Creating charts in a spreadsheet,manually creating a chart, and adding data to the series, soon got very boring. It was much more interesting to automate this. This blog post describes how I used Python and xlsxWwriter to create an Excel format spread sheet – all this from Linux.
Required output
Because our building is used by different groups during the week, I wanted to have
- a chart for “Monday” for one group of users, “Tuesday” for another group of users, etc. This would allow me to see the typical profile, and make sure the calculated usage was sensible.
- A chart on a week by week basis. So a sheet and chart for each week.
- Automate this so, I just run a script to get the spread sheet and all of the graphs.
From these profiles we could see that from 0700 to 0900 every day there was a usage hump – a timer was turning on the outside lights, even though no one used the building before 1000!
Summary of the code
- Read a CSV file in to memory
- Create the workbook and add a sheet
- Create a chart template
- Create a chart for every day of the week
- Build up the first row of data labels as a header row
- Convert a string to a data time
- Write each row
- Create a sheet for each week
- Add data range to each chart
- Writing a formula
- Save, clean up and end
Reading the csv file
I used
import csv
fn = "HF.csv"
with open(fn, newline='') as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
# get the column lables
keys = row.keys()
...Create the workbook and add a sheet
This opens the specified file chart_scatter.xlsx, for output, and overwrites any previous data.
import xlsxwriter
...
workbook = xlsxwriter.Workbook('chart_scatter.xlsx')
data= workbook.add_worksheet("Data")Create a chart template
I used a Python function to create a standard chart with common configuration, so all charts had the same scale, and look and feel.
def default_chart(workbook,title):
chart1 = workbook.add_chart({'type': 'scatter'})
# Add a chart title and some axis labels.
chart1.set_title ({'name': title})
chart1.set_x_axis({
'time_axis': True,
'num_format': 'hh:mm',
'min': 0,
'max': 1.0,
'major_unit':1/12., # 2 hours
'minor_unit':1.0/24.0, # every hour
'major_gridlines': {
'visible': True,
'line': {'width': 1.25, 'dash_type': 'long_dash'},
},
'minor_tick_mark': 'inside'
})
chart1.set_y_axis({
'time_axis': True,
'min': 0,
'max': 7.0, # so they all have the same max value
'major_unit':1,
'minor_unit':0,
'minor_tick_mark': 'inside'
})
#chart1.set_y_axis({'name': 'Sample length (mm)'})
chart1.set_style(11) # I do not know what this does
chart1.set_size({'width': 1000, 'height': 700})
return chart1Create a chart for every day of the week
This creates a sheet (tab) for each day of the week, creates a chart, and attaches the chart to the sheet.
days=['Mon','Tue','Wed','Thu','Fri','Sat','Sun']
days_chart = []
for day in days:
c=default_chart(day) # create chart
days_chart.append(c) # build up list of days
# add a sheet with name of the day of the week
wb =workbook.add_worksheet(day) # create a sheet with name
wb.insert_chart('A1',c) # add chart to sheet
Build up the first row of data labels as a header row
This processes the CSV file opened above and writes each key to the first row of the table.
In my program I had some logic to change the headers from the csv column name to a more meaningful value.
fn = "HF.csv"
with open(fn, newline='') as csvfile:
reader = csv.DictReader(csvfile)
# read the header row from the csv
row = next(reader, None)
count = LC.headingRow(workbook,data,summary,row)
keys = list(row.keys())
for i,j in enumerate(keys):
# 'i' is is the position
# 'j' is the value
heading = j
# optional logic to change heading
# write data in row 0, column i
data.write_string(0,i,heading) # first row an column of the data
# add my own columns header
data.write_string(0,count+1,"Daily total")
Convert a string to a data time
d = row['Reading Date'] # 01/10/2022
dd,mm,yy = d.split('/')
dt = datetime.fromisoformat(yy+'-'+mm+'-'+dd)
weekday = dt.weekday()
# make a nice printable value
dow =days[weekday] + ' ' + dd + ' ' + mm + ' ' + yy
row['Reading Date'] = datetime.strptime(d,'%d/%m/%Y')Write each row
This takes the data items in the CSV file and writes them a cell at a time to the spread sheet row.
I have some cells which are numbers, some which are strings, and one which is a date time. I have omitted the code to convert a string to a date time value
ddmmyy = workbook.add_format({'num_format': 'dd/mm/yy'})
for row in reader:
keys = row.keys()
items = list(row.items())
for i,j in enumerate(items): # ith and (key,value)
j =j[1] # get the value
# depending on data type - used appropriate write method
if isinstance(j,datetime):
data.write_datetime(r,i, j,ddmmyy)
else:
if j[0].isdigit():
dec = Decimal(j)
data.write_number(r,i,dec)
sum = sum + dec
else:
data.write(r,i ,j) Create a sheet for each week
if (r == 1 or dt.weekday() == 6): # First record or Sunday
# create a new work sheet, and chart
temp = workbook.add_worksheet(dd + '-' +mm)
chart1 = workbook.add_chart({'type': 'scatter'})
chart1 = default_chart('Usage for week starting '+ ...)
# put chart onto the sheet
temp.insert_chart('A1', chart1) Add data range to each chart
This says create a chart with
- data name from the date value in column 3 of the row – r is row number
- use the column header from data sheet row 0, column 5; to row 0 column count -1
- use the vales from from r, column 5 to row r ,column count -1
- pick the colour depending on the day colours[] is an array of colours [“red”,”blue”..]
- picks a marker type based on week day from an array [“square”,”diamond”…]
# r is the row number in the data
chart1.add_series({
'name': ['Data',r,3],
# field name is row 0 cols 5 to ...
'categories': ['Data',0,5,0,count-1],
# data is in row r - same range 5 to ,,,
'values': ['Data',r,5,r,count-1],
# pick the colour and line width
'line': {'color': colours[weekday],"width" :1 },
# and the marker
'marker': {'type': markers[weekday]}
})Write a cell formula
You can write a formula instead of a value. You have to modify the formula for each row and column.
In a spread sheet you can create a formula, then use cut and paste to copy it to many cells. This will change the variables. If you have for cell A1, =SUM(A2:A10) then copy this to cell B2, the formula will be =SUM(B3:B11).
With xlsxWriter you have to explicitly code the formula
worksheet.write_formula('A1', '{=SUM(A2:A10)}')
worksheet.write_formula('B2', '{=SUM(B3:B11)}')Save, clean up and end
I had the potential to hide columns – but then they did not display.
I made the column widths fit the data.
# hide boring stuff
# data.set_column('A:C',None,None,{'hidden': 1})
# Make columns narrow
data.set_column('D:D', 5) # Just Column d
data.set_column('F:BA', 5) # Columns F-BA 30.
workbook.close()
exit(0)
ColinPaice 