The Distant Librarian

Preparing a dataset to use in Hydrator

Every few months I get an email from someone looking for some help with the Hydrator tool I wrote about back in 2017. Hydrator is a simple tool to rehydrate twitter ids into full tweets. From the Hydrator GitHub page, "Twitter's Terms of Service do not allow the full JSON for datasets of tweets to be distributed to third parties. However they do allow datasets of tweet IDs to be shared. Hydrator helps you turn these tweet IDs back into JSON and also CSV from the comfort of your desktop."

The advantage for me, and apparently many others, is that it's an actual program instead of a command-line tool, so it's pretty damn easy to use for those of us who only occasionally need to rehydrate a list of tweeter ids.

The biggest issue people have is that Hydrator only wants a file with a single list of twitter ids, and nothing else. Not even a header on that list. For a reasonably-sized list it's usually no problem to use Excel or similar to clean up the offending dataset, but the other day someone asked if I could help with prepping the file at A Twitter Dataset of 150+ million tweets related to COVID-19 for open research. For some reason the researchers added two columns to the dataset, so in addition to the column of twitter ids, there are also columns for date and time.

Note this is all happening on my MacBook. YMMV on other platforms.

I did some Googling and decided that csvkit should do the trick and it did, with a couple of caveats. First, it's an incredibly simple command-line tool, with a stellar step-by-step tutorial. Trust me, you can use it! So I installed it and ran the first command to turn the COVID-19 dataset from a TSV (tab-separated values) file into a CSV (comma-separated values) file:

in2csv full_dataset-clean.tsv > full_dataset-clean.csv

And it didn't work :-( csvkit is supposed to be able to recognize and parse tsv, but for whatever reason it didn't in this case. No biggie, back to Google, and after a little trial and error, I settled upon this single command line to convert the file from TSV to CSV:

cat full_dataset-clean.tsv | tr "\\t" "," > full_dataset-clean.csv 

And just below that is a suggestion that I might not've even needed csvkit for the next step, but I didn't see that until just now :-)

So that command did indeed translate my TSV file into a CSV file, and I went back to the csvkit tutorial to get a file with only the first column, using the command:

csvcut -c 1 full_dataset-clean.csv > full_dataset-clean-tweet_ids.csv

One last problem is that there's still a header on that column (tweet_id), which I verified with the command:

csvcut -n full_dataset-clean-tweet_ids.csv

Google suggests the SED (stream editor) command will do the trick:

sed -i -e '1d' full_dataset-clean-tweet_ids.csv

And I can verify the first line is now gone with:

head full_dataset-clean-tweet_ids.csv

Probably way more convoluted than it needed to be, but it got the job done, and now I can point Hydrator at my full_dataset-clean-tweet_ids.csv file and Bob's your uncle!

Note to self, xsv is another CSV tool that might be worth exploring.

Summary of CO2 Emissions by Sector. Alberta, 1988-2005.

Earlier this week I was trying to help a student find CO2 emissions data for Alberta going back to 1985, and have so far been unsuccessful. According to the Canadian Greenhouse Gas Measurement Program, measurements began in 1975, at least nationally, but I haven't been able to find the actual data following any of the links on that page. Canada's Official Greenhouse Gas Inventory includes data by Province from 1990.

Eventually I came across a report from the Alberta Department of Energy dated September 1990. Table 2 of A Discussion paper on the potential for reducing carbon dioxide emissions in Alberta : 1988 - 2005 is a summary of CO2 Emissions by Sector, so that gets us two more years back. Helpfully, colleagues at the U of Alberta have digitized that report, and I was able to lift the table into Excel pretty easily (thanks PDF Tables). Just to surface the data I have included it here, and have posted it to our institutional data repository, though technically the report is still under copyright.

One frustrating but important note is that the data from this report does not appear to match the data in the Canada Greenhouse Gas reports, so take it with a solid grain of salt. I still thought it was important enough to try to make it more widely available.

Photoshop 2020 blank screen instead of images - fixed

Just had the pleasure of upgrading to the latest version of Creative Cloud at MPOW, and while it appeared to go smoothly I immediately had a problem with my workflow. Importing an image (jpg) into Photoshop would only show a blank white screen. The image WAS correctly rendered on the home screen, but just a white workspace when attempting to actually work with the image.

