[fix]: Handle duplicates when file names include more than 4 digits

[signekb]

Description

Sometimes when the file names include six digits they are actually meaningful and represent the data at different times. @Aastedet gave the example of an address being unchanged.

Currently, we ignore the name of the source_file (and thereby the year) and deduplicate all rows with the same values except for the source_file. But Anders mentioned that this isn't always the wanted behaviour.

@Aastedet do you mind elaborating below? :)

[Aastedet]

One example could be the files that are available at finer granularity than yearly. For example, most of the sociodemographic data is updated quarterly, and the information in bef199806 and bef199812 is not a duplicate, but corresponds to the status at the end of june of 1998 vs. end of december 1998 (e.g. place of residence).

By adding the last two digits, we also make it explicit that this data, e.g. bef199812 corresponds to the status at the end of the index year, whereas most other register sources without the two trailing "month digits" include the data from the index year (e.g. akm1998 contains the computed "average" employment status of a person across the whole year of 1998). This is important for the user to know when deciding which year of bef to join to which year of akm for baseline characteristics.

[lwjohnst86]

Hmm, but both bef199806 and bef199812 have a full join so any duplicate information gets joined into a single entity for the year 1998. That's not enough or?

[Aastedet]

If I understand correctly, my concern is that

1. the user won't know where the non-duplicated rows are coming from (which value is from bef199806 and which is from bef199812)
2. since the duplicated rows are deduplicated/removed, the user won't know if a person was missing in the dataset at one point in the year (e.g. died/emigrated during the second half of 1998) or was present at both times of the year, but just had no change in their data (e.g. didn't move or get married/divorced during the second half of 1998)

[signekb]

1. Currently, I've added a source_file column that includes the name of the original file, so as far as I understand, your first concern is covered there. If duplicate rows are found in multiple source files, the row from the file that appears first in the path arg in the convert_to_parquet() function is kept.
2. This is the concern that we don't cover at the moment. At the top of my head, we could solve it in two simple ways: don't deduplicate rows and let it be up to the user which rows to keep (the data will still be more efficient to use in the parquet format instead of SAS), or keep the names of all source files in the source_file column so in the cases where it's useful to know which files the rows come from, the information can still be found there.

Thoughts?

[Aastedet]

I think not de-duplicating is a good default. It outputs files with predictable contents (for users familiar with the register data) and puts the user in charge of solving their particular quirks. Deduplicating is useful of course, but us doing it by default might lead to unexpected results for some use-cases.

[signekb]

I agree. To keep things simple for now, especially with chunking as well where we probably won't read in multiple files at once anyway, not deduplicating seems like the way to go, at least for now.

What do you think, @lwjohnst86?

[lwjohnst86]

Hmm, I don't love that idea, especially since I can imagine people immediately being confused why there are duplicates. At least, I know that's how I felt when looking at data that has duplicates. And then trying to do any sort of pivoting with duplicates is annoying/difficult, and if people using the data don't know about why we chose not to de-duplicate, I can see them becoming suspicious of the quality of the data (not to say register data is the highest quality data around 🤔).

[lwjohnst86]

But I can also see that it would be simpler to implement (maybe, I haven't dug into the details about that issue for a while). We can always start with a simple bind_rows() and do joins later if we want. 👍

[signekb]

@Aastedet suggested that we should partition by year+month whenever possible (e.g., two files from the same register named "202012" and "202003 should be in folders "year=202012" and "year=202003" instead of "2020"). @Aastedet please elaborate why 🌱

[lwjohnst86]

We shouldn't mix years and months. A year is 4 digits. If it's really necessary, we do year=YYYY/month=MM/... but I think that will confuse things, as the question becomes, what happens to the other months?

[signekb]

@lwjohnst86 Could it make sense to only add the month partition when one or more files in the register has 6 digits in the name?
Or does this lead us down a path of more complexity than we would like?

[lwjohnst86]

I think we have to think carefully about what a month partition actually means and how we might communicate that to the user. Imagining myself as an uninformed user of the parquet dataset, if I saw that i would assume it only contained data for that month. If thats the direction to go, er should split the date into year and month for all data and save it so only data for that month is included.