Rolandt

SPARQL has a lot of similarities to SQL and also is very different. Here I am going to get the English works of Rabindranath Tagore from Wikidata. I will use this as an exercise to learn SPARQL. As you will see, I have not tried to explain the terms here as I expect some knowledge of SQL and Semantic Triples. Since I go step by step, I think it's easy to understand.

This blog post is in continuation to my other blog posts about Learning about Semantic Web and Triple, Trying WikiData.

Let start with getting all the works from Wikidata. You can run the queries on Wikidata's query interface here. Paste the query and press play button to execute.

SELECT and LIMIT

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?author
WHERE {
   ?work wdt:P50 ?author .
}
LIMIT 10

Here ?work ?author are variables. Here are we are trying to get any ?work that has an ?author. So the statement

?work wdt:P50 ?author

is like any other semantic triple.

subject a predicate and an object 

But make sure it ends with a period aka .

wdt:P50 denotes author property. And we are LIMITing the result set to 10. Since we are just exploring.

FILTER

So our query lists anything that has author property. But now what if we want to get just the works of Rabindranath Tagore. We can do that by comparing the author property to Rabindranath Tagore. WikidataId of is Rabindranath Tagore - Q7241

We can add a FILER to filter out

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?author
WHERE {
    ?work wdt:P50 ?author .
    FILTER ( ?author = wd:Q7241 )
}

VALUES

Another way is to use VALUEs keyword. It assigns a value to a variable so it beccomes easy to substitute. So the query gets simpler

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?work ?author
WHERE {
  ?work wdt:P50 ?author .
  VALUES ( ?author ) {
    ( wd:Q7241 )
  } 
  
}

Now let's go one step further and get only English works. That's done based on the attribute of the work, Language. The attribute is wdt:P407. And the Wikidata entity for English language is wd:Q1860. So we can filter it.

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?author ?pubLang
WHERE {
  VALUES ( ?author ?pubLang) {
    ( wd:Q7241 wd:Q1860)
  }
  ?work wdt:P50 ?author .
  ?work wdt:P407 ?pubLang .  
}

Let's get the title of the work.

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?title ?pubLang ?pubDate
WHERE {
  VALUES ( ?author ?pubLang ) {
    ( wd:Q7241 wd:Q1860  )
  }  

  ?work wdt:P50 ?author .
  ?work wdt:P1476 ?title .
  ?work wdt:P407 ?pubLang .
  ?work wdt::P577 ?pubDate .
}

OPTIONAL

Now let's get published date wdt:P577. But lets make it optional. Like an outer join. Include even if the property does't exist or have a value.

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?title ?pubLang ?pubDate
WHERE {
  VALUES ( ?author ?pubLang ) {
    ( wd:Q7241 wd:Q1860  )
  }  
  
  ?work wdt:P50 ?author .
  ?work wdt:P1476 ?title .
  ?work wdt:P407 ?pubLang .
  OPTIONAL { ?work wdt:P577 ?pubDate } . 
}

Similarly get the label of the language. In wikidata the labels can exist in many languages. So we are going to filter it only for English language labels

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?title ?pubLang ?pubDate ?langauge
WHERE {
  VALUES ( ?author ?pubLang ) {
    ( wd:Q7241 wd:Q1860  )
  }  
  
  ?work wdt:P50 ?author .
  ?work wdt:P1476 ?title .
  ?work wdt:P407 ?pubLang .
  OPTIONAL { ?pubLang rdfs:label ?langauge } .
  OPTIONAL { ?work wdt:P577 ?pubDate } . 
  FILTER(LANG(?langauge) = 'en')
}

ORDER BY

And then sort by ?pubDate in descending order.

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX schema: <http://schema.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?work ?title ?pubLang ?pubDate ?langauge
WHERE {
  VALUES ( ?author ?pubLang ) {
    ( wd:Q7241 wd:Q1860  )
  }  
  
  ?work wdt:P50 ?author .
  ?work wdt:P1476 ?title .
  ?work wdt:P407 ?pubLang .
  OPTIONAL { ?pubLang rdfs:label ?langauge } .
  OPTIONAL { ?work wdt:P577 ?pubDate } . 
  FILTER(LANG(?langauge) = 'en')
}

ORDER BY DESC(?pubDate)