I did some googling and found a similar problem reported with a fix for the Mac version, and was able to figure out where the same settings were in Windows, and it fixed my problem.

So for my future reference, and maybe yours, go to Edit, then Preferences, then choose Performance. Uncheck the box for Use Graphics Processor. This was on a Dell OptiPlex 7050 running Windows 10. Good luck!

A map of the most spoken languages in every Canadian Province or Territory besides English and/or French

I saw this post on BoingBoing earlier today: A fascinating map of the most spoken languages in every US state besides English and Spanish and wondered what an equivalent map for Canada might look like. So I made one.

Data was pulled from the 2016 Canadian Census, looking at the variable "Language spoken most often at home - Both sexes", but throwing out English and French, the two official Languages of Canada. In third place for each Province or Territory you have:

Kinda neat! If I look to fourth place, German appears in Manitoba and the Yukon, and Punjabi floats up in Alberta and BC. Otherwise, no new languages appear.

After I threw this together I thought to search to see if it had already been done and found one showing quite different results from the 2011 census.

How to Find the RSS Feed URL by Guessing

The Zapier newsletter I just got had a link to this article from last year: How to Find the RSS Feed URL for Almost Any Site. It's got some good tips for sussing out RSS feeds, but I have a couple more.

I ran across two sites recently that should've had RSS feeds, but apparently didn't, at least according to Inoreader, my feed reader of choice. Inoreader usually does a good job of automatically detecting an RSS feed if it exists, but it didn't find one for www.canadianenergycentre.ca^*, which is a WordPress site. Turns out if you simply add /feed to the end of the URL you'll be good to go. That may be true for all WordPress sites - worth a try at least. Not sure why Inoreader didn't find that, and I'm still waiting to hear back from their tech support.

The other site that's crying for a feed is The Far Side! I emailed to ask for one but never heard back, so I built one of my own using Feed43. I'm not posting it here for fear of it being removed, but you can build one yourself, or hit me up privately for the URL :-)

Long Live RSS!

^*This site doesn't get any Google juice from an actual link from me

Witchcraft and OpenRefine

Last September I learned about a fabulous resource called Places of Residence for Accused Witches.

Map of Scots women accused of witchcraft published for first time https://t.co/vOxIlv6g24 ht @ResearchBuzz Fascinating stuff! #GIS #witches #witchcraft #mapping pic.twitter.com/nOWNHiUlw4

(@ppival) September 26, 2019

At that time I didn't realize how much work had been done using OpenRefine, one of my favourite tools, but earlier this week came across a series of videos on the background of the project, and one of them was all about how Emma Carroll (Witchfinder General) used OpenRefine and WikiData in the project. Great stuff, and I love listening to her accent!

Trolling around some of the other pages related to the project, here's a link to a map showing the different methods of torture that were used. Yeesh!

Archives in the news

I've recently become more involved in a project to digitize historical maps held at the U of Calgary, so this article really spoke to me (thanks @achurrell!) Yukon geologists raid the archives to aid ongoing search for gold. And because it seems so often news reports like this don't seem to include a link to the actual resource being reported on, I'm pleased that the CBC did provide a link directly to YCGC Historic Placer Data, which is fascinating to poke around. I wonder whether our (currently small) collection of Oil and Gas maps will be used in the same way, some day?

Also recently crossing my path is a news release talking about the use of an archival collection from the Glenbow Archives, which are now held here at the U of Calgary. The Imperial Oil Files: New Collection Adds to Climate and Energy Research Archives On Science and Denial. And the collection itself: Introduction to the Imperial Oil Archival Document Collection. The U of Calgary has no affiliation with this organization, I'll add. We just hold the archives. I'm curious to know whether these images were captured on the sly, or in an open fashion. It's almost like an analogue version of scraping a website!

Available Reads is like Library Extension lite

I just added the Available Reads browser extension, and really like the feature to sort my Goodreads titles by availability on Overdrive. But for even more information on additional sites (like Amazon), Library Extension still takes the cake. Some screen shots featuring the two extensions: (click to embiggen for details)