Advice on reconciling discordant data

Question

Let me support my question with a quick scenario.

We're writing an app for family meal planning. We'll produce daily plans with a target calorie goal and meals to achieve it for our nuclear family. Our calorie goal will be calculated for each person from their attributes (gender, age, weight, activity level).

The weight attribute is the simplest example here.

When Dad (the fascist nerd who is inflicting this on his family) first uses the application he throws approximate values into it for Daughter. He thinks she is 5'2" (157 cm) and 125 lbs (56kg). The next day Mom sits down to generate the menu and looks back over what the bumbling Dad did, quietly fumes that he can never recall anything about the family, and says the value is really 118 lbs!

This is the first introduction of the discord. It seems, in this scenario, Mom is probably more correct that Dad. Though both are only an approximation of the actual value.

The next day the dear Daughter decides to use the program and sees her weight listed. With the vanity only a teenager could muster she changes the weight to 110 lbs. Later that day the Mom returns home from a doctor's visit the Daughter needed and decides that it would be a good idea to update her Daughter's weight in the program. Hooray, another value, this time 117 lbs.

Now how do you reconcile these data points? Measurement error, confidence in parties, bias, and more all confound the data. In some idealized world we'd have a weight authority of some nature providing the one and only truth. How about in our world though?

And the icing on the cake is that this single data point changes over time.

How have you guys solved or managed this conflict?

Answer 1

View your data in the same way stock market prices are commonly viewed; as a linear combination of a smooth function (your actual data) and random fluctuations. Compute the rolling average of your data with an appropriate number of historic samples, and assume this to be the data you actually want.

Given both the actual data y and the average y', you can regard the fluctuations y-y' as a stochastic process. This process has properties such as a standard deviation and variance, giving you useful information about the data in terms of its accuracy. This information could be used to discard some data points (those for which the fluctuation is larger than the standard deviation, for example), allowing you to recalculate the moving average from more accurate data or even using the data directly, but this may not necessarily be a good idea.

In this process, determining a good number of samples to use in the rolling average is the most difficult problem. It depends very much on the frequency and regularity of your data, but also on wether you expect your actual data to fluctuate much. For human weight with samples every day the number of samples can likey be fairly large (14 perhaps). There are different weighted variants of the rolling average that could be used as well.

Answer 2

There are two ways that this data can change, as I see it: Corrections (where the wrong value was entered) and natural variation over time (because people's weight changes). So you may not want to record weight as a single value, but maybe record it as weight_measure_at_point_in_time which has a value for weight, and another for the date it was recorded. That way you know the measurement changes, and you know when. When you do calculations with this value, you could choose to look at only the most recent measure, and compare similar calculations to earlier measurements.

If you want to start auditing the table, you can do that too by setting up audit fields that record who changed the measured value, when they did it, and why (and also store all records for the measurement so you have a history of all edits). If you want to calculate weights on the changed measurement based on who did it, you have to have some way of deciding whose changes are more authoritative and how much weight those changes carry (and I would not want to be around when you have that discussion with everyone, about how much or how little their opinion matters). That can be done too, you could use all weights for different versions of a measurement at a single point in time to calculate a "final" measurement, but that makes it all a bit more complicated.