So when we run that query on Wikidata. We get a table and here is how it looks. (I have actually embedded the results iframe from Wikidata.

I will try and include other advanced features in the next part. What do you think? Was this was useful?

You can subscribe to my blog using RSS Feed. But if you are the person who loves getting email, then you can join my readers by signing up.

Vaccines

Novavax is having such trouble manufacturing that the US has paused funding.

This study looked at mixing and matching AZ and Pfizer. It found that side effects were similar for all the combinations. It found that AZ first then Pfizer was about the same as Pfizer+Pfizer in terms of antibodies; it found that AZ+Pfizer or Pfizer+Pfizer gave more antibodies than Pfizer first then AZ or AZ+AZ.

When it came to T-cells, AZ+AZ was still the worst, with Pfizer+Pfizer and AZ+Pfizer about the same and Pfizer+AZ the best.

This still is not a true efficacy result, just test tube results.

This article found that, among people who had recovered from COVID-19, those who were unvaccinated were 2.34 times more likely to get reinfected than those who were vaccinated.

Variants

This tweet thread says that J&J is 67% effective against hospitalization with Beta, and 71% effective against hospitalization with Delta.

Recommended Reading

This article is long and exhaustive, but is an interesting look at global vaccine supply chains.

Using gifsicle

Tip via Mark Norman Francis on Twitter:

Saw your GIF size TIL, and gifsicle -O3 --colors 48 --lossy gets it down to 320k. You can tweak the number of colours and loss to get it smaller but that’s when it starts to look worse to my eyes

I installed it using brew install gifsicle and ran it like this:

/tmp % ls -lah datasette-launch.gif 
-rw-r--r--@ 1 simon  wheel   3.7M Sep 13 12:52 datasette-launch.gif
/tmp % gifsicle -O3 --colors 48 --lossy -o datasette-launch-smaller.gif datasette-launch.gif 
/tmp % ls -lah datasette-launch*                                                            
-rw-r--r--  1 simon  wheel   613K Sep 13 12:54 datasette-launch-smaller.gif
-rw-r--r--@ 1 simon  wheel   3.7M Sep 13 12:52 datasette-launch.gif

Original: 3.7MB file:

Compressed 613KB file:

The reduced colours there were a bit too much for me, especially for the purple gradient buttons at the end. So I tried this instead:

gifsicle -O3 --colors 128 --lossy -o datasette-launch-smaller-2.gif datasette-launch.gif

Which gave me a 723KB file which I think looks good enough for my purposes:

Using ImageMagick mogrify

Found this tip on Stack Overflow: to reduce the site of an animated GIF, you can use the mogrify tool like this:

mogrify -layers 'optimize' -fuzz 7%  sqlite-convert-demo.gif

This saves over the original, so make a copy of it first.

I ran this against this 1.3MB animated GIF:

The result was this 401KB GIF:

The -fuzz 7% option is documented here - it treats similar colours as the same colour:

The distance can be in absolute intensity units or, by appending % as a percentage of the maximum possible intensity (255, 65535, or 4294967295).

Earlier this week I released sqlite-utils 3.14 with a powerful new command-line tool: sqlite-utils convert, which applies a conversion function to data stored in a SQLite column.

Anyone who works with data will tell you that 90% of the work is cleaning it up. Running command-line conversions against data in a SQLite file turns out to be a really productive way to do that.

Transforming a column

Here's a simple example. Say someone gave you data with numbers that are formatted with commas - like 3,044,502 - in a count column in a states table.

You can strip those commas out like so:

sqlite-utils convert states.db states count \
    'value.replace(",", "")'

The convert command takes four arguments: the database file, the name of the table, the name of the column and a string containing a fragment of Python code that defines the conversion to be applied.

The conversion function can be anything you can express with Python. If you want to import extra modules you can do so using --import module - here's an example that wraps text using the textwrap module from the Python standard library:

sqlite-utils convert content.db articles content \
    '"\n".join(textwrap.wrap(value, 100))' \
    --import=textwrap

You can consider this analogous to using Array.map() in JavaScript, or applying a transformation in a Python list comprehension in Python.

Custom functions in SQLite

Under the hood, the tool takes advantage of a powerful SQLite feature: the ability to register custom functions written in Python (or other languages) and call them from SQL.

The text wrapping example above works by executing the following SQL:

update articles set content = convert_value(content)

convert_value(value) is a custom SQL function, compiled as Python code and then made available to the database connection.

The equivalent code using just the Python standard library would look like this:

import sqlite3
import textwrap

def convert_value(value):
    return "\n".join(textwrap.wrap(value, 100))

conn = sqlite3.connect("content.db")
conn.create_function("convert_value", 1, convert_value)
conn.execute("update articles set content = convert_value(content)")

sqlite-utils convert works by compiling the code argument to a Python function, registering it with the connection and executing the above SQL query.

Splitting columns into multiple other columns

Sometimes when I'm working with a table I find myself wanting to split a column into multiple other columns.

A classic example is locations - if a location column contains latitude,longitude values I'll often want to split that into separate latitude and longitude columns, so I can visualize the data with datasette-cluster-map.

The --multi option lets you do that using sqlite-utils convert:

sqlite-utils convert data.db places location '
latitude, longitude = value.split(",")
return {
    "latitude": float(latitude),
    "longitude": float(longitude),
}' --multi

--multi tells the command to expect the Python code to return dictionaries. It will then create new columns in the database corresponding to the keys in those dictionaries and populate them using the results of the transformation.

If the places table started with just a location column, after running the above command the new table schema will look like this:

CREATE TABLE [places] (
    [location] TEXT,
    [latitude] FLOAT,
    [longitude] FLOAT
);

Common recipes

This new feature in sqlite-utils actually started life as a separate tool entirely, called sqlite-transform.

Part of the rationale for adding it to sqlite-utils was to avoid confusion between what that tool did and the sqlite-utils transform tool, which does something completely different (applies table transformations that aren't possible using SQLite's default ALTER TABLE statement). Somewhere along the line I messed up with the naming of the two tools!

sqlite-transform bundles a number of useful default transformation recipes, in addition to allowing arbitrary Python code. I ended up making these available in sqlite-utils convert by exposing them as functions that can be called from the command-line code argument like so:

sqlite-utils convert my.db articles created_at \
    'r.parsedate(value)'

Implementing them as Python functions in this way meant I didn't need to invent a new command-line mechanism for passing in additional options to the individual recipes - instead, parameters are passed like this:

sqlite-utils convert my.db articles created_at \
    'r.parsedate(value, dayfirst=True)'

Also available in the sqlite_utils Python library

Almost every feature that is exposed by the sqlite-utils command-line tool has a matching API in the sqlite_utils Python library. convert is no exception.

The Python API lets you perform operations like the following:

db = sqlite_utils.Database("dogs.db")

db["dogs"].convert("name", lambda value: value.upper())

Any Python callable can be passed to convert, and it will be applied to every value in the specified column - again, like using map() to apply a transformation to every item in an array.

You can also use the Python API to perform more complex operations like the following two examples:

# Convert title to upper case only for rows with id > 20
table.convert(
    "title",
    lambda v: v.upper(),
    where="id > :id",
    where_args={"id": 20}
)

# Create two new columns, "upper" and "lower",
# and populate them from the converted title
table.convert(
    "title",
    lambda v: {
        "upper": v.upper(),
        "lower": v.lower()
    }, multi=True
)

See the full documentation for table.convert() for more options.

A more sophisticated example: analyzing log files

I used the new sqlite-utils convert command earlier today, to debug a performance issue with my blog.

Most of my blog traffic is served via Cloudflare with a 15 minute cache timeout - but occasionally I'll hit an uncached page, and they had started to feel not quite as snappy as I would expect.

So I dipped into the Heroku dashboard, and saw this pretty sad looking graph:

Somehow my 50th percentile was nearly 10 seconds, and my maximum page response time was 23 seconds! Something was clearly very wrong.

I use NGINX as part of my Heroku setup to buffer responses (see Running gunicorn behind nginx on Heroku for buffering and logging), and I have custom NGINX configuration to write to the Heroku logs - mainly to work around a limitation in Heroku's default logging where it fails to record full user-agents or referrer headers.

I extended that configuration to record the NGINX request_time, upstream_response_time, upstream_connect_time and upstream_header_time variables, which I hoped would help me figure out what was going on.

After applying that change I started seeing Heroku log lines that looked like this:

2021-08-05T17:58:28.880469+00:00 app[web.1]: measure#nginx.service=4.212 request="GET /search/?type=blogmark&page=2&tag=highavailability HTTP/1.1" status_code=404 request_id=25eb296e-e970-4072-b75a-606e11e1db5b remote_addr="10.1.92.174" forwarded_for="114.119.136.88, 172.70.142.28" forwarded_proto="http" via="1.1 vegur" body_bytes_sent=179 referer="-" user_agent="Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot)" request_time="4.212" upstream_response_time="4.212" upstream_connect_time="0.000" upstream_header_time="4.212";

Next step: analyze those log lines.

I ran this command for a few minutes to gather some logs:

heroku logs -a simonwillisonblog --tail | grep 'measure#nginx.service' > /tmp/log.txt

Having collected 488 log lines, the next step was to load them into SQLite.

The sqlite-utils insert command likes to work with JSON, but I just had raw log lines. I used jq to convert each line into a {"line": "raw log line"} JSON object, then piped that as newline-delimited JSON into sqlite-utils insert:

cat /tmp/log.txt | \
    jq --raw-input '{line: .}' --compact-output | \
    sqlite-utils insert /tmp/logs.db log - --nl

jq --raw-input accepts input that is just raw lines of text, not yet valid JSON. '{line: .}' is a tiny jq program that builds {"line": "raw input"} objects. --compact-output causes jq to output newline-delimited JSON.

Then sqlite-utils insert /tmp/logs.db log - --nl reads that newline-delimited JSON into a new SQLite log table in a logs.db database file (full documentation here).

Now I had a SQLite table with a single column, line. Next step: parse that nasty log format.

To my surprise I couldn't find an existing Python library for parsing key=value key2="quoted value" log lines. Instead I had to figure out a regular expression:

([^\s=]+)=(?:"(.*?)"|(\S+))

Here's that expression visualized using Debuggex:

I used that regular expression as part of a custom function passed in to the sqlite-utils convert tool:

sqlite-utils convert /tmp/logs.db log line --import re --multi "$(cat <<EOD
    r = re.compile(r'([^\s=]+)=(?:"(.*?)"|(\S+))')
    pairs = {}
    for key, value1, value2 in r.findall(value):
        pairs[key] = value1 or value2
    return pairs
EOD
)"

(This uses a cat <<EOD trick to avoid having to figure out how to escape the single and double quotes in the Python code for usage in a zsh shell command.)

Using --multi here created new columns for each of the key/value pairs seen in that log file.

One last step: convert the types. The new columns are all of type text but I want to do sorting and arithmetic on them so I need to convert them to integers and floats. I used sqlite-utils transform for that:

sqlite-utils transform /tmp/logs.db log \
    --type 'measure#nginx.service' float \
    --type 'status_code' integer \
    --type 'body_bytes_sent' integer \
    --type 'request_time' float \
    --type 'upstream_response_time' float \
    --type 'upstream_connect_time' float \
    --type 'upstream_header_time' float

Here's the resulting log table (published using datasette-publish-vercel).

Once the logs were in Datasette, the problem quickly became apparent when I sorted by request_time: an army of search engine crawlers were hitting deep linked filters in my faceted search engine, like /search/?tag=geolocation&tag=offlineresources&tag=canvas&tag=javascript&tag=performance&tag=dragndrop&tag=crossdomain&tag=mozilla&tag=video&tag=tracemonkey&year=2009&type=blogmark. These are expensive pages to generate! They're also very unlikely to be in my Cloudflare cache.

Could the answer be as simple as a robots.txt rule blocking access to /search/?

I shipped that change and waited a few hours to see what the impact would be:

It took a while for the crawlers to notice that my robots.txt had changed, but by 8 hours later my site performance was dramatically improved - I'm now seeing 99th percentile of around 450ms, compared to 25 seconds before I shipped the robots.txt change!

With this latest addition, sqlite-utils has evolved into a powerful tool for importing, cleaning and re-shaping data - especially when coupled with Datasette in order to explore, analyze and publish the results.

TIL this week

• Search and replace with regular expressions in VS Code
• Check spelling using codespell
• Set a GIF to loop using ImageMagick
• SQLite aggregate filter clauses
• Compressing an animated GIF with ImageMagick mogrify

Releases this week

• sqlite-transform: 1.2.1 - (10 releases total) - 2021-08-02
  Tool for running transformations on columns in a SQLite database
