3
$\begingroup$

A CSV file is characterized by a header, describing the n datarows to come. The header is a text string separated by a separator. A CSV header might look like (C1)

Date;Time;ZIP-Code;Address;Temperature

Assume we have a second CSV file (C2) with this structure:

Date;ZIP-Code;Address;Temperature

C1 and C2 are similar, but they are not the same: C2 lacks "Time"

Assume another C3:

Time;Date;Address;ZIP-Code;Temperature

Here the same items as in C1 are present, but the order is different.

What I am after is a metric which would give me the similarity between two sets, including the relative closeness of items with these two sets. In other words, if the order of items varies within one set, but the cardinality is the same, the similarity value should be bigger compared to different cardinality or if the cardinality is the same but the items itself vary.

I came up with this preliminary mental measure. I could plot in a matrix

             Date;Time;ZIP-Code;Address;Temperature
Date           1 
Time                1
ZIP-Code                    1
Address                            1
Temperature                                 1


             Date;Time;ZIP-Code;Address;Temperature
Time           0    1     0         0       0
ZIP-Code       0    0     1         0       0
Address        0    0     0         1       0
Temperature    0    0     0         0       1


             Date;Time;ZIP-Code;Address;Temperature
Temperature    0    0      0       0        1
Address        0    0      0       1        0
ZIP-Code       0    0      1       0        0
Time           0    1      0       0        0
Date           1    0      0       0        0



             Date;Time;ZIP-Code;Address;Temperature
Head1           0   0       0       0       0
Head2           0   0       0       0       0
Head3           0   0       0       0       0
Head4           0   0       0       0       0

My sense of similarity would be "the more structure / pattern in the matrix", the more similar the tow CSV headers are.

I wonder if there is a measure like Dice-Distance, Cosine-Similarity, Jaccard-Index which would be of help?

$\endgroup$
4
  • $\begingroup$ None of these examples appear to be a classical Comma-Separated Value file as I generally think of them. They are similar to such files, but most adhere to the rules that everything is separated by an explicit separating character. No CSV parser that I know of will properly parse these examples. $\endgroup$
    – Bill Barth
    Mar 23 '16 at 16:34
  • $\begingroup$ I think you missunderstood my question. I am only talking about measuring the similarity of the CSV headers. The matrix above is my take on determining similarity between two CSV headers by determining matching header descriptions. These are not CSV files. Actually I am not interested in matching files but only headers. $\endgroup$
    – JohnDoe
    Mar 23 '16 at 20:28
  • 1
    $\begingroup$ What would be the use case here? Unless one is dealing with hundreds of CSV files (or a CSV file with hundreds of columns), wouldn't it be easier to just visually inspect the headers? $\endgroup$
    – user1336
    Mar 23 '16 at 21:00
  • $\begingroup$ @user1336 calculating automatic quality metric concerning the adherence to the metric of structural consistency on open data portals like data.gov.uk, data.gov, many others $\endgroup$
    – JohnDoe
    Mar 24 '16 at 7:31
1
$\begingroup$

Useful inclusion measures depend on whether you want to check pairwise similiarity or containment. For similiarity the Jaccard index is intuitive. For containment I would adapt it to what ratio of columns of my 'gold standard file' are in the file to check which is a common feature used in natural language problems.

For the order of columns in a file, an idea is to find the overlapping sequences of columns. Assume both files have the exactly same columns but they are reordered. You can find overlapping sections, and then count the sum of overlaps (length of the section -1, because a overlap of 1 is no overlap) and normalise to the maximum possible. See example below.

File 1: A | B | C | D | E
File 2: D | E | C | A | B
overlapping sections: (D, E), (C), (A, B)
normalisation: sum of overlaps / max possible overlap
result: ( 1 + 0 + 1 ) / 4 = 0.5

A combination (sum, product?) of these two will cover some patterns in your matrices, but fail at other patterns a human would identify, i.e. having all columns in reverse order leads to 0 for the second metric.

Another problem would be inconsistent column names. If the string names don't exactly match, everything above breaks down. In that case, you might want to look into matching functions (matching dependencies) for data cleaning before.

$\endgroup$
1
  • $\begingroup$ Thank you, your comments was inspiring. While I will not accept is as an answer (there are simply to many takes on how to do it right in my opinion), your thoughts helped me to implement a comparison tool which lives here: github.com/the42/setsim $\endgroup$
    – JohnDoe
    Mar 30 '16 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.