Nathan Grigg

Some electric analysis

I bought an electric car last month, which got me interested in my electric bill. I was surprised to find out that my electric company lets you export an hour-by-hour usage report for up to 13 months.

There are two choices of format: CSV and XML. I deal with a lot of CSV files at work, so I started there. The CSV file was workable, but not great. Here is a snippet:

"Data for period starting: 2022-01-01 00:00:00  for 24 hours"
"2022-01-01 00:00:00 to 2022-01-01 01:00:00","0.490",""
"2022-01-01 01:00:00 to 2022-01-01 02:00:00","0.700",""

The “Data for period” header was repeated at the beginning of every day. (March 13, which only had 23 hours due to Daylight Saving Time adjustments, also said “for 24 hours”.) There were some blank lines. It wouldn’t have been hard to delete the lines that didn’t correspond to an hourly meter reading, especially with BBEdit, Vim, or a spreadsheet program. But I was hoping to write something reusable in Python, preferably without regular expressions, so I decided it might be easier to take a crack at the XML.

Here is the general structure of the XML:

<entry>
  <content>
    <IntervalBlock>
      <IntervalReading>
        <timePeriod>
          <duration>3600</duration>
          <start>1641024000</start>
        </timePeriod>
        <value>700</value>
      </InvervalReading>
    </IntervalBlock>
  </content>
</entry>

Just like the CSV, there is an entry for each day, called an IntervalBlock. It has some metadata about the day that I’ve left out because it isn’t important. What I care about is the IntervalReading which has a start time, a duration, and a value. The start time is the unix timestamp of the beginning of the period, and the value is Watt-hours. Since each time period is an hour, you can also interpret the value as the average power draw in Watts over that period.

XML is not something I deal with a lot day to day, so I had to read some Python docs, but it turned out very easy to parse:

from xml.etree import ElementTree
from datetime import datetime
import pandas as pd
import matplotlib.pyplot as plt

ns = {'atom': 'http://www.w3.org/2005/Atom', 'espi': 'http://naesb.org/espi'}
tree = ElementTree.parse('/Users/nathan/Downloads/SCE_Usage_8000647337_01-01-22_to_12-10-22.xml')
root = tree.getroot()
times = [datetime.fromtimestamp(int(x.text))
         for x in root.findall("./atom:entry/atom:content/espi:IntervalBlock/espi:IntervalReading/espi:timePeriod/espi:start", ns)]
values = [float(x.text)
          for x in root.findall("./atom:entry/atom:content/espi:IntervalBlock/espi:IntervalReading/espi:value", ns)]
ts = pd.Series(values, index=times)

The ns dictionary allows me to give aliases to the XML namespaces to save typing. The two findall commands extract all of the start tags and all of the value tags. I turn the timestamps into datetimes and the values into floats. Then a make them into a Pandas Series (which, since it has a datetime index, is in fact a time series).

My electricity is cheaper outside of 4-9 p.m., so night time is the most convenient time to charge. I made a quick visualization of the last year by restricting myself from midnight to 4:00 a.m. andtaking the average of each day. Then I plotted it without lines and with dots as markers:

plt.plot(ts[ts.index.hour<4].groupby(lambda x: x.date).mean(), ls='None', marker='.')

As expected, you see moderate use in the winter from the heating (gas, but with an electric blower). Then a lull for the in-between times, a peak in the summer where there is sometimes a bit of AC running in the night, another lull as summer ends, and then a bit of an explosion when I started charging the car.

For now, I am actually using a 120 V plug which can only draw 1 to 1.5 kW and is a slow way to charge a car. Eventually I will get a 240 V circuit and charger, increase the charging speed 5x, and have even bigger spikes to draw.


Amazon accounts, Kindle, and families

I’m back from a blogging hiatus for a quick complaint about the sorry state of Amazon’s account system, especially when it comes to households and minors.

Everything that follows is to the best of my knowledge, and only includes the features I actually use.