• sqlite-utils: 3.14 - (82 releases total) - 2021-08-02
  Python CLI utility and library for manipulating SQLite databases
• datasette-json-html: 1.0.1 - (6 releases total) - 2021-07-31
  Datasette plugin for rendering HTML based on JSON values
• datasette-publish-fly: 1.0.2 - (5 releases total) - 2021-07-30
  Datasette plugin for publishing data using Fly

Breaking Changes to the Web Platform

"Over the years there have been necessary changes to the web platform that caused legacy websites to break." - this list is thankfully very short, only 11 items so far. Let's hope it stays that way!

Via @styfle

Ish via Simon Willison, in a blog post describing some recent updates to sqlite-utils that brings sqlite-utils/datasette a step close to doing what OpenRefine does (Apply conversion functions to data in SQLite columns with the sqlite-utils CLI tool), I note debuggex.com, a tool for visualising regular expressions as syntax diagrams (albeit, not open source, so no local running…).

Deutsche Welle: RKI: Inzidenz steigt früher und schneller als im Sommer 2020. “In seinem jüngsten Wochenbericht hält das Robert Koch-Institut äußerst beunruhigende Wahrheiten zu Corona bereit. Die Berliner Charité hat einmal mehr eine Gegenoffensive gestartet.”

“Die Sieben-Tage-Inzidenz in Deutschland steigt derzeit laut Robert Koch-Institut (RKI) mehrere Wochen früher und schneller wieder an als im Sommer 2020. Dies sei “trotz steigender Impfquote” festzustellen, schrieb das RKI bei Twitter. Die Sieben-Tage-Inzidenz ist ein wesentlicher Maßstab für die Verschärfung oder Lockerung von Corona-Auflagen. Sie gibt die Zahl der Neuinfektionen pro 100.000 Einwohner innerhalb einer Woche an.

Wie aus dem neuen RKI-Wochenbericht hervorgeht, können Gesundheitsämter nicht mehr alle Infektionsketten nachvollziehen.”

Let's take this post on memes from 2018 as a starting point. I confess I hadn't seen it before, but hey, one can't see everything. Just so, one can't be everything, and I think that's the point of this article. And that's what Twitter - or maybe life in general - demands. "I didn’t consider my articles complete without a reaction. Twitter, teeming with peers, mattered more than a general comments section." Yeah, maybe. Or maybe it's this need for approval generally. It's a hard skill to learn, to be happy with your work without the benefit of external validation. Sometimes I feel I've mastered it (as when I ran a radio station for several years with no listeners). Sometimes, not so much.

At 6:30 PM on Wednesday August 4^th my 15-year-old daughter and I pulled up the Jaguar I-Pace electric car in front of my 91-year-old Mom’s place in Regina, Saskatchewan. I was tired and achey because I’d just finished driving 1,725km (1,072 miles) across two days to see her for the first time since Covid started. I was happy to see Mom, happy about the first road-trip in a long time, and happy to have tested the hypothesis that, in 2021, a fully-electric vehicle can handle long-haul travel.

This essay gathers together the data from the trip and tries to draw conclusions. There’s also a real-time Twitter thread with typos and bad pictures.

To my non-metric readers: Sorry, it’s in km. I’ll convert a few of the key numbers.

The experience

It was pretty wonderful, actually. The Jaguar is a comfortable modern car with great seats, good audio, and all the automation you’d expect. It has awesome, overwhelming acceleration power for when you’re in a tricky passing situation. My daughter was excellent company. Cruising along a good road — and a lot of the Trans-Canada highway is — becomes a pretty pleasing experience.

The worst part, by a wide margin, was the wildfire smoke, between us and some of the world’s most fantastic scenery. But that’s a symptom of the onrushing climate crisis, and one of the best things we can do to mitigate the devastation is to stop burning fossil fuels to travel.

Of course, this road trip was different from any previous experience, because charging. In a fossil car, you don’t have to think, you just wait for the tank to get a half or three quarters down, then pull over at the next station. Recharging requires planning; fortunately the tools are pretty good; more on that below.

The chargers

One reason I decided this experiment was worth trying was Petro-Canada’s message about their Electric Highway program, and I quote: “We have a charger every 250 km or less from Halifax, N.S. to Victoria, B.C.” There’s one not far from where I live in Vancouver, i tried it, and it worked first time with just a credit-card tap, no fuss no muss.

Trouble is, that quote is kind of a lie. There are gaps, and that’s when all the chargers are working, which fairly regularly they’re not. But my experience is that Petro-Can, while good, is never your only charging option.

Some background is required here. “High-power” Fast DC chargers come at multiple power levels: I saw 50, 100, 200, and 350kW. The difference makes a difference. Our Jag can only really charge at 100kW, but my personal perception is that the higher-power chargers fill up that last 20% much faster. And it feels complicated; for example, in my experience with a Co-op Connect charger, rated at “only” 100kW, it felt faster.

When you use these things, they feel like first-generation tech, pushing the edges of what’s possible (or at least maintainable). In particular, when you plug a 350kW charger into a car with a really low battery, once it’s finished syncing and starts pumping electrons, the sound torques up like a 747 taking off. And the installations include multiple big tall metal boxes (see the picture below). Also, the huge big thick connecting wire gets super hot to the touch.

Anyhow, my impression of the Petro-Can network remains mostly positive. The machines work well. It’s annoying that some are 100kW, some 200, and some 350, for no obvious reason. It’s annoying that sometimes they’re stuck into a weird grubby back corner of the lot in a way that makes it hard to get your car in the right position to reach the charging port with the wire. But, good on ’em.

Electrify Canada is another organization that’s promising a national network of fast chargers. They’re a partner of Electrify America, constructed by Volkswagen as part of their settlement over cheating on emissions testing. Anyhow, maybe they’ll be great some day. Once I was far enough into the trip to have Petro-Can fully worked out, I tried to find an Electrify charger in working condition but failed.

If you’re OK with using 50kW chargers there are loads and loads of options. Many smaller-town Visitor Centres and and Chambers of Commerce put one in, as has my own electrical utility, BC Hydro. Once you’ve worked with a higher-power charger though, they’re just not a satisfying experience.

The numbers

Each line in the table below represents one driving leg and includes the charging experience at the beginning of that leg (thus absent on each day’s initial leg). I think the column headings are mostly pretty obvious, except perhaps for:

1. kWh/h is the amount of juice divided by the driving pause. Often the charger would report less time, which I put down to initialization delay, so I think I’m using the right value. The variation here is a little random, because how fast it goes depends strongly on how empty your battery is.

2. km/ch stands for “km per charge-hour”, estimating the amount of road range you get per hour of charging. Which I think is a really important number.

3. Network; “PC” is Petro-Canada, “Flo” isn’t an acronym, and “Co-op” is Co-op Connect.

There are two data sources: The drive-time data is from Jaguar’s trip logging via their Incontrol app; thus the awkwardness caused by non-charge roadside stops. The charge-time data is the output from the various charging sessions along the way. I’m certain that neither is perfect, but the results seem intuitively in the right neighborhood, based on my experience.

Let’s have a closer look at the numbers that seem interesting to me.

Charge time

17:42 driving, 6:24 charging (including one evening charge after the day’s drive was finished). Not that great on the face of it. Now, it clearly could have been less; since my confidence that any given charger would Just Work started out weak, I was carefully allowing for failures and not running the battery very low. Later on in the trip as I gained confidence (specifically in the Petro-Canada network) I was willing to take on things like the Calgary-to-Medicine Hat leg, 2:43 and 285.9km, running the battery from 90% down to 11%.

Also my daughter is after all a teen-ager, and perhaps not quite as quick as I moving through cafes and restrooms and so on.

Also note that the legs are kind of short; this car can go 400km on a charge. But not when you’re on a big wide modern Prairie highway with almost no other traffic, blasting along at 110km/h (65mph) or more, continuously. I think you’d find this true of pretty well every electric vehicle.

But, here’s the thing: It didn’t feel excessive. I can only remember a total of maybe fifteen minutes when we were consciously just hanging waiting for charge. Most places, we got a coffee or lunch, hit the bathroom, took a walk around the block for our knees’ sake, and then it was time to unplug and go.

Having said that, this is a 2019 model-year car and the charging technology is improving. If we’d had a Porsche Taycan and nothing but 350kW chargers, the story would have been very different. Will the fast-charging technology make the leap into the mainstream-car price plane? Will 350kW chargers become ubiquitious? I’d like to know.

