# Replacing the missing values with the maximum value

I have the following dataset:

userid   days1    days2    days3   avg_days
1        Nan      0        3       1.5
2        6        8        1       5
3        0        0        0       0
4        1        5        Nan     3
5        NaN      NaN      NaN     NaN
6        0        0        0       0
7        1        Nan      Nan     1
8        NaN      NaN      NaN     NaN


In this dataset, I have the information regarding how early user makes a request about items 1, 2 and 3. In some cases, users may not have requested the item, which are indicated as NaN. We try to calculate the avg_days (the average days until the request was made after the release of the item). We want to correlate this variable with some other behaviours of the users.

To explain the data better, for example, user #1, didn't request the item 1 (i.e., NaN, and requested the item 2 on the same day (i.e., 0, when the item was released), and requested the item 3, after 3 days of its release.

However, for users with no requests (e.g., user 5 and user 8), it is impossible to calculate the avg_days. As a solution to this issue we have considered replacing NaN values (in avg_days column) with the maximum avg_day value calculated (which is 5 in this case):

userid   days1    days2    days3   avg_days
1        Nan      0        3       1.5
2        6        8        1       5
3        0        0        0       0
4        1        5        Nan     3
5        NaN      NaN      NaN     5 * replaced
6        0        0        0       0
7        1        Nan      Nan     1
8        NaN      NaN      NaN     5 * replaced


Our logic was that as the higher avg_days values would indicate negativity (delay of requesting the item), it would be reasonable to place the most negative value for the users who have not request anything. This way, we were able to include them in the analysis, and we obtained some meaningful correlations. Without this (i.e., when the missing data was discarded), there were no significant correlations.

However, we are not sure about the validity of this approach. I wonder if someone has opinion on this and if such approaches have been used in the literature? Thanks for help!

Not only is it probably a bad idea to single impute the all-missing case with the overall mean, it's also a bad idea to simply take the average among the non-missing cases.

Using the data to drive an empirical imputation procedure is only valid when the data are ignorably-missing: that means that missingness doesn't depend on the actual value. Knowing nothing of your problem, I can't help you figure that out. But an example where it matters is, say, self-reported pain scale. If the patient can't actually tell you how much pain they're in because they're in too much pain, then that's not really missing (quite the opposite, in fact). You can't test or determine non-ignorable missingness. When missingness is ignorable, a minimally complete approach to imputation is multiple imputation.

If the data are not ignorably missing, you can impute with "worst case scenario" (based on empirical maximums), or use probability models for responses and expectation maximization to model a range of possible responses.

EDIT:

The data are structured such that there is 1 row per user, and 1 column per item. The value represents the days between when an item came available and when the user buys that item. Missing values means the user has not yet ordered that item. My recommendation is to use a survival model with right censoring for items not-yet ordered. The average is not a valid metric for right censored data.

• I think my case is somehow similar to your example. I am calculating avg_days as a way to identify their interest in the items. If they have not requested any item, that means (probably) they have no interest. Sample size is 900, and around 600 of them have not requested any item. Therefore, the data are not ignorably-missing (if I understand you correctly). And, you recommend impute with "worst case scenario" (based on empirical maximums), is this what I am doing, is that right? Thanks for all the help! – renakre Jun 11 '18 at 16:47
• @renakre Can you say more why they show up as N/A? You have other 0s so why did they drop out? – AdamO Jun 11 '18 at 16:55
• 0 means that the user requested the item on the same day of its release (so it is a very good value indicating the high interest of the user). If they have not requested an item, its value is N/A, which means actually they have no interest (therefore it is not really a missing value at random). avg_days is based on the values that are not N/A. If all of them N/A, then avg_days has to be N/A, which is the problem I am trying to address. Is this clearer now? Thanks a lot again! – renakre Jun 11 '18 at 17:00
• May I kindly ask how to perform the 1st recommendation (hierarchical model), and the reason for this? AFAIU, this is not for imputing any values. Thanks! – renakre Jun 11 '18 at 17:11
• @renakre So "daysx" is a lag from release date til a user orders it? If they just haven't ordered it yet, then it's a survival problem with censored data. That's not missing data. Use current date difference and a 0-event (censor) indicator in a Cox model. Sounds like you may need more in-depth consultation help. – AdamO Jun 11 '18 at 17:12

You should first determine what type of problem this is: MAR,MCAR,MNAR which you can read about on the following link

https://towardsdatascience.com/how-to-handle-missing-data-8646b18db0d4

You can follow approach accordingly. If it is okay, then just disregard user 5&8 for the analysis purposes else you have to justify why the user behavior is correlated to the other user behavior.

• But why do you get to keep user 1, 4, and 7? – AdamO Jun 11 '18 at 16:38