Musings of a dad with too much time on his hands and not enough to do. Wait. Reverse that.

Page 2 of 23

Scanning slides, Part 2

Believe it or not, you used to be able to walk right up to the White House

A while back, I wrote about some PowerShell tricks I use as I scan the thousands of slides my dad has amassed over the last five decades. I did leave one small trick out, though, that I wish to share now.

As I scan old photos, I do my best to document every detail I can about the picture: when it was taken, where, who are the people in the photo, etc. One of these days, I’ll figure out a more robust way to store these details, but for now, I write them to text files that I keep in the very folders of the images they describe.

When I first began genealogy back in the 1990s, a lot of the software I worked with professionally used INI configuration files in which a section would begin left-aligned in the file and all other lines for the section would be tab-indented underneath. This is the format I adopted back then and, for consistency sake, have continued with ever since:

Example of my current image documentation file

So, you might be asking yourself, “self, what does any of this have to do with PowerShell?” Well, as I scan a set of slides, I’ll house them in their own folder. Then, I’ll run PowerShell like the following to quickly generate a readme/documentation text file to describe the images:

# generate a readme file for the directory
$dir = "C:\my_path\slides\grp_007"
$desc_line1 = "Slide appears to be dated "
$desc_line2 = "Slide is number #.  Photographer was likely John Jones. Slide is labeled 'Kodachrome II Transparency'. Slide was part of a metal container labeled magazine number '2'. Handwritten label on slide case reads, "
gci $dir | where {$_.Extension.ToLower() -eq ".jpg"} | foreach{"{0}`r`n`t{1}`r`n`t{2}`r`n" -f $_.Name, $desc_line1, $desc_line2} | Out-File ("{0}\readme_grp007.txt" -f $dir)

This code will quickly scaffold out my documentation file and save me a lot of typing. Typically, each slide has some sort of handwritten label that I’ll also want to capture in the readme file, so I’ll still have to go through each slide and type out the label corresponding to the image, but most of the slides share many of the same properties and being able to capture all those common properties at once is a great time saver.

Reading HTML into Dataframes, Part 2

In a previous post, I provided a simple example of using pandas to read tables from a static HTML file you have on disk. This is certainly valid for some use cases. However, if you’re like me, you’ll have other use cases where you’ll want to read tables live from the Internet. Here are some steps for doing that.

Step 1: Select an appropriate “web scraping” package

My go-to Python package for reading files from the Internet is requests. Indeed, I started this example with requests, but quickly found it wouldn’t work with the particular page I wanted to read. Some pages on the internet already contain their data pre-loaded in the HTML. Requests will work great for such pages. Increasingly, though, web developers are using Javascript to load data on their pages. Unfortunately, requests isn’t savvy enough to pick up data loaded with Javascript. So, I had to turn to a slightly more sophisticated approach. Selenium proved to be the solution I needed.

To get Selenium to work for me, I had to perform two operations:

  1. pip/conda install the selenium package
  2. download Mozilla’s gecko driver to my hard drive

Step 2: Import the packages I need

Obviously, you’ll need to import the selenium package, but I also import an Options library and Python’s time package for reasons I’ll explain later:

from selenium import webdriver
from selenium.webdriver.firefox.options import Options
import time

Step 3: Set up some Options

This is…optional (pun completely intended)…but something I like to do for more aesthetic reasons. By default, when you run selenium, a new instance of your browser will launch and run all the commands you programmatically issue to it. This can be very helpful debugging your code, but can also get annoying after a while, so I suppress the launch of the browser window with the Options library:

options = Options()
options.headless = True  # stop the browser from popping up

Step 4: Retrieve your page

Next, instantiate a selenium driver and retrieve the page with the data you want to process. Note that I pass the file path of the gecko driver I downloaded to selenium’s driver:

driver = webdriver.Firefox(options=options, executable_path="C:\geckodriver-v0.24.0-win64\geckodriver.exe")

Step 5: Take a nap

The website you’re scraping might take a few seconds to load the data you want, so you might need to slow down your code a little while the page loads. Selenium includes a variety of techniques to wait for the page to load. For me, I’ll just go the easy route and make my program sleep for five seconds:

time.sleep(5)  # wait 5 seconds for the page to load the data

Step 6: Pipe your table data into a dataframe

Now we get to the good part: having pandas create a dataframe from the data on the web page. As I explained in Part 1, the data you want must be loaded in a table node on the page you’re scraping. Sometimes pages load data in div tags and the like and use CSS to make it look like the data are in a table, so make sure you view the source of the web page and verify that the data is contained in a table node.

Initially in my example, I tried to pass the entire HTML to the read_html function, but the function was unable to find the tables. I suspect the tables may be too deeply nested in the HTML for pandas to find, but I don’t know for sure. So, I used other features of selenium to find the table elements I wanted and passed that HTML into the read_html function. There are several tables on this page that I’ll probably want to process, so I’ll probably have to write a loop to grab them all. This code only shows me grabbing the first table:

df_total_assets = pd.read_html(driver.find_element_by_tag_name("table").get_attribute('outerHTML'))[0]

Step 7: Keep things neat and tidy

A good coder cleans up his resources when he’s done, so make sure you close your selenium driver once you’ve populated your dataframe:


Again, the data you’ve scraped into the dataframe may not be in quite the shape you want it to be, but that’s easily remedied with clever pandas coding. The point is that you’ve saved much time piping this data from its web page directly into your dataframe. To see my full example, check out my code here.

Reading HTML into Dataframes, Part 1

Recently, I asked a co-worker for a list of data on which I needed to work. Instead of sending me his spreadsheet as an email attachment, he pasted his spreadsheet directly into the body of an email. How in the world am I supposed to work with that? Pandas can help!

I saved his email out to disk as an HTML file. Outlook converted his pasted spreadsheet into a HTML table. Then, I just used Pandas’ read_html function to read the HTML file. It automatically found the table and converted it into a dataframe for me. Problem solved!

Step 1: Save your file as an HTML file

If the data you want to process is in a table in the body of an email, about your only option is to save that email to disk as an HTML file. Save the email, then I’d recommending opening the file in a text editor like Notepad++ and making sure the data you want to process was saved within a table element. In my example here, I simply grabbed three tables of data from the Internet and pasted them all into a single HTML file.

Step 2: Import pandas

import pandas as pd

Step 3: Read in your HTML file

Note that the read_html function returns a list of dataframes:

list_of_dfs = pd.read_html('multiple_tables.html')

Now, with your list of dataframes, you can iterate over it, find the dataframe of the data you want to work with, and have at it.

for df in list_of_dfs:

Your data might not be in quite the shape you want, but pandas has lots of ways to shape a dataframe to your particular specifications. The important point is that pandas was able to read in your data in seconds versus the time it would have taken to transform the data into a CSV or some other arrangement for parsing.

« Older posts Newer posts »

© 2019

Theme by Anders NorenUp ↑