In this context, there’s another number there that I think is really interesting: The “km/c-h”, how far you can get on an hour’s charge. For this particular car on this selection of chargers, it was over 200km (124 miles) per charge-hour. I think that’s enough? Maybe in the lower regions of enough, but there.

And finally, I suspect every driving-safety professional would beam in approval of a power system that forces you to get out of the car and move around every couple of hundred km.

$$$

It cost us $120.52 in electricity. Is that a lot or a little? I tentatively think they’re undercharging. While the electricity itself is pretty cheap, the charging infrastructure isn’t. If this is going to work, the charging networks are going to have to make money and I don’t see it at these prices.

Bear in mind that at home with the Level 2 charger in the carport, charging feels close to free. Travel maybe doesn’t need to be as cheap as the networks are currently making it.

Also, charging by the minute seems wrong. I guess having a time-based component makes sense to keep slow chargers from soaking up all the time, but especially at an extra-high-powered charger, a Porsche Taycan is going to get a whole lot more range out of each minute than a five-year-old Nissan Leaf, so why should they pay less for the same amount of range? Hmmmm.

PlugShare

If you’ve got a Tesla there’s less planning, the cars know where the Superchargers are. If you have anything else, you really need PlugShare. There are a few apps in this space, but PlugShare is best at showing you a map with all the chargers on it, and thus helping you route-plan. The reason it works is because it’s social; whenever you hit a charging station you can “Check In” and leave a note saying whether it’s working and how fast it goes. This dramatically reduces the risk of rolling up to a station and finding it broken. I absolutely don’t think this journey would have been possible without it.

Pro tip: When you’re planning a trip on PlugShare, put in all the chargers you might be able to use as you go along. Then when you’re driving, you can look at your remaining range and your upcoming options and most choices become pretty easy. It’s got a limited but decent Android Auto app that I used a lot. (I assume CarPlay too?)

Futures

A question: Are there enough chargers, or too many? At the moment, the answer is probably “too many”. One of the things I really worried about was limping into some charging station to find all the chargers occupied and having to wait for an hour before I could even start. The picture above shows the only time I saw other humans; a young couple with a four-day-old Mustang Mach-E, off for a joyride to Kamloops. Otherwise, the chargers we visited showed no signs of life. Somebody spent a lot of money to build an expensive resource that is today largely un-used.

Having said that, anyone with even a shred of optimism about our future has to believe there are going to be a whole lot more battery-electric cars coming. Here in BC at the west edge of Canada we have North America’s highest EV uptake, pushing 10% of new car sales.

When we were charging at the big Petro-Can station in Kamloops, walking from the the two well-positioned chargers to the coffee shop, we went by the gas-sales part, which was massive, at least a dozen pumps and cars lined up for every one.

At some point that picture will flip, and there’ll be occasional vendors that still sell gas, but mostly just slick, fast, chargers. I worry that the process will be kind of painful, but I’m sure it’ll happen. So I hope someone’s planning the transition.

Would you do it again?

Definitely.

And everyone should stop driving fossil vehicles starting now. Because the climate crisis is upon us. We can’t prevent it now, but we can save lives and reduce destruction if we slash carbon output. There’s no excuse not to.

The thing about semver major version numbers are that they don't mean new stuff, they're a permanent reminder of how many times you got the API wrong. Semver doesn't mean MAJOR.MINOR.PATCH, it means FAILS.FEATURES.BUGS

— Will McGugan

Framework is a new company offering a laptop that is designed to be repairable and upgradeable, both in terms of internal components like the screen and motherboard, and in pluggable expansion cards.

Table of Contents

1. Hardware
2. Assembly
3. Expansion Cards
4. Firmware
5. Battery
6. OpenBSD Support Log
7. Current OpenBSD Support Summary
8. Conclusion

Hardware

While many classic ThinkPad owners will probably say “so what?” to a repairable and upgradeable laptop, doing so in an ultrabook-style thin and light laptop in 2021 is fairly novel. Each new ThinkPad I’ve used lately has become less and less repairable and upgradeable, with RAM and WiFi cards now being soldered to the motherboard.

The Framework Laptop became available for pre-order in May and was offered in two configurations of essentially the same device: a pre-built option and a DIY option that can be purchased without RAM, an SSD, a WiFi card, or a Windows license.

Both configurations offer the same 13.5” 3:2 aspect ratio screen and various Intel CPU options. I opted for the Intel i7-1185G7 processor Intel i5-1135G7, after exchanging it for the i7 model which I initially purchased.

The laptop weighs 2.8 lbs and is 11.6” wide, 9” tall, and 0.6” inches thick. It has basically two USB-C ports on each side, although they are recessed about 1.5” to accommodate the expansion cards which are just various USB-C dongles in uniform cases. Framework offers USB-C, USB-A, HDMI, and DisplayPort connector cards, along with a MicroSD card reader and 250GB and 1TB storage devices. Currently all of the cards desired must be purchased with the laptop at the time of ordering, and they are not yet available for sale separately.

In between the two expansion slots on the left side is an LED for charging status and a 3.5mm headphone jack to the right of the slots. On the right side of the laptop is another LED for charging, if charging via USB-C connection on that side.

There are two downward-firing speakers and a fan exhaust on the bottom. The speakers sound excellent and have a sufficient amount of bass (but not overpowering like the new MacBook Air). The fan is inaudible at idle, but can get very loud at full speed when doing a high amount of CPU and disk activity such as a git clone. It makes a little click/squeak when turning on, and in some cases can come on and turn off every few seconds which makes it kind of annoying in a very quiet room. There is no audible coil whine from any of the components.

The keyboard deck has a power button with integrated fingerprint reader in the upper-right corner. Unfortunately it also contains a white LED ring around it that is very bright and does not dim with the keyboard backlight, nor does it turn off when the fingerprint reader is disabled in the BIOS. Since the button is so close to the screen, it remains in my field of view and is very annoying in a dark room.

Update (2021-11-12): The 3.06 BIOS update adds a “Power Button LED Brightness Level” adjustment that can toggle between low, medium, and high. Even at the lowest setting it’s still kind of annoying, though. I wish it could be turned off completely. The images below show the light with the “high” setting which it defaulted to before.

The keyboard has 1.5mm of key travel with a pronounced but quiet tactile. It reminds me of the MacBook Pro 2015 keyboard but with slightly more tactile. I wish the arrow keys were an inverted T, however. The keyboard has 3 levels of backlight (plus off) adjustable with Fn+Space. The surface of the keys is somewhat rough and matte.

Update (2021-12-07): Framework now offers transparent and blank keyboards, the latter of which I purchased and installed.

The 13.5” non-touch screen has a 3:2 aspect ratio and a 2256x1504 resolution, which is high enough to use 1.5x scaling. It is reportedly a >400 nit screen, and most of the time in my office I’m using it at about 30% brightness.

The screen lid is quite bendy, and has a lot of wobble on its hinge although the hinge itself doesn’t seem to wobble and is quite tight. This means that it doesn’t wobble in response to typing or moving things on my desk, which is what I was concerned about, but does wobble excessively when making screen angle adjustments. The lid can be mostly opened all the way with one hand and will open fully to 180 degrees. Centered on the back of the lid is the Framework logo in black, with “Framework” branding written on the front of the hinge in glossy black against the matte black plastic making it very discreet in most lighting.

The screen bezel is sufficiently thin, but still has space for a 1080p webcam with a physical cut-off switch (showing a red strip when disabled) next to the microphone which also has its own cut-off switch. The bezel is held on magnetically to make it easier to upgrade or change. If you’re wondering like I was, the square to the left is an ambient light sensor (though it is not exposed as a standard ACPI ALS device) and the one on the right is the actual camera. There is no Windows Hello IR camera.

The Windows Precision Touchpad is made by PixArt. Its surface is not quite as smooth as I would like but is not bad, although the click mechanism is somewhat loud and hollow sounding which makes it seem cheap.

Assembly

Since I purchased the DIY version, mine came with the 32GB of Crucial RAM that I ordered, along with an Intel AX210 WiFi card (which I swapped out for an AX201 due to OpenBSD compatibility) in separate packaging.

The Framework Laptop includes a screwdriver in the box with a removable Torx and Phillips bit on one end, and a spudger on the other. All of the screws on the laptop, from the captive ones on the bottom that release the keyboard deck (rather than the bottom casing coming off as on most laptops) to the internal screws on the WiFi card, SSD, and other internal components, can be removed with the same Torx bit.

