Cannot always reconstruct denominator and group_by parameters

[iaindillingham]

This issue was reported by @ccunningham101. Thanks, Christine!

deciles-charts assumes the columns in a measure table are: [group_by, ]numerator, denominator, value, date. This assumption allows deciles-charts to reconstruct the denominator and group_by parameters passed to Measure.¹ However, it doesn't always hold.

In the following example, the assumption holds because cohort-extractor drops duplicate columns from a measure table. group_by columns are dropped before the denominator column; and the denominator column is dropped before the numerator column.

# Observed column order: depression, population, value, date
# i.e. numerator, denominator, value, date
m = Measure(
    id="...",
    numerator="depression",
    denominator="population",
    group_by=["population"],
)

However, in the following example, the assumption doesn't hold. Why?

# Observed column order: depression, autism, population, value, date
# i.e. numerator, denominator, group_by, value, date
m = Measure(
    id="...",
    numerator="depression",
    denominator="autism",
    group_by=["population"],
)

I think this is because although group_by=["population'] should be the same as group_by=None, it isn't. (Note that group_by=["population"] and group_by="population" are equivalent.)

Footnotes

1. deciles-charts doesn't use the group_by parameter, so _get_group_by should be removed. ↩

[iaindillingham]

I think deciles-charts should probably assume the columns in a measure table are

if group_by == "population":
    numerator, denominator, group_by, value, date
else:
    [group_by, ...]numerator, denominator, value, date

but it's not that simple. Why?

To create a measure file, a path to an input file (e.g. input_2021-01-01.csv) and a list of measures (i.e. instances of Measure) are passed to cohortextractor._load_dataframe_for_measures. The set of numerator values and denominator values, and the set of group by values, is calculated. If either set contains the value "population", then this value is discarded. Finally, a dictionary is created: first, the group by values are added as keys; then the numerator and denominator values are added as keys.

Only the keys from the dictionary are read as columns from the input file into a data frame. Consequently, the columns in the data frame will be the (remaining) group by columns, the numerator columns, and the denominator columns. A column with the name "population" is appended, as the final column. This is the order we observe above, in the second example. (The value and date columns are added later.)

Just one more thing - it's not that simple. Dictionaries have kept insertion order since Python 3.7. However, the usecols argument to pandas.read_csv (for Pandas v1.4.1, which it required by cohort-extractor v1.65.0) ignores element order. The behaviour of the columns argument to pandas.read_feather is ambiguous; but the behaviour of the columns argument to pandas.read_stata is to match element order. So, it's not a given that the columns in a measure table are as above.

[iaindillingham]

This is the gift that keeps giving, isn't it? 😆 What if group_by=["population", "another_column"]? Would the columns be another_column, numerator, denominator, population, value, date? Seems as though reliably determining the denominator column is troublesome.

[iaindillingham]

but it's not that simple.

It's not that simple; but for different reasons. We can ignore how an input file is read. What's important is which columns are dropped and how rows are grouped. In all cases:

-1 is date, which is added by _combine_csv_files_with_dates
-2 is value, which is added by Measure._calculate_results

I'm unsure whether it's possible to determine the denominator column reliably: that is, to not mistake it for another column. However, as @ccunningham101 has pointed out, we don't need to: instead, we need to drop rows where the value of the value column is inf. I thought that cohort-extractor replaced inf with 0, but it doesn't. As Pandas reads the string "inf" into numpy.inf, a mask should suffice.

[iaindillingham]

I think that dropping rows where the value of the value column is inf is the optimal solution. @ccunningham101 asked whether behaviour against real data was any different to behaviour against dummy data: I don't think it is.

To recap: our aim is to drop rows where the value of the denominator column is zero. Reliably determining the denominator column is troublesome. Whilst we could assume the columns in a measure table are

if group_by == "population":
    numerator, denominator, group_by, value, date
else:
    [group_by, ...]numerator, denominator, value, date

this assumption wouldn't hold in all cases. However, if the value of the denominator column is zero, then the value of the value column will be inf. We can reliably determine the value column, so dropping rows where the value of the value column is inf achieves our aim.

Behaviour against real data isn't any different to behaviour against dummy data: at least, there's nothing in _generate_measures or _combine_csv_files_with_dates to suggest that the paths are different in the presence of inf values, and it's trivial to generate inf values using dummy data.