A regular Amazon account can be used for shopping, Kindle, and Prime Video (among other things). You can have a maximum of two regular Amazon accounts in a household, and they can share Prime shipping benefits and Kindle purchases, but not Prime Video. However, under the primary member’s Prime Video login, you can have sub-profiles to separate household members.

On a Kindle device, you can share ebook purchases with minors using Amazon Kids. This is not a true Amazon or Kindle account, but a sub-account within a regular Amazon account. That is, you sign into the Kindle with the parent’s account and then enter Kid Mode. All purchases (or library check-outs) must be made on the parent’s account and then copied over to the child’s library using a special Amazon dashboard.

Note that Amazon Kids+ is a different product: it is basically Kindle Unlimited for Amazon Kids accounts. I have used it and I think the selection is terrible. For example, they love to carry the first book of a series but not the remainder of the series. Also, when I last used it, there was no way to know which books are available through Amazon Kids+ short of searching for the book on a kid’s device.

There is a shopping feature called Amazon Teen. This is essentially a regular Amazon account, but it is linked to a parent’s account, and purchases are charged to the parent’s card, with the option to require purchase-by-purchase approval from the parent. This is a way to share Prime shipping features with a teenager, and the only way to share Prime shipping with more than a single person in your househould. Crucially, Amazon Teen accounts cannot purchase Kindle books, log into a Kindle device, or share Kindle purchases with the parent’s account.

Until now, I have mostly survived in the Amazon Kids world, despite the friction involved in getting a book onto a kid’s device. My kids have mostly adapted by ignoring their Kindles and reading books in Libby on their phones. This isn’t a good fit for my teen and tween, who need to read books at school. They are not allowed to use phones at school, but are allowed to use e-ink Kindles.

Everything came to a head this weekend, when I tried to make them both Amazon Teen accounts, which are useful in their own right. (The current practice is that they text me an Amazon link when they need something, and it will be nice for them to be a little be more self-sufficient.) This was before I knew that Amazon Teen accounts couldn’t buy Kindle books (why?), so I then attempted to create them each a second account, not linked to mine in any way, for Kindle purposes.

That is when things came to a screeching halt, but this is at least partially my fault. While I had been looking into this, I was downloading Kindle books to my computer using a Keyboard Maestro script that simulated the five clicks required for each download. I’m pretty sure that this triggered some robot-defensive behavior from Amazon, which made it impossible for me to create an account without a cell phone attached to the account. But all of our household phone numbers are already attached to other accounts, and attempting to remove them put me into an infinite loop of asking for passwords and asking for OTPs.

I eventually solved this problem in two different ways. One involved talking to a human at Amazon’s tech support, which I admit is better than many of the other tech companies at solving this kind of problem. The other involved a VPN, which seems to have freed me from bot-suspicion.

But in the end, I also put in an order for a Kobo. I’m told they can sync directly with Libby for library checkouts, unlike Amazon which requires a complex multi-click dance which might prevent my kids from using their Kindles even if I do get their accounts squared away. And these are the last major micro-USB devices in the house, so maybe the time has come to move on. Ironically, the only way I could find a Kobo that shipped in less than a week was to buy it from Amazon.


Fastmail JMAP backup