Installation of the RAM and the Samsung 980 Pro 1TB NVMe SSD that I already had were very simple. Installing the WiFi card took a minute or two due to the custom bracket that is supposed to be installed over the antenna connectors, but is oriented differently when temporarily installed from the factory. All of the component locations on the motherboard have QR codes which direct one to URLs on the Framework website for instructions.

Expansion Cards

I initially installed USB-C and USB-A expansion cards on the left side, and a USB-C and 1TB storage card on the right which will be used for backups.

I like that the expansion cards keep the ports a good distance from each other and that there can be USB-C charging ports on either side. I’ve often run into problems with the two USB-C ports on my MacBook Air being too close to each other and with them both on the left side, I can’t opt to charge on the right side if I happen to be sitting with the wall outlet on that side of me.

Framework has an Expansion Card Developer Program to support 3rd party expansion cards and they’ve also made available the schematics and case designs for their existing cards. I miss the days of funky PCMCIA cards which brought so much expandability to laptops without needing dongles.

In the future I’d like to make a simple 3D-printed case for the Creative BT-W3 Bluetooth adapter and maybe one for the Logitech mouse dongle, allowing them to be plugged in without sticking out.

Firmware

The Framework Laptop has an InsydeH2O BIOS that is accessible upon pressing F2. Secure Boot must be disabled in the BIOS menu to boot OpenBSD, and CSM/Legacy booting is not supported. I opted to disable the fingerprint reader and Hyperthreading.

Battery

I don’t usually publish battery life estimates for laptops because power usage on modern laptops can vary wildly with screen brightness and CPU load, whether it’s constantly Turbo boosting, etc. The Framework Laptop has a 55 Watt-hour battery and it idles around 9 Watts with the screen at 25% brightness and connected to WiFi.

OpenBSD Support Log

2021-08-04: Initial boot of OpenBSD-current showed a strange issue once inteldrm took over from efifb. The screen would go blank and then the backlight would flicker off and on every second or so with the screen still black. Recompiling the kernel with DRMDEBUG enabled showed that it was having trouble training the eDP panel properly, although enabling debugging caused it to print messages to the console during the whole process, introducing enough delay that it could complete the training properly. Eventually I found that by switching between X and the console a few times, the screen would train and work properly, but after an xset dpms force off, or trying to resume from S3, this workaround does not usually work. I’m still trying to debug this, but I can’t reproduce it in Linux with the same DRM code that we have in OpenBSD (based on Linux 5.10.56).

I also started having stability issues with the machine randomly powering off which was extremely frustrating, but I eventually tracked it down to the touchpad as the culprit. Since OpenBSD does not yet have a Tiger Lake GPIO interrupt driver, the touchpad is working in polling mode (via ihidev). At some point this goes haywire (minutes to hours) and touching the touchpad results in the machine instantly powering off. I’m making a guess that the EC is crashing while trying to interface with the touchpad. By disabling the dwiic i²c driver, the touchpad becomes available through the legacy PS/2 interface, allowing it to work through the pms mouse driver. However, after a day of using this legacy configuration, the same power-offs would randomly happen when touching the touchpad. There is a BIOS option to disable this legacy interface, which I did, and am now running without a working touchpad until I can write a GPIO driver.

I’ve experienced a few other issues with the laptop that seem related to the EC, such as keyboard input in the EFI bootloader dropping keys and being very sluggish (this happened only once), USB-C ports refusing to accept power for charging until the system is powered off and back on again (I’ve had this happen a number of times), and it not restarting when instructed to by the kernel/ACPI (a few times). I’m sure all of these issues will work themselves out over time through future firmware/EC updates.

2021-08-08: While comparing debugging output of the Intel DRM driver in Linux and OpenBSD, I noticed that on OpenBSD, it was failing to parse the VBT of the graphics device, which contains crucial information about the connected panel and output ports. This explains why the panel was not able to be trained properly and why HDMI output didn’t work. Some more debugging located the cause of the problem, which was that the OpenBSD-specific code in the OpRegion-handling module was not mapping the correct base address, so the kernel was mapping junk and unable to find a valid VBT in that junk. I committed a fix for the bug and now the integrated display initializes normally when inteldrm takes over from efifb at boot, as well as when waking up from a DPMS sleep and upon S3 resume. I also re-tested the HDMI output Expansion Card and HDMI output works as expected.

2021-08-09: I added Tiger Lake LP support to pchgpio, enabling GPIO interrupt support for ihidev, which was needed to make the touchpad work properly in non-polling mode (and apparently not cause the EC to randomly freak out and power down the system).

2021-08-14: After some buyer’s remorse in spending $700 extra on the i7-1185G7 model (and discovering it has Intel AMT), I contacted Framework to ask about exchanging it for an i5-1135G7 model. They were very helpful and accepted the return of my i7 model and processed a new order for an i5 model, which I received within a few days without having to wait in line again for the next batch, which doesn’t ship until October.

2021-08-31: Closing the laptop’s lid does not currently cause an S3 suspend due to a bug in the DSDT of the Framework Laptop which references an invalid EC device in the ECDT. I’ve been told by Framework that this will be fixed in a future BIOS update.

2021-09-21: A fix has been committed for the touchpad not working after S3 resume.

2021-11-12: The 3.06 BIOS update added a “Power Button LED Brightness Level” adjustment to finally be able to lower the brightness of the ring around the power button. It also fixes the lid sensor interrupt that allows OpenBSD to suspend when the lid is closed.

Current OpenBSD Support Summary

Status is relative to OpenBSD-current as of 2021-09-21.

Conclusion

Framework has done well with their future-thinking design and prioritizing repairability and upgradeability. Whether the company, replacement parts, and future motherboard/processor upgrades will be around in five or ten years to make good on their promises remains to be seen, but I feel like we’ll never get there unless consumers support the company in the beginning.

My concern before receiving my laptop was that reviewers and consumers would be expected to look past a lot of faults just because it was repairable and upgradeable, as can been seen with modular phones and other devices. However, I feel that the Framework Laptop is a well-executed product that can stand on its own right now and any future upgradeability is just icing on the cake.

I hope the expansion card idea takes off and 3rd party developers create some really interesting things.

Editor note: Japan had two different military programs working on developing an atomic bomb. The movie reviewed here only discusses one of the programs. Japan’s attempt to develop nuclear weapons was much more successful than imagined, click here and read What If Japan Had the Atomic Bomb First? For more details.

Review by Kaori Shoji

“Gift of Fire (Japanese original title: Taiyo no Ko)” proffers an unsettling view from an old and familiar window. Directed by Hiroshi Kurosaki, the gist of this story set in the summer of 1945, is this: just weeks before Japan’s surrender in WWII, a team of graduate students at Kyoto University were hard at work on the creation of a nuclear bomb. While this piece of information may not be news to many western historians, the majority of the Japanese are bound to feel baffled. For generations, the Japanese were conditioned – by our elders, by the media, by the education system and history itself, to feel that we were the victims of a war that very few in the populace ever wanted to fight. And now a Japanese movie is saying we could have been the perpetrators of the world’s first nuclear bomb attack? That’s an incredibly heavy load to process, and still more to confront. 

After all, Japan had banked the struggle of the postwar years and the rapid growth era that followed it, on Hiroshima and Nagasaki. The horrors that happened on August 6th, 1945 in Hiroshima and then, a mere 72 hours later in Nagasaki, were recounted through generations and revived in the media more times than anyone could count. It was a legacy of suffering, a collective mantle of unspeakable sadness under which the Japanese toiled and labored for decades to come. 
At the same time, the two bombs exonerated the Japanese from having to explain and face up to wartime atrocities – the crimes committed in Korea, China, Singapore and pretty much the entire Asian Pacific Rim. Call it the a-bomb card, pulled out when the Koreans or Chinese got noisy about acknowledgement and reparations for war crimes. And whenever the Japanese people protested against the econo-centric measures of the government that irredeemably polluted oceans and mountains and quashed the livelihoods of  millions of traditional workers. For seventy plus years, the a-bomb card served Japanese politicians, their politics and the national conscience. We suffered enough, now leave us alone. 

But “Gift of Fire” says it may be about time to turn in that card. 

