Python Analytics & My electricity Prices- Part 2
In Python Analytics & My electricity Prices- Part 1 after being able to answer the question “IS MY NEXT BILL GOING TO BE GREATER OR LESS THAN THE CURRENT ONE?” there were some other feature that I wanted to use Python to analyse.
Changing Daytime Bucket sizes to match the Bill Daytime buckets
In the previous python script I ended up with 6 time buckets:
labels = [‘night’, ‘morning’, ‘avo’, ‘evening’, ‘late night’, ‘night1’]
Whereas the bill only has 5, [ ‘morning’, ‘avo’, ‘evening’, ‘late night’, ‘night’]
The column that defines these is used to group the data so that the mean of the values in those groups can be calculate.
As I was working in discrete days the buckets were defined as follows:
buckets = [0,7, 12, 17, 21 ,23,24]
each with discrete labels:
labels = [‘night’, ‘morning’, ‘avo’, ‘evening’, ‘late night’, ‘night1’]
As the labels have to be discrete, I cannot have 2 the same name, eg ‘night’. So I named the last period ‘night1’ to ensure discrete labels.
I realised that this column could just have the values in the dataframe renamed using :
df1[‘Time Bin’] = df1[‘Time Bin’].str.replace(‘night1’, ‘night’)
So wherever there was a ‘night1’ it was relabeled as ‘night’. So when the groupby was used then all the night ones are grouped together, so a nice easy solution to that issue and the python code now aligns with the bill day periods and the mean values reflect the bill. I rewrote the code to allow for this.
Is there a common time in the night when spot prices are cheaper?
My daily Hot Water requirement usually requires a minimum of 1 hr heating of the Hot Water Cylinder (HWC). So is there a a time through the night when prices are consistently lower?
To answer this question, I took the same monthly dataset that i’d used for the previous exercise, so that will give me 28 separate days with 16 spot prices from 11pm through to 7am at half hour intervals.
This is a reasonable spread and I hoped to be able to see some trends. This may change for different seasons so I will start with a small sample to see if there is any obvious pattern.
I used the same method as the previous script to start with to isolate only the Night Time period in the days.
So I used the BIN method and then filtered out the Day Time periods.
I only need 3 bins, from midnight to 7am, then from 7am to 11pm and then from 11pm to Midnight.
# read contents of csv file
df = pd.read_csv(path2Files + file1W, index_col=False) #, skiprows=11)
# # convert strings to datetimes
df['Period start']= pd.to_datetime(df['Period start'], dayfirst=True)
# create bins based on hour periods
bins = [0, 7,23,24]
# add custom labels
labels = ['night', 'morning', 'night1']
# add the bins to the dataframe
df['Time Bin'] = pd.cut(df['Period start'].dt.hour, bins, labels=labels, right=False)
df['Time Bin'] = df['Time Bin'].str.replace('night1', 'night')
# this drops all rows with morning in , so only night time data
df = df[~df['Time Bin'].isin(['morning'])] After getting the 3 bins I can then rename the last bin (Night1) to Night so there are then only 2 bins and then filter out all the Morning hours , so only the night time period rows are left in the Dataframe.
After dropping columns not required, I can then use the Groupby method to get the minimum and maximum spot price values each day and the timestamp of when they occur:
# drop columns not required
df = df.drop(['Unnamed: 0','Time Bin','Region ID','Region','Period end'], axis=1)
# df.to_csv(path2Files + file2W)
df['day'] = df['Period start']
df = df.set_index('day')
df1 = df.groupby(df.index.day)['Price ($/MWh)'].agg(['idxmin','min', 'idxmax','max'])I then wrote this to a file and used Excel to do a simple comparison, to find the frequency of times a specific period comes up. I’ve used the formula on both the Minimum $/kWh rate during the night period per day and the Maximum $/kWh rate.
For min $/kWh of the 28 days, 7 are at 3.30am to 4am, 4 at 3am, 4 at 4.30am
For max $/kWh of the 28 days, 6 are at 3.00am to 3.30am, 5 at midnight, 4 at 6.30am
So there is no clear time that it is best to turn the HWC on to get the cheapest rate during the night period, based on the dataset used. There is a trend, but a bigger dataset would need to be used to see if there are times in the night where the $/kWh rate is consistently lower.
When doing a basic graph with all of the nighttime points graphed there does not seem to be any clear trend:
As I’ve written this script, I will park it for the moment but may revisit with a larger dataset and see if some trend may show.
Other fixed & variable charges – part 1
On top of actual kWh usage there are other charges. Distributor, Administration, Metering charges and the EA levy. How are these calculated?
When looking at my recordings of the bills I note on one of them that my power usage charge was $0.01 although I used 53.17kWh of power in that period. See highlighted line on image below. There were still reasonable charges for Distributor, Administration, Metering charges and the EA levy components. So how are they calculated?
I had 3 thoughts on this, first, there was a minimum charge, regardless, for part of the charge, and a portion of the charge was a multiplier of the Purchase Cost sum or kWh used. The 2nd thought was that it was a direct percentage of the kWh used. The 3rd thought was that there may have been part fixed and then a bounded part for a different charge depending on steps of Purchase Cost sum or kWh used, eg between 50-100kWh one percentage, for 100-150kWh a different percentage, maybe on a sliding scale, the more you use the less percentage markup.
When I tried the first method of calculation, using the $0.01 purchase cost and taking that as the base, so any extra would be somehow tied to the extra purchase cost
In the first 2 suggested methods above there seems to be large variations if you do the calculations directly, as shown in the tables above. So I think the 3rd method may be the one used.
I have emailed the supplier Paua and asked how these charges are calculated and will wait to see what the reply is. Searching on the Interwongle hasn’t come up with any practical results so far.
Other fixed & variable charges – part 2
I received a very prompt reply from m Power supplier on this topic, as follows:
- The Generation charges are per kWh for each POC. Your POC is CPK0331.
- Distribution Charges (which also include Transmission Charges) for Inclusive Meters like yours are currently $0.30 per Day and per kWh rates of Peak (weekdays 7-11am and 5-9pm) $.1075 and OffPeak of $0.0575
- Administration Charges are $0.03 per kWh
- Metering Charges are $0.15 per day and .005 per kWh
- EA Levy. We currently charge $0.001 per kWh to recover the EA Levy.
So I’ll revisit the costs for those. I must admit, it hadn’t occurred to me that there would be a daily charge, but it makes sense.
The video below is a good general explainer of power bill in NZ:
WITS dashboard
I came across the WITS Dashboard when looking for the API for prices, and have been fossiking around the site. There is more data there to download too.
Overview of Wholesale Electricity Market video
End Comment
No real gains in this blog but some things tested. C’est la vie.
Now I have the Other charges structure I’ll revisit that part of the project.
The next area to look is at the longer predictions to see if I can anticipate power usage more than a week out (and is still retroactive as I’ve used that power so cannot modify my behaviour if there are high demand times. If there is a peak in the historic data this may be a warning for me to monitor and possibly use it as an indication that it may occur again and react to it.
