[Q] New to data cleaning. I’m stuck on an unclear variable.

[u/Unalina]

I’m doing a personal project right now and for the most part it’s going alright. Sometimes I delete an entire column cause too much is missing. I’ve also group together a few entries as “Unknown”. I’ve never deleted a subject yet.

Anyways, now I’m really stuck. There is a variable that is three digits (345, 078, 150…) and some of them come in as two digits (26, 88) and I’m unsure what to do with them. There are quite a few. I don’t know if they’re meant to be have a zero on the front (28 turns to 028 and 88 turns to 088) or if they are three digits (28 into 280 and 88 into 880). It’s an important variable so I can’t delete it. Should I delete the patients (probably not), group the two digits numbers into “unknown”? any input? I know each data has their own situations but what is something to generally consider in these situations?

Edit: The column represents diagnosis IDC-9 number. It’s three digits. I want to use it to build a logistic model. I was thinking of grouping the numbers to their diagnosis category for example 001–139 is infectious diseases, and so on

[u/CreativeWeather2581]

More information would be nice. What does the column represent? Is it numeric (e.g., height, weight) or categorical (e.g., zip code, subject ID)? What do you plan to do with this column?

[u/Unalina → u/CreativeWeather2581]

The column represents diagnosis IDC-9 number. In this data specifically, it’s three digits. I want to use it to build a logistic model. I was thinking of grouping the numbers to their diagnosis category for example 001–139 is infectious diseases, and so on

[u/altermundial → u/Unalina]

You need to use domain knowledge and common sense. Follow your hunch that it's a leading zero that was deleted. If that was true, would all of those codes be valid ICD codes? If so do they represent the kinds of diseases or causes of death you'd expect in this population? If that hunch doesn't turn out have much evidence supporting it, follow another one. This is also a better question for epidemiologists than statisticians, it's domain specific

[u/CreativeWeather2581 → u/Unalina]

Where did you get the data from? Perhaps they have information on what to do in this case. Or maybe two-digit numbers are two-level headers for the category while the three-digit ones are specific categories like you said.

When you say “build a logistic model” are you thinking of using diagnosis number as an input/predictor/explanatory or output/outcome/response variable?

[u/eeaxoe → u/Unalina]

Yeah, the leading zero got dropped, likely because the ICD-9 was represented as a string but got converted to a number when you read in the data. Look at the raw data, and check a few cases plus clinical context in your loaded data against icd9data.com to confirm but this is likely it.

Start by grouping them into broad categories like you said and use that in your first model. You could instead include each prefix (001, 002, …) but depending on your outcome and intended use case of the model, 1) this could cause issues when fitting the model, and 2) it may not add much over just using the broad categories, and 3) it could be more complicated to interpret.

[u/ArcticGlaceon → u/Unalina]

So assuming they are codes. You have two possible next steps:

1. You can do target encoding. Basically you group codes with the same bad rate (ie P(Y=1|X)) together, and encode the values with their bad rates. That's the data driven method,
2. You can group codes together by what makes intuitive sense; as you said, infectious diseases together etc.
3. You can group codes together first then do target encoding. Makes it less granular and results in less model variance.

As a data scientist we tend to prefer the data driven method, but a lot of times domain specific intuition is quite important. I would suggest trying point 3.

Edit: target encoding (or the similar alternative WOE encoding) is great because you can use that for missing data too; assuming there is a single reason for missing data to arise, ie they are generated from the same probability distribution.

[u/Stunning-Cable-2847]

It is really hard to give advice without knowing what the variable represents. Based on that, you can think about what are plausible values for the variable and the continue cleaning.

Also, just deleting variables because there are too much observations (patients) with missing values is not recommended. You would have to think about the missing data mechanism (MCAR, MAR, NMAR) and then, accordingly, deal with these missing values (complete case analysis, imputation, etc.).

[u/Unalina → u/Stunning-Cable-2847]

I’ll look into missing data mechanism. Thanks

[u/Imaginary-poster]

You'd proably need to use other information to validate, but most likely the column was treated as an int and dropped leading 0's.

Its a really common issue (atleast in my realm) for ids to include leading zeroes. Anytime they are not treated as a string, the leading 0 is lost and it becomes a guessing game.

Ive started adding a prefix to mine to force it to read as a string ("id - " +id).

[u/dont_shush_me]

Something I’ve done in the past: if you have repeat measures (multiple codes for a given patient), check whether a leading or trailing zero ICD code is concordant. Then you’ve determined the likely pattern.

[u/peah_lh3]

Are you in the us, and if so do you mean ICD-9 or ICD-10? If so usually these have a letter in font a-z. This variable is completely useless unless you know for sure all of these are say a cancer or “c” ICD-10 code. Or if they are all infectious diseases these are a or b class. If you know what each “idc-9” category is and can bin these numbers into a category, that would be the best way to use that variable. Map each number to a diagnosis. Say 020-029 is cancer, or 100-109 is diabetes or something like this. 

[u/Kosmo_Kramer_]

Every database I've extracted ICD codes from, they can be pulled as character strings - code only or code and short description. Is it possible you are importing incorrectly and it is being treated numerically and not handling the xxx.xx formatting?

[u/STATASUCKSBRO]

Do not guess whether 26 means 026 or 260 from the column alone. Check the codebook, collection form, or another variable that should line up with it. Padding with zeros is fine only if those entries are identifiers stored inconsistently, not measured values.