Merge master/detail results from RTILA with Miller

Let’s imagine you scrape a project on several steps, and get a CSV with the link to the detail pages and some extra fields of information on each step.

In this example, I went to a directory site and from the main page, I scraped some States and link to the inner pages.

Below you can see the results table. Property 1 contains the URL for the next step of the scraping.

Then I grab the results from Property 1 and input them again on RTILA.

On the new detail page, I will grab more links.

Let’s grab the pagination here.

After configuring the next project, I got this resulting information.

Where is the problem?

On the first step, we had the field State, and it does not appear on the second table.

After some bridges, only the very last one will be able to grab information, but the parent fields will be lost on those intermediate tables.

There is an excellent tool, Miller/mlr, that allows us to merge CSV files using one common key.

The key will be the URL, in our sample, Property 1 from the first table matches with URL2 of the second table.

Go to GitHub and grab Miller:

https://github.com/johnkerl/miller/releases

You will need 2 files, mlr.exe, and msys-2.0.dll.

Export your project results or grab my tables to follow this example.

Copy all the stuff together, right-click, and open your terminal.

Don’t worry, you will not get your hacker certificate today, it’s easy.

Let’s combine them now.

Paste this on your console.

mlr --icsv --csv join -u -j "Property 1" -r "URL2" -f 1.csv 2.csv > combined.csv

It will match the parent key (Property 1 from the first table) with the child (URL2 from the second table).

As easy as that.

And the fields from the first table will be preserved.