Perform File Comparisons using esProc

You can handle simple file comparisons with the console command, Java, python and perl. But all of them are not good at performing set operations and structured computations. This will result in complicated code for multi-threaded processing and cumbersome process in comparing multiple fields, big files and the files with different structures.

As esProc (free edition is available) supports set operations, cursor operations and structured computations, it can make the tasks simpler. It is easy to use and supports independent operation, console execution, invocation by reporting tools and Java applications. More details can be found in How esProc Implements Text Processing.

The following explains how esProc handles file comparisons through examples.

Find common field values of two files

Both the f1.txt file and the f2.txt file have Name and Dept fields and use the first row as the column names. You need to find the common Name values of the two files. Below are selections from the files:

f1.txt:
           Name               Dept

           Rachel            Sales

           Ashley             R&D

           Matthew         Sales

           Alexis              Sales

           Megan            Marketing

f2.txt:
           Name               Dept

           Emily               HR

           Ashley             R&D

           Matthew         Sales

           Alexis              Sales

           Megan            Marketing

esProc script:

Import the files into the memory using import function and perform the intersection operation using isect function. Both files use the default separator tab. @t imports the first row as column names, enabling the direct use of Name and Dept to reference the corresponding fields in the subsequent computations. If the first row is not column names, use the default column names, such as _1 and _2, to reference fields.

The isect function is equal to the intersection operator “^”, so the code can be written as A1.(Name) ^ B1.(Name).

Here’s the result:

Tips:

1. You can use an intersection to find common field values. Similarly you can use a union, difference and concatenation to get the corresponding results.

To get all the names in f1.txt and f2.txt (during which duplicate values will be combined into one), use union function or the union operator “&”. Here’s the result:

To get the different field values of f1.txt and f2.txt, use diff function or the difference operator “\”. Here’s the result:

To concatenate the values of Name fields from f1.txt and f2.txt and retain the duplicate values, you can use conj function or the concatenation operator “|”. Here’s the result:

2. To compare complete records, you can use @ts options to import each file as a set of strings, as shown below:

Find the modified records in the CSV file

old.csv is the original file, and new.csv is the file resulted from adding, deleting and modifying the data of old.csv. Both files have the same logical primary key – username and date. Now you need to find the records in old.csv which have been modified.

Below is a selection of the data:

You can see that in new.csv the third and the fourth rows are the newly added and the fifth row is the modified. The third row in old.csv is deleted.

esProc script:

Import each file with the comma as the separator and sort data by the primary key. After that, get the new records first (because they have different primary keys) and store them in the variable new; then find the different records (which may have different values in any field) and store them in the variable diff; finally find the difference between diff and new, that is, the modified records (which have the same primary key but different other fields). Here’s the result:

Tips: you can find the deleted records with the code [A2,B2].merge@d(userName,date) .

Find common rows of two big files

Both file1.txt and file2.txt store a great deal of strings. You need to find their common rows (intersection). Below are selections of data:

esProc script:

The cursor function opens each file in the form of cursor, without importing data all at once. By default tab is used as the column separator in importing all fields, which will be named _1、_2、_3…_n automatically. The sortx function can sort a cursor; the merge function performs a merge on the ordered data; @x means merging the cursors and @i specifies that the merge result be the intersection. The export function can write cursor data into a file, as shown below:

Tips: The merge function uses @i to get the intersection; uses @u to get the union; and uses @d to get the difference. By default it performs the concatenation, which combines the two files according to the sequence of the strings and allows duplicates.

Compare a big file and a small file

file1.txt contains big data, but file2.txt can be loaded into the memory. You need to find the common rows of the two files.

esProc script:

Import the smaller file into the memory and create a hash index to increase the efficiency in performing the set operations. The primary function can be used to set the primary key and index can be used to create the hash index. The find function finds the common data of cursor A1 and cursor B1, i.e. the intersection.

Tips:

1. To find the difference, A3’s code can be written as =A1.select(!B1.find(~._1)) .

2. To get the union, you can first find the difference between file1 and file2 and then union the result with file2.

Improve performance of file comparisons using parallel computing

All the above examples use serial computation, yet performance can be increased by using parallel computation. You can use several threads to retrieve a file. Each thread accesses a part of the file with the cursor and performs the set operation. Finally the results of processing the cursors will be merged.

Let’s run a test with a 2.77G big file and a 39.93 small file under the same hardware environment. It takes an average of 85 seconds to complete the task using serial computation, and an average of 47 seconds using the parallel computation. The speed has almost doubled. Since the set operation is not complicated, most of the performance improvement happens during the file retrieval from the hard disk. In fact the more complicated the operation is, the more greatly the performance improves.

esProc script for parallel processing:

Divide a file into 4 segments and generate 4 cursors to perform the computations, and finally merge the resulting cursors.

It would be good not to divide the file into too many segments. Generally the number is equal to the number of threads the system defines; otherwise some segments will have to wait in queue to be processed. The number of threads can be set in the configuration files. The cursor function uses @z option to divide a file into segments and retrieves a segment using the cursor. A file will be divided roughly because being divided exactly will result in broken rows. To get each row in its completeness, esProc will automatically skip the head row and make up the tail row.

Compare files with different structures

The Data.txt, a text file separated with tabs, has 6 fields, in which here field contains strings which are delimited by semicolons each. list is a one-column file. Compare the two files, and if a subset of the split here field in a record matches any row of the List.txt, then export it to result.txt.

Below is the source data:

esProc script:

In the above script, the select function query the desired data; the array function splits a string; “^” means getting the intersection; and “[]” represents a null set.

Here’s the result:

Find strings that aren’t included in a HTML file

Find which strings of the list tagList aren’t included in the a.html file. TagList=”Marketing”,”sales”,”human resource”,”Finance”,”R&D”,”commerce“, and a.html is as follows:

esProc script:

Use read function to load in the file as a string; and then use select function to perform a conditional query. The condition is that if A2 (the large string) contains “~” (A1’s current member); finally calculate the difference. Here’s the result: