Suppose I am only working with two datasets, Data Source 1 with columns Customer ID and Install Date; and Data Source 2 with columns Customer ID and Install Date.

Now suppose each data source has some un-observably large number of rows - so that I brute force checking if the install dates for customers in both datasets are the same is not feasible.

I could sample some number of observations from both sources and and create a binary feature which is 1 if matching and 0 if not matching.

What statistical test would I use to test that both data sources are matching? My goal is to eventually obtain a sample size (with power=.9 and alpha=.05).

My first instinct was to reach for the McNemar's test - but that doesn't feel right.

Any help is much appreciated!

  • 2
    $\begingroup$ What is your purpose? (a) Might be to see if the two databases are in general agreement. [Maybe one systematically reports earlier install dates, and you want to detect that.] (b) Might be interested in details of accuracy. [Each diff (either direction) means someone made a mistake. You want to know how common such mistakes are. ] (c) Something else entirely that I haven't thought of. $\endgroup$ – BruceET Jul 17 '19 at 19:57
  • 2
    $\begingroup$ ... My immediate impression is the your purpose will make a difference in the statistical analysis. Are these two independently recorded databases purporting to have the same info? If so, why two? What are consequences of disagreements btw databases? Can you give a couple of examples of a few Cust IDs for which dates don't agree? How much disagreement counts as a mistake? $\endgroup$ – BruceET Jul 17 '19 at 19:58
  • 1
    $\begingroup$ Thanks for the response @BruceET - purpose (a) above is the type of use case I would have. $\endgroup$ – TheCuriouslyCodingFoxah Jul 17 '19 at 20:00
  • 1
    $\begingroup$ After two 'answers' to different interpretations and extensive, inconclusive discussion, the objective remains unclear. Voting to close pending coherent modification of the question. $\endgroup$ – BruceET Jul 18 '19 at 20:29

If you find a single mismatched pair, you know the data sets are not matched. It's impossible both to find a mismatched pair and for the datasets to be perfectly matched. So any test that relies on finding any mismatched pairs will have a false alarm rate (i.e., alpha) of 0.

The power of any test depends on the true "effect". The datasets would be considered mismatched if even one pair was mismatched. If you have a massive number of pairs and only one of them is mismatched, the probability of finding a mismatched pair in any sample is so low that you won't have any power to detect it. There is no way to balance power and size of the sample without knowing the proportion of mismatched pairs, but if you knew the proportion of mismatched pairs was different from 0, then you'd already have the answer to your question.

  • 1
    $\begingroup$ IMHO this is not a helpful answer to OP's question as refined in Comment. Not downvoting because Comment may have come after you started your Answer. $\endgroup$ – BruceET Jul 17 '19 at 20:34
  • 2
    $\begingroup$ I did answer before the comment, but also I feel their refinement contradicts their post. They want to know if their datasets match exactly on date, not whether the average date of the two datasets is the same. Their question is ill-formed IMO. $\endgroup$ – Noah Jul 17 '19 at 22:18

I think you want to do paired t test on dates, with the null hypothesis that on average dates agree against the alternative hypothesis that dates differ from DB 1 to DB 2 (earlier or later).

Here is a 'power and sample size' analysis from Minitab. Lacking details from you, I assumed the SD of the difference is $\sigma = 2$ and that it is important to detect a systematic difference as large as one or two days.

Power Curve for Paired t Test 

Power and Sample Size 

Paired t Test

Testing mean paired difference = 0 (versus ≠ 0)
Calculating power for mean paired difference = differenceα 
α = 0.05  
Assumed standard deviation of paired differences = 2

            Sample  Target
Difference    Size   Power  Actual Power
         1      44     0.9      0.900031
         2      13     0.9      0.910708

enter image description here

As you can see, under these assumptions, you would need to look at about 44 pairs (customer IDs) to detect a difference of one day, and only about 13 for a difference of two days. You need to contrive a method to sample these customers at random.

You should check to see if differences are roughly normal. If there are many huge outliers (either positive or negative), then a t test may not be appropriate.

If the sample SD of differences is much different from my assumed $\sigma = 2,$ then you need to re-do the computations for sample size. (For example, if $\sigma=1,$ then respective sample sizes drop to about 13 and 5; if $\sigma=4,$ then increase to 170 and 44.)

Note: For the purpose you stated in your Comment, you should not reduce the differences to 0's and non-0's. The actual size of the difference matters.

  • 1
    $\begingroup$ Thank you @BruceET - this makes sense! $\endgroup$ – TheCuriouslyCodingFoxah Jul 17 '19 at 21:54
  • 2
    $\begingroup$ Good answer if this is what the OP wanted, though that's not so clear in the question. The null hypothesis of this test is that the average date in the two datasets is the same. The average date could be the same even if no pair of dates for a given customer ID were remotely alike. In fact, the more different the dates are in a pair, the less likely one is to reject the null hypothesis of a paired t-test, which would seem to run entirely counter to OP's intention for this test. $\endgroup$ – Noah Jul 17 '19 at 22:24
  • 1
    $\begingroup$ @Noah: Agree it's not clear what OP wants/needs. Took OP's choice of (a) in my Comment to be that matching averages is the goal. Between the two Answers, I hope something is helpful. $\endgroup$ – BruceET Jul 17 '19 at 23:09
  • 1
    $\begingroup$ @TheCuriouslyCodingFoxah OK then. Does any mismatch at all mean the two datasets are "not matched" or are they "essentially matched" if the proportion of mismatches is below some threshhold? Both can be answered, but you do need to decide what you want at some point. Meanwhile I'm voting to close this as 'unclear what you're asking'. $\endgroup$ – BruceET Jul 18 '19 at 20:26
  • 1
    $\begingroup$ In R, pbinom(0, 300, .01) returns 0.0490. Thus, if $X \sim \mathsf{Binom}(300, .01),$ then $P(X \ge 1) \approx 0.95.$ So if the probability of a mismatch is 0.01, you're fairly likely to find at least one mismatch among 300 entries. $\endgroup$ – BruceET Jul 19 '19 at 5:36

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