Parsing complex text files using regular expressions and vectorization

When text data is in a nice CSV format, read.csv is enough to parse it into a useable format. But if this is not the case, getting the data into a useable format is not so straightforward. In this post I particularly illustrate the use of regular expressions for complex and flexible text processing, and the power of vectorization in R. Vectorization means that we operate on vectors as a whole, not operate on individual elements of a vector.

Take for example a snippet of this data which I downloaded from StackOverflow:

The entire data file can be downloaded here.

In this post I’ll be stepping through the R code needed to get this text data into a useable format. First, we want to read the data into a character vector:

where each element of the vector is a line in the the text file. Already we see that the first line is some header information which we want to skip:

note the use of negative indexing to remove an element. Next we want to find all the elements in the vector that relate to the date for which the data is representative, we do that by using a regular expression which looks for lines that start with -:

and find the amount of actions, upvotes or downvotes etc, that have taken place on each day, i.e. the index of a certain day minus the index of the day before that:

note that we add rep_date_entries[1] because diff cuts off the first element. Now that we know which elements relate to the date, we can read all other lines into a nice data.frame:

The reputation column has a somewhat strange format ((10)), we need to get rid of the brackets. A nice way of doing that is using a regular expression, and the str_extract function from the stringr package:

The regular expression [0-9]+ matches one or more numbers, and str_extract gets the number out of the string. Now we have the data, we need to add a column which says for each row to which date it belongs. We know which lines in the data belong to a date (rep_date_entries) and we know how much data entries there are per day (actions_per_day). We can now simply repeat each element in rep_date_entries as many times as there are actions:

You can see that the date is not yet in a nice format, we need to get rid of all the text, except the date itself. Again, we can use a regular expression, combined with str_extract for this:

The regular expression "[0-9]{4}-[0-9]{2}-[0-9]{2}" matches any occurence of 4 numbers-2 numbers-2 numbers. Finally, we transform the date from a string to a real date object using strptime:

The end result is the following data.frame:

All this code together leads to the following function:

I think this nicely illustrates the power of both vectorization, very short and to-the-point for-loop-less syntax, and regular expressions in editing strings.

Tagged with: , ,
Posted in R stuff

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see