The Kyoto University lab was under close scrutiny by the Imperial Army and the scientists were pressed for results. If and when they succeeded in splitting the atom, the plan was to drop the bomb on San Francisco. The students were plagued by doubts and filled with anxiety, and who can blame them. Nightly air raids, frequent power outages and utter lack of resources prevented their experiments from moving forward when all the while, they were listening to American news on a hand-crafted short wave radio. The Americans were closing in on the Japanese Imperial Army though the propaganda reports from the frontlines said otherwise. “Should we even be doing this, in the comfort of a laboratory? Shouldn’t we be fighting and giving our lives to this country?” says one anguished student, who eventually quits the project to enlist in the Imperial Army. 

The truth was, the Kyoto University team lacked everything to build anything, let alone a weapon of mass destruction. Still, they forge on, fueled by a blind hope that an atomic bomb will change the course of the war or even end it. Besides, says Professor Arakatsu, the helmer of the project: “if we don’t build it the Americans will. And if the Americans don’t get there first the Soviets will. Why do you think this war started in the first place? It’s because the world is in a race to procure energy resources. Whoever gets control of the energy, gains control over the world.”  

By saying that, Arakatsu has shifted the concept of war from ideology and nationalism to science and technology. Fittingly, “Gift of Fire” is mostly devoid of sentiment and righteousness, preferring instead to sanctify the scientist and all that he stands for. In Arakatsu’s laboratory, scientific advancement is a religion, and the god at the altar is Albert Einstein. The highest prayer of course, is E=mc2. The film marks the first time a Japanese film has addressed the atomic bomb from a scientific standpoint, and bringing in an American voice to the proceedings. Kurosaki persuaded Peter Stormare to appear in a voice-only role that defends, honors and ultimately glorifies the scientific mission. 

Perhaps that voice belongs to Shu (Yuya Yagira) who is the most committed member in Arakatsu’s lab. Shu is responsible for procuring the much-needed uranium for their experiments, and turns to a local potter for his meager supply. The potter used to make beautiful things, but now he only makes funeral urns. “A lot of people dying,” says the potter matter-of-factly to Shu, pointing to the rows and rows of small white urns turned out in his kiln that day. Shu can only nod, bow and make his exit with the uranium powder stashed in his rucksack. Shu knows that Japan will hurtle toward a terrible defeat unless they can build the bomb before the Americans. At the same time he knows their chances of making that happen are practically zilch. Yet the thought of giving up never crosses his mind. A stubborn work-ethic and an obsessive regard for science dictates Shu’s actions and his MO, like the rest of his team, is to ‘ganbaru (do the best they can)’ until they drop. 

Director/writer Hiroshi Kurosaki is a seasoned television director, best known for his work on the NHK morning drama series “Hiyokko, (2017)” which means ‘youngster.’ Kurosaki has a flair for portraying youth and innocence under duress. “Hiyokko” was set in the post war years, with a young female protagonist (Kasumi Arimura) searching for her missing father in Tokyo. Arimura teams up with Kurosaki again for “Gift of Fire” as she plays Setsu, who is Shu’s possible love interest. Setsu, perhaps as a nod to our times, is not the typical docile young woman of wartime Japan. In one scene she lectures Shu and his brother Hiroyuki (played by the late Haruma Miura who committed suicide last summer. The film marks his final screen appearance) about their tunnel vision, exhorting them to look beyond the war and envision a future without violence. In the post-screening press conference Kurosaki said: “I wanted to portray what a young woman must have felt in the final days before the surrender. She’s young and has an intense desire to live and experience life, but death is always there.” 

“Gift of Fire” is not without redemption. What permeates the otherwise dark and spartan narrative is the sheer innocence of the characters, especially Shu and Hiroyuki. In their separate ways, the brothers seek closure to a war that had come to define their identifites – Shu by creating the atomic bomb, and Hiroyuki by flying a plane right into an American warship. Defeat may be imminent but neither of them are about to surrender peacefully. “They made mistakes, they’re not heroes,” said Kurosaki. “They are ordinary young men, blundering on and doing the best they can.” Sadly that’s never enough to right a ship gone horribly awry. 
END 

My webinar on “Software Design for Data Scientists” raised over $600 for Books for Africa. The key points are summarized below; if you’d like me to give the talk to your company, university, or other organization, I’d be happy to do so in exchange for a donation to BfA or some other mutually-agreed charity.

Rule 0: Computer scientists aren’t taught software design either, so don’t feel like you’ve missed something.

Rule 1: Design for people’s cognitive capacity (7±2, chunking, and all that).

Rule 2: Design toward widely-used abstractions and maximize the ratio of “what’s unique in this statement” to boilerplate, but remember that the tradeoff between abstraction and comprehension depends on how much people already know.

Rule 3: Design for evolution, because the problem, the tools, and your understanding will all change each other. The key tool for this is information hiding; the Liskov Substitution Principle and Design by Contract will help.

Rule 4: Design for testability - not just because you want to test, but because it’s a way to validate designs.

Rule 5: Design as if code was data, because it is. Programs are just text files; code in memory is just another data structure, and taking advantage of this can make designs much more elegant (but also less comprehensible to newcomers).

Rule 6: Design for delivery - organize your code the way your packaging system expects, handle errors instead of printing them and discarding them, and use a proper logging system.

Rule 7: Design graphically (but don’t try to create software blueprints). Flowcharts, informal architecture diagrams, entity-relationship diagrams, and use case maps will all help people understand the overall design.

Rule 8: Design after the fact. The most important thing isn’t to follow any particular process, it’s to look as though you did so that the next person can retrace your steps.

Rule 9: Design with villains in mind, because security, privacy, and fairness can’t be sprinkled on after the fact.

Rule 10: Design collaboratively and inclusively, because it will produce a better design (and because it’s just the right thing to do).

The National Institute of Infectious Diseases, which filed the report, has not made it public in Japan. 

By Jake Adelstein and Chihiro Kai

(Originally published at 12:08 am August 6. updated August 6, 8:35 a.m.)

For a multi-language database of clinics offering a wait-list for vaccine appointments, click here 

The National Institute of Infectious Diseases (Tokyo) reported the first finding of the highly infectious Lambda variant of COVID19 here to an international database three days before the Olympics Opening Ceremony. The Institute has not publicly disclosed the details in Japan yet. One scientist who worked on the report told Japan Subculture Research Center that it was detected at an airport checkpoint and had not made it “into the wild”. He believes it originated in Peru but public data suggests it came from the US into Japan. Please note, on August 5th, Tokyo alone recorded 5,000 new cases of COVID19, the highest number since the pandemic began in Japan. Many infected are being told to self-medicate as hospitals fill up with serious cases. The entry of the Lambda variant into Tokyo is not a welcome development. 

On July 20th, three days before the Tokyo Olympics began, Japan’s National Institute of Infectious Diseases reported, for the first time, that the highly infectious Lambda variant (ラムダ株) had been found in country. The report was submitted to an international COVID19 and other infectious diseases database known as GISAID. The Japanese government has not formally announced that the variant, first found in Peru, had also been found here now, as well.  The variant has now been found in 26 countries. Japan could be the 27th country to host the virus.

Last month, a team of researchers at Tokyo University published an academic paper which noted that the Lambda variant was highly infectious and resistant to vaccines. In Peru, where the variant was first discovered, 80% of the infections are now traced to the Lambda variant. The research team at Tokyo University believes the variant “has potential to be a threat to the human race”.  (ラムダ株が人類社会に潜在的な脅威になり得る)

The National Institute first reported finding the Lambda variant to the GISAID database on July 20. GISAID is non-profit organization that maintains a database for infectious diseases including COVID-19, founded in 2008. GISAID originally stands for Global Initiative on Sharing Avian Influenza Data. The database shared the first complete genome sequences of COVID19 in early January 2020 and there have been nearly 2.5 million submissions logged with the database since. Institutes submitting data to the group must have their credentials confirmed and agree to a database access agreement.

The variant was confirmed by the SARS-CoV-2 testing team at the National Institute of Infectious Diseases (Tokyo) and the data submitted by the Pathogen Genomics Center at the same institute. Nozumu Hanaoka (花岡希) a senior research scientist at the Infectious Diseases Center for Infectious Disease Risk Management and several other researchers signed off on the submission. 

Unanswered Questions

The National Institute of Infectious Diseases has not responded to requests for further information about the discovery of the variant. For example, when was the variant found in Japan? Was it found at the airport and never made it to the wild? Was it brought to Japan by a participant in the Olympics? Where was the carrier of the virus located? Calls made to the Ministry of Health, Labor and Welfare which oversees the institute were not returned. We will continue to pursue the story and get clarification.