(updated

[Update: Since I first wrote this, Fastmail switched from using HTTP BasicAuth to Bearer Authorization. I have updated the script to match.]

I use Fastmail for my personal email, and I like to keep a backup of my email on my personal computer. Why make a backup? When I am done reading or replying to an email, I make a split-second decision on whether to delete or archive it on Fastmail’s server. If it turns out I deleted something that I need later, I can always look in my backup. The backup also predates my use of Fastmail and serves as a service-independent store of my email.

My old method of backing up the email was to forward all my email to a Gmail account, then use POP to download the email with a hacked-together script. This had the added benefit that the Gmail account also served as a searchable backup.

Unfortunately the Gmail account ran out of storage and the POP script kept hanging for some reason, which together motivated me to get away from this convoluted backup strategy.

The replacement script uses JMAP to connect directly to Fastmail and download all messages. It is intended to run periodically, and what it does is pick an end time 24 hours in the past, download all email older than that, and then record the end time. The next time it runs, it searches for mail between the previous end time and a new end time, which is again 24 hours in the past.

Why pick a time in the past? Well, I’m not confident that if you search up until this exact moment, you are guaranteed to get every message. A message could come in, then two seconds later you send a query, but it hits a server that doesn’t know about your message yet. I’m sure an hour is more than enough leeway, but since this is a backup, we might as well make it a 24-hour delay.

Note that I am querying all mail, regardless of which mailbox it is in, so even if I have put a message in the trash, my backup script will find it and download it.

JMAP is a modern JSON-based replacement for IMAP and much easier to use, such that the entire script is 140 lines, even with my not-exactly-terse use of Python.

Here is the script, with some notes below.

  1 import argparse
  2 import collections
  3 import datetime
  4 import os
  5 import requests
  6 import string
  7 import sys
  8 import yaml
  9 
 10 Session = collections.namedtuple('Session', 'headers account_id api_url download_template')
 11 
 12 
 13 def get_session(token):
 14     headers = {'Authorization': 'Bearer ' + token}
 15     r = requests.get('https://api.fastmail.com/.well-known/jmap', headers=headers)
 16     [account_id] = list(r.json()['accounts'])
 17     api_url = r.json()['apiUrl']
 18     download_template = r.json()['downloadUrl']
 19     return Session(headers, account_id, api_url, download_template)
 20 
 21 
 22 Email = collections.namedtuple('Email', 'id blob_id date subject')
 23 
 24 
 25 def query(session, start, end):
 26     json_request = {
 27         'using': ['urn:ietf:params:jmap:core', 'urn:ietf:params:jmap:mail'],
 28         'methodCalls': [
 29             [
 30                 'Email/query',
 31                 {
 32                     'accountId': session.account_id,
 33                     'sort': [{'property': 'receivedAt', 'isAscending': False}],
 34                     'filter': {
 35                         'after': start.isoformat() + 'Z',
 36                         'before': end.isoformat() + 'Z',
 37                     },
 38                     'limit': 50,
 39                 },
 40                 '0',
 41             ],
 42             [
 43                 'Email/get',
 44                 {
 45                     'accountId': session.account_id,
 46                     '#ids': {
 47                         'name': 'Email/query',
 48                         'path': '/ids/*',
 49                         'resultOf': '0',
 50                     },
 51                     'properties': ['blobId', 'receivedAt', 'subject'],
 52                 },
 53                 '1',
 54             ],
 55         ],
 56     }
 57 
 58     while True:
 59         full_response = requests.post(
 60             session.api_url, json=json_request, headers=session.headers
 61         ).json()
 62 
 63         if any(x[0].lower() == 'error' for x in full_response['methodResponses']):
 64             sys.exit(f'Error received from server: {full_response!r}')
 65 
 66         response = [x[1] for x in full_response['methodResponses']]
 67 
 68         if not response[0]['ids']:
 69             return
 70 
 71         for item in response[1]['list']:
 72             date = datetime.datetime.fromisoformat(item['receivedAt'].rstrip('Z'))
 73             yield Email(item['id'], item['blobId'], date, item['subject'])
 74 
 75         # Set anchor to get the next set of emails.
 76         query_request = json_request['methodCalls'][0][1]
 77         query_request['anchor'] = response[0]['ids'][-1]
 78         query_request['anchorOffset'] = 1
 79 
 80 
 81 def email_filename(email):
 82     subject = (
 83             email.subject.translate(str.maketrans('', '', string.punctuation))[:50]
 84             if email.subject else '')
 85     date = email.date.strftime('%Y%m%d_%H%M%S')
 86     return f'{date}_{email.id}_{subject.strip()}.eml'
 87 
 88 
 89 def download_email(session, email, folder):
 90     r = requests.get(
 91         session.download_template.format(
 92             accountId=session.account_id,
 93             blobId=email.blob_id,
 94             name='email',
 95             type='application/octet-stream',
 96         ),
 97         headers=session.headers,
 98     )
 99 
100     with open(os.path.join(folder, email_filename(email)), 'wb') as fh:
101         fh.write(r.content)
102 
103 
104 if __name__ == '__main__':
105     # Parse args.
106     parser = argparse.ArgumentParser(description='Backup jmap mail')
107     parser.add_argument('--config', help='Path to config file', nargs=1)
108     args = parser.parse_args()
109 
110     # Read config.
111     with open(args.config[0], 'r') as fh:
112         config = yaml.safe_load(fh)
113 
114     # Compute window.
115     session = get_session(config['token'])
116     delay_hours = config.get('delay_hours', 24)
117 
118     end_window = datetime.datetime.utcnow().replace(microsecond=0) - datetime.timedelta(
119         hours=delay_hours
120     )
121 
122     # On first run, 'last_end_time' wont exist; download the most recent week.
123     start_window = config.get('last_end_time', end_window - datetime.timedelta(weeks=1))
124 
125     folder = config['folder']
126 
127     # Do backup.
128     num_results = 0
129     for email in query(session, start_window, end_window):
130         # We want our search window to be exclusive of the right endpoint.
131         # It should be this way in the server, according to the spec, but
132         # Fastmail's query implementation is inclusive of both endpoints.
133         if email.date == end_window:
134             continue
135         download_email(session, email, folder)
136         num_results += 1
137     print(f'Archived {num_results} emails')
138 
139     # Write config
140     config['last_end_time'] = end_window
141     with open(args.config[0], 'w') as fh:
142         yaml.dump(config, fh)

The get_session function is run once at the beginning of the script, and fetches some important data from the server including the account ID and a URLs to use.

The query function does the bulk of the work, sending a single JSON request multiple times to page through the search results. It is actually a two-part request, first Email/query, which returns a list of ids, and then Email/get, which gets some email metadata for each result. I wrote this as a generator to make the main part of my script simpler. The paging is performed by capturing the ID of the final result of one query, and asking the next query to start at that position plus one (lines 77-78). We are done when the query returns no results (line 69).

The download_email function uses the blob ID to fetch the entire email and saves it to disk. This doesn’t really need to be its own function, but it will help if I later decide to use multiple threads to do the downloading.

Finally, the main part of the script reads configuration from a YAML file, including the last end time. It loops through the results of query, calling download_email on each result. Finally, it writes the configuration data back out to the YAML file, including the updated last_end_time.

To run this, you will need to first populate a config file with the destination folder and your API token, like this:

token: ffmu-xxxxx-your-token-here
folder: /path/to/destination/folder

You will also need to install the ‘requests’ and ‘pyyaml’ packages using python -m pip install requests pyyaml. Copy the above script onto your computer and run it using python script.py --config=config_file. Note that everything here uses Python 3, so you may have to replace ‘python’ with ‘python3’ in these commands.


Reading feeds in a world of newsletters

I understand the popularity of email newsletters, especially for publishers. It’s a simple way to get paid content out, easier for users than a private RSS feed. But that doesn’t mean I want to read newsletters in my email app.

Feedbin, which I am already using for my regular RSS subscriptions, bridges the gap. As part of my Feedbin account, I get a secret email address, and anything sent to that address ends up in my RSS reader. Problem solved!

But it quickly gets annoying to sign up for newsletters (often creating an account) with an email address that is neither memorable nor truly mine. Fastmail, which I am already using for my regular email, makes it easy to find specified emails sent to my regular address, forward them to my feedbin address, and put the original in the trash.

In fact, Fastmail lets me use “from a member of a given contact group” as the trigger for this automatic rule, which makes the setup for a new newsletter very simple:

  1. Subscribe to the newsletter
  2. Add the sender to my Fastmail address book
  3. Add the newly created contact to my “Feedbin” group

This is very convenient, for newsletters as well as other mail that is more of a notification than an email. Here are some of the emails that I now read as though they were feeds: