DEV Community: James McPherson

I know, I'll use a regex!

James McPherson — Fri, 14 Aug 2020 20:48:53 +0000

This post appeared first on my blog, at https://www.jmcpdotcom.com/blog/posts/2020-08-08-i-know-ill-use-a-regex/

This past week, a colleague asked me for help with a shell script that he had come across while investigating how we run one of our data ingestion pipelines. The shell script was designed to clean input CSV files if they had lines which didn't match a specific pattern.

Now to start with, the script was run over a directory and used a very gnarly bit of shell globbing to generate a list of files in a subdirectory. That list was then iterated over to check for a .csv extension.

Please save your eye-rolls and "but couldn't they..." for later

Once that list of files had been weeded to only contain CSVs, each of those files was catted and read line by line to see if the line matched a desired pattern - using shell regular expression parsing. If the line did not match the pattern, it was deleted. The matching lines were then written to a new file.

Again, please save your eye-rolls and "but couldn't they..." for later

The klaxons went off for my colleague when he saw the regex:

    NEW=${f%.csv}_clean.csv;
      {
      buffer=""
      read
      while IFS="" read -r line && [ -n "$line" ]
      do
            buffer="${buffer}${line}"
            if [[ "$buffer" =~ ^\"[0-9]{4}-([0][0-9]|1[0-2])-([0-2][0-9]|3[01])\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",[^,]*,\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",\"[^\"]*\",.*$ ]];
            then
                  echo "$buffer"
                  buffer=""
            else
                  buffer="${buffer} "
            fi
      done
      } < "${f}" > "${NEW}"

My eyes got whiplash. To make it easier to understand, let's put each element of the pattern on a single line:

    ^\"[0-9]{4}-([0][0-9]|1[0-2])-([0-2][0-9]|3[01])\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    [^,]*,
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    \"[^\"]*\",
    .*$

Which is really something. The first field matches a date format - "yyyy-mm-dd" (which is ok), then we have 12 fields where we care that they are enclosed in double quotes, one field that we want to not be quoted, another 12 fields which are quoted again, and any other fields we don't care about.

Wow.

I told my colleague that this wasn't a good way of doing things (he agreed).

There are better ways to achieve this, so let's walk through them.

Firstly, the shell globbing. There's a Unix command to generate a list of filesystem entries which match particular criteria. It's called find. If we want a list of files which have a 'csv' extension we do this:

    $ find DIR -type f -name \*.csv

You can use '.' or '*' or any way of representing a DIRectory in the filesystem.

Now since we want this in a list to iterate over, let's put it in a variable:

    $ CSVfiles=$( find DIR -type f -name \*.csv -o -name \*.CSV )

(You can redirect stderr to /dev/null, with 2>/dev/null inside the parens if you'd like).

Now that we've got our list, we can move to the second phase - removing lines which do not match our pattern. Let's try this first with awk. Awk has the concept of a Field Separator, and since CSV files are Comma-Separated-Value files, let's make use of that feature. We also know that we are only really interested in two fields - the first (yyyy-mm-dd) and the fourteenth.

    $ awk -F',' '$1 ~ /"[0-9]{4}-([0][0-9]|1[0-2])-([0-2][0-9]|3[01])"/ &&
        $14 !~ /".*"/ {print}' < $old > $new

That's still rather ugly but considerably easier to read. For the record, the bare ~ is awk's equals operator, and !~ is not-equals.

We could also do this with grep, but at the cost of using more of that horrible regex.

In my opinion a better method is to cons up a Python script for this validation purpose, and we don't need to use the CSV module.


    from collections import UserString
    from datetime import datetime

    infile = open("/path/to/file.csv", "rw")
    input = infile.readlines()

    linecount = len(input)

    for line in input:

        fields = line.split(",")
        togo = False

        try:
            datetime.strptime(fields[0], "%Y-%m-%d")
        except ValueError as _ve:
            togo = True

        if '"' in fields[14] or not UserString(fields[14]).isnumeric():
            togo = True
        if togo:
            del line

    if len(input) != linecount:
        # We've modified the input, so have to write out a new version, but
        # let's overwrite our input file rather than creating a new instance.
        infile.seek(0)
        infile.write("\n".join(input))

    infile.close()

This script is pretty close to how I would write it in C (could you tell?).

We first open the file (for reading and writing) and read in every line, which yields us a list. While it's not the most memory efficient way of approaching this problem, it does make processing more efficient because it's one read(), rather than one-read-per-line. We store the number of lines that we've read in for comparison at the end of our loop, and then start the processing.

Since this is a CSV file we know we can split() on the comma, and having done so, we check that we can parse the first field. We're not assigning to a variable with datetime.strptime() because we only care that we can rather than what the object's value is. The second check is to see that we cannot find the double apostrophe in the element, and that the content of the field is in fact numeric. If neither of these checks succeed, we know to delete the line from our input.

Finally, if we have in fact had to delete any lines, we rewind our file
(I was going to write pointer, but it's a File object. Told you it was close
to C!) to the start, and write out each line of input with a newline character
before closing the file.

Whenever I think about regexes, especially the ones I've written in C over the years, I think about this quote which Jeffrey Friedl wrote about a long time ago:

Some people, when confronted with a problem, think
“I know, I'll use regular expressions.”

Now they have two problems.

It was true when I first heard it some time during my first year of uni, and still true today.

A shell and GNU awk oneliner

James McPherson — Tue, 28 Jul 2020 22:56:27 +0000

I'm updating a test suite for $work, and need to handle input data with differing numbers of columns. The input is CSV files, which is fairly friendly. The number of files I've got to test my code against is extensive and I wanted to quickly figure out how many of each type I have.

Enter the one-liner:

$ for f in *csv; do awk -F, '{END print ARGV[ARGC-1], ":\t", NF}'
file1:      10
file2:      11
file3:      14
file4:      10
....
file100:     14
$

For each file in the list, when GNU awk has read it all in and parsed it using the , as a field delimiter (the END), we then print the filename (ARGV[ARGC-1]), and the number of fields NF. If I wanted to print the count of the number of records (lines) in the file, I could add NR to the arguments to print.

I hope you found this useful.

If you want to explore GNU Awk more, the documentation is at https://www.gnu.org/software/gawk/manual

A (not so) brief introduction to using the Python database API

James McPherson — Sun, 28 Jun 2020 03:55:11 +0000

I've had this post in the works for a while now, and I'm pleased to say that I've finally hit 'publish'.

Earlier this year I promised a friend at #PyladiesBne that I would write something about using databases with #Python, specifically #Oracle and #PostgreSQL. Here it is:

https://www.jmcpdotcom.com/blog/posts/2020-06-03-a-brief-introduction-to-the-python-database-api/

It's not actually brief, because I got sidetracked. You know how it is when you have this dataset and realise that you can ask more questions than you expected....

That's just an occupational hazard for a #DataEngineer!

How I started learning Apache Spark

James McPherson — Tue, 15 Oct 2019 03:26:22 +0000

I've realised over the years that the best way for me to start learning a new language, toolkit or technology is to dive right in and start trying to solve problems with it.

This is most definitely true for Apache Spark, which I had to do recently in order to prepare for a #DataScience interview.

I wrote a utility to Extract information from my 6+ years of PV Inverter data, Transform it and Load it (#ETL) into #DataFrames which I query for record dates, minimum and maximum output as well as daily average output. Keeping with my standard practice, I've put that code on GitHub, and written a blog post about the process. See more (much more!) at https://www.jmcpdotcom.com/blog/posts/2019-10-11-apache-spark-init/

Apache #Spark, #ETL, #Python

Sentiment Analysis of Australian political hashtags

James McPherson — Mon, 14 Oct 2019 22:25:11 +0000

It's time for another post from my home blog. This is another #Python #microservice, which ties together some #NaturalLanguageProcessing (using the nltk toolkit, the @Twitter search API and #SentimentAnalysis with some basic #MachineLearning. To visualise all this I used the C3.js charting library, and put together some more JavaScript for periodic (30 second) and flowing visuals.

https://www.jmcpdotcom.com/blog/posts/2019-10-04-microservices-part-2/

I'm going to return to this project shortly, to update the tweet source corpus. The source I've used for this project is based on British tweets, and while Australia and Britain share many things, I'm not completely confident that our sarcasm and cynicism is reflected in the corpus.

A microservice making electorate info more accessible

James McPherson — Fri, 04 Oct 2019 02:32:17 +0000

Over at my home blog I've written about a #Python #microservice I created to make the task of finding your Australian electorate more accessible. It covers both Federal divisions and state+territory lower house electorates. I also go into detail about the #ETL process I went through to get the data I needed.

See more (a lot more!) at A microservice for electorate maps

Flask @googlemaps #webscraping #BeautifulSoup #GeoJSON #KML