Any information pertaining to the Lambda variant in Japan would be welcome. Please post in the comments, which are reviewed before being made public. If you wish to submit information without disclosing your name, let us know, we will contact you privately and remove the comment if requested. 

What You Should Know About the Lambda Variant

Lambda’s origin and WHO classifications for COVID-19 variants. 

The Lambda variant of COVID-19 was first discovered in Peru in August 2020. As of July 14, 2021, it made up roughly 90% of COVID-19 infections in the nation and is likely responsible for the spike in coronavirus cases in Peru’s second wave of infections this spring. The World Health Organization designated the Lambda variant as a “Variant of Interest” or VOI on June 14, 2021, the lower of two classifications used to survey the public health risks of existing COVID-19 strains. 

A Variant of Interest is defined as a COVID-19 variant with genetic changes that are predicted to affect the transmissibility, disease severity, and the ability of the virus to escape diagnosis and medical treatments. Furthermore, VOIs are identified to cause significant community transmission in multiple countries and suggest an emerging risk to global public health. 

The good news is variants under the VOI classification carry a “keep a close eye on it” designation where WHO and member states monitor the spread as a precaution. Although the emergence of a VOI in a new country, like Lambda’s introduction to Japan in July, should be investigated, medical and government officials are more concerned about Variants of Concern. 

A VOC is a variant that meets the definition of a Variant of Interest and is shown to be more contagious, induce heavier symptoms, and less responsive to available public health and social measures. The Delta variant, currently the world’s predominant strain as contagious as chickenpox, is categorized as a VOC. 

The Lambda variant in Japan. What we know so far. 

According to the Global Initiative on Sharing All Influenza Data, GISAID, a primary international source for open-access influenza virus data, the Lambda case detected in Japan was transmitted from the United States.

It is currently unclear as to how, why, or where this transmission took place. However, there have been no other Lambda cases declared in the country. Whether this is due to a properly followed quarantine protocol or a lack of Japan’s ability to detect and report additional infections is unknown. Click here to access the complete interactive Lambda database. 

On July 28, Japanese scientists posted a report on the Lambda variant eight days after its domestic detection. The document is yet to be peer-reviewed. 

In the document, the authors state that the Lambda variant is highly infectious, less susceptible to current vaccinations, and shows resistance to antiviral immunity elicited by vaccination. The report continues that because the “Lambda variant is relatively resistant to the vaccine-induced antisera” (blood serum containing antibodies produced in response to vaccination), “it might be possible that this variant is feasible to cause breakthrough infection” in already vaccinated populations. The scientists worry the variant’s categorization as a VOI instead of a VOC downplay the virus’s potential threat to public health. 

What you can do to protect yourself and your community from COVID-19 variants. 

Although the Japanese scientists’ pre-print report suggests that Lambda may possess a greater ability to escape vaccine-induced immunity, currently available vaccines are still the best way to significantly decrease your chances of catching and transmitting the virus. Vaccines provide even better protection against severe illness and death from COVID-19. 

Dr. Anthony Fauci, the director of the U.S. National Institute of Allergy and Infectious Diseases, said the current surge in COVID-19 cases caused by insufficient vaccination rates gives the virus “ample” time to mutate into a more dangerous new variant in the fall and winter. 

“[Q]uite frankly, we’re very lucky that the vaccines that we have now do very well against the variants — particularly against severe illness,” Fauci said to McClatchy Washington Bureau on August 4.

 “If another one comes along that has an equally high capability of transmitting but also is much more severe, then we could really be in trouble,” he said. “People who are not getting vaccinated mistakenly think it’s only about them. But it isn’t. It’s about everybody else, also.”

As of Wednesday this week, only 32.39% of the Japanese population has been fully vaccinated against COVID-19. The most effective way to prevent further illness and death from all variants of the coronavirus is to promptly get as many residents of Japan fully immunized. For a multi-language database of clinics offering a wait-list for vaccine appointments, click here. 

Whenever I start thinking that writing is too hard, or that I can't do it without the right pen or the right technology I think about Graham Greene:

"Graham Greene was an almost eerily disciplined writer. He could write in the middle of wars, the Mau Mau uprising, you name it. And he wrote, quite strictly, five hundred words per day, in a little notebook he kept in his chest pocket. He counted the words, and at five hundred he stopped, even, his biographer says, in the middle of a sentence. Then he started again the next morning." 

New Yorker - 8 March 2021

“Do your own research” is one of the most pernicious phrases on the internet. Hucksters use it to fool people. It will lead you astray. Based on my 20 years as a researcher, I’ll delve into what research actually is, what it means when someone writes “do your own research,” and why it leads to … Continued

The post The problem with “Do your own research.” appeared first on without bullshit.

Hi, Rose here. My job is to write for the Datawrapper blog. But some people’s job is to throw a javelin, and other people’s job is to row a boat! Watching those people compete at the Olympics this week has gotten me thinking about how we all got to this point...

By the time a medalist stands on the Olympic podium, they’ve trained for years to reach the limits of human capacity — capacity to do one extremely specific thing. To get there, you need a few factors. You need unbelievably hard work. You need incredible mental determination. You need rare natural gifts. But you also need to find your thing. And unless you’re born into some kind of athletic dynasty, finding a sport you excel at and like takes a certain kind of luck. The kind of luck where, at just the right time in your life, just the right person says to you, “Hey, have you ever heard of synchronized diving?” Or, “I think you’d be amazing at the canoe slalom.” Or, “Come on, try out for curling!”

This week, I got to wondering how important this kind of luck is in the making of an Olympic athlete. Are we missing out on the next great skateboarder because no one ever handed them a skateboard? That one might be impossible to figure out, but there are some life circumstances we can measure — the weather, for example. Are we missing out on the next great snowboarder because it never snows where they live?

To investigate, I decided to compare the average home climates of Olympic athletes in winter and summer sports. The chart below shows every medalist from every modern Olympics, 1896–2014, plotted according to their country’s average temperature in winter and in summer.^[1] Be warned that over 27,000 people have gone home with a medal over the years, so the chart and its tooltips may take a minute to load!

As usual, there are a few caveats you should take into account when looking at this data. First, seasons and temperatures can vary widely within a country, so nationwide averages don't match everyone's personal reality. Alaskans actually live in a much colder climate than they're getting credit for in this chart. Second, these national averages are based on land area and don't even reflect the general population distribution. For example, most Canadians live clustered in parts of the country that are warmer than this chart reflects. And finally, athletes are plotted here according to the country whose team they competed for, which isn't necessarily their own country of birth or residence.

But still, the trend is clear! Compared with the other group, winter athletes come from colder winters (-8.5° versus -1.5°) and summer athletes come from hotter summers (19.5° versus 16.8°). In fact, no country warmer than Australia has ever produced a winter medalist at all — including powerhouses like Brazil with 334 medalists in the summer events. On a broad level like this one, athletes' home climates clearly have a role in steering them towards their particular discipline. After all, you can't snowboard without snow.

Tips for creating this chart

These temperature averages were calculated at the country level — so if I had plotted them as-is, every athlete from the same country would have been right on top of each other. I needed to add some jitter, and I wanted to use that jitter to represent another interesting variable: the total number of medalists produced by each country.

I wrote a little function in R that jitters points to a uniform distribution within some ellipse. In this chart, the radii of the ellipses are proportionate to the square root of the number of medalists for each country. This means that the area of each ellipse is proportionate to the actual number of medalists.

Do you play any unusual sports? Or enjoy going swimming in the dead of winter? Leave a comment below or write me at rose@datawrapper.de. And get excited for next week and the first-ever Weekly Chart from our support engineer Margaux!

1. I calculated each country's average temperature in all twelve months of the year, using measurements from 1900-2010. Since different parts of the world have different seasonal patterns, I took each country's month with the coldest average temperature — whether that's January or July — to be its winter, and the hottest month to be its summer. ↩︎

The post An update from Firefox appeared first on The Mozilla Blog.

I’m shocked. Shocked, I say.

Though we’ve seen leaked images of Apple’s cancelled AirPower charging pad in the past, this is likely our best look at the fabled charging accessory yet.

In an interview with The Verge, Giulio Zompetti, a 28-year-old from Italy, explained that he purchased a prototype version of the AirPower from a “Chinese e-waste source.” The charger doesn’t feature a cover, with its several coils entirely exposed, but Zompetti says it’s weighty thanks to its heavy stainless steel build.

In the images Zompetti supplied to The Verge, you can see that the prototype AirPower features 22 coils on the front and 22 controller circuits on the rear. He goes on to say that this prototype requires an iPhone with specific hardware (which he also purchased) for its charging coils to work, but that he was eventually able to get it to work properly.

Zompetti says he’s been collecting rare tech devices since March 2018, including a prototype of the first Apple Watch.

AirPower pic.twitter.com/bv8gi0NiiL

— Giulio Zompetti (@1nsane_dev) August 5, 2021

For context, Apple officially cancelled the AirPower back in March 2019 after revealing it alongside the iPhone X in 2017, stating that it experienced difficulties getting the charger to meet its high standards. Since its cancellation, companies like Nomad have picked up the mantle of charge-any-device-anywhere with the Base Station Pro and its FreePower technology.

According to Bloomberg’s Mark Gurman, Apple is reportedly still looking into an AirPower-like charging device.

Source: The Verge, @1nane_dev 

The post Working prototype of Apple’s cancelled AirPower wireless charger emerges appeared first on MobileSyrup.

I’ve long been enamored of the sparkline, a graphical device which its inventor Edward Tufte defines thusly:

A sparkline is a small intense, simple, word-sized graphic with typographic resolution. Sparklines mean that graphics are no longer cartoonish special occasions with captions and boxes, but rather sparkline graphics can be everywhere a word or number can be: embedded in a sentence, table, headline, map, spreadsheet, graphic.

Nowadays you can create sparklines in many tools including Excel and Google Sheets, both of which can use the technique to pack a summary of a series of numbers into a single cell. By stacking such cells vertically you can create views that compress vast amounts of information.

In A virtuous cycle for analytics I noted that we often use Metabase to display tables and charts based on extracts from our Postgres warehouse. I really wanted to use sparklines to summarize views of activity over time, but that isn’t yet an option in Metabase.

When Metabase is connected to Postgres, though, you can write Metabase questions that can not only call built-in Postgres functions but can also call user-defined functions. Can such a function accept an array of numbers and return a sparkline for display in the Metabase table viewer? Yes, if you use Unicode characters to represent the variable-height bars of a sparkline.

There’s a page at rosettacode.org devoted to Unicode sparklines based on this sequence of eight characters:

Notice that 2581, 2582, and 2588 are narrower than the rest. I’ll come back to that at the end.

If you combine them into a string of eight characters you get this result:

▁▂▃▄▅▆▇█

Notice that the fourth and eight characters in the sequence drop below the baseline. I’ll come back to that at the end too.

These characters can be used to define eight buckets into which numbers in a series can be quantized. Here are some examples from the rosettacode.org page:

“1 2 3 4 5 6 7 8 7 6 5 4 3 2 1” -> ▁▂▃▄▅▆▇█▇▆▅▄▃▂▁
“1.5, 0.5 3.5, 2.5 5.5, 4.5 7.5, 6.5” -> ▂▁▄▃▆▅█▇
“0, 1, 19, 20” -> ▁▁██
“0, 999, 4000, 4999, 7000, 7999” -> ▁▁▅▅██

To write a Postgres function that would do this, I started with the Python example from rosettacode.org:

bar = '▁▂▃▄▅▆▇█'
barcount = len(bar)
 
def sparkline(numbers):
    mn, mx = min(numbers), max(numbers)
    extent = mx - mn
    sparkline = ''.join(bar[min([barcount - 1,
                                 int((n - mn) / extent * barcount)])]
                        for n in numbers)
    return mn, mx, sparkline

While testing it I happened to try an unchanging sequence, [3, 3, 3, 3], which fails with a divide-by-zero error. In order to address that, and to unpack the algorithm a bit for readability, I arrived at this Postgres function:

create function sparkline(numbers bigint[]) returns text as $$

    def bar_index(num, _min, barcount, extent):
        index = min([barcount - 1, int( (num - _min) / extent * bar_count)])
        return index

    bars = '\u2581\u2582\u2583\u2584\u2585\u2586\u2587\u2588'
    _min, _max = min(numbers), max(numbers)
    extent = _max - _min 

    if extent == 0:  # avoid divide by zero if all numbers are equal
        extent = 1

    bar_count = len(bars)
    sparkline = ''
    for num in numbers:
        index = bar_index(num, _min, bar_count, extent)
        sparkline = sparkline + bars[index]

    return sparkline

$$ language plpython3u;

Here’s a psql invocation of the function:

analytics=# select sparkline(array[1, 2, 3, 4, 5, 6, 7, 8, 7, 6, 5, 4, 3, 2, 1]);
    sparkline
-----------------
▁▂▃▄▅▆▇█▇▆▅▄▃▂▁
(1 row)

And here’s an example based on actual data:

Each row represents a university course in which students and teachers are annotating the course readings. Each bar represents a week’s worth of activity. Their heights are not comparable from row to row; some courses do a lot of annotating and some not so much; each sparkline reports relative variation from week to week; the sum and weekly max columns report absolute numbers.

This visualization makes it easy to see that annotation was occasional in some courses and continuous in others. And when you scroll, the temporal axis comes alive; try scrolling this view to see what I mean.

We use the same mechanism at three different scales. One set of sparklines reports daily activity for students in courses; another rolls those up to weekly activity for courses at a school; still another rolls all those up to weekly activity for each school in the system.

At the level of individual courses, the per-student sparkline views can show patterns of interaction. In the left example here, vertical bands of activity reflect students annotating for particular assignments. In the right example there may be a trace of such temporal alignment but activity is less synchronized and more continuous.

When we’re working in Metabase we can use its handy mini bar charts to contextualize the row-wise sums.

The sparkline-like mini bar chart shows a row’s sum relative to the max for the column. Here we can see that a course with 3,758 notes has about 1/4 the number of notes as the most note-heavy course at the school.

Because these Unicode sparklines are just strings of text in columns of SQL or HTML tables, they can participate in sort operations. In our case we can sort on all columns including ones not shown here: instructor name, course start date, number of students. But the default is to sort by the sparkline column which, because it encodes time, orders courses by the start of annotation activity.

The visual effect is admittedly crude, but it’s a good way to show certain kinds of variation. And it’s nicely portable. A Unicode sparkline looks the same in a psql console, an HTML table, or a tweet. The function will work in any database that can run it, using Python or another of the languages demoed at rosettacode.org. For example, I revisited the Workbench workflow described in A beautiful power tool to scrape, clean, and combine data and added a tab for Lake levels.

When I did that, though, the effect was even cruder than what I’ve been seeing in my own work.

In our scenarios, with longer strings of characters, the differences average out and things align pretty well; the below-the-baseline effect has been annoying but not a deal breaker. But the width variation in this example does feel like a deal breaker.

What if we omit the problematic characters U+2581 (too narrow) and U+2584/U+2588 (below baseline and too narrow)?

There are only 5 buckets into which to quantize numbers, and their heights aren’t evenly distributed. But for the intended purpose — to show patterns of variation — I think it’s sufficient in this case. I tried swapping the 5-bucket method into the function that creates sparklines for our dashboards but I don’t think I’ll switch. The loss of vertical resolution makes our longer sparklines less useful, and the width variation is barely noticeable.

Unicode evolves, of course, so maybe there will someday be a sequence of characters that’s friendlier to sparklines. Maybe there already is? If so please let me know, I’d love to use it.

—
1 https://blog.jonudell.net/2021/07/21/a-virtuous-cycle-for-analytics/
2 https://blog.jonudell.net/2021/07/24/pl-pgsql-versus-pl-python-heres-why-im-using-both-to-write-postgres-functions/
3 https://blog.jonudell.net/2021/07/27/working-with-postgres-types/
4 https://blog.jonudell.net/2021/08/05/the-tao-of-unicode-sparklines/
5 https://blog.jonudell.net/2021/08/13/pl-python-metaprogramming/
6 https://blog.jonudell.net/2021/08/15/postgres-and-json-finding-document-hotspots-part-1/
7 https://blog.jonudell.net/2021/08/19/postgres-set-returning-functions-that-self-memoize-as-materialized-views/
8 https://blog.jonudell.net/2021/08/21/postgres-functional-style/
9 https://blog.jonudell.net/2021/08/26/working-in-a-hybrid-metabase-postgres-code-base/
10 https://blog.jonudell.net/2021/08/28/working-with-interdependent-postgres-functions-and-materialized-views/
11 https://blog.jonudell.net/2021/09/05/metabase-as-a-lightweight-app-server/
12 https://blog.jonudell.net/2021/09/07/the-postgres-repl/