1
$\begingroup$

I have a Dataset containing information about spendings of customers in various shops. There are 10 spending variables related to some categories (like spendings on clothing, spendings on hardware, spendings on service) and one variable which is spendings_overall. Spendings_overall should be the sum of the 10 single subcategory spendings. There are some additional variables describing the customers (age, sex, customergroup, ...)

The Problem: Participants have the possibility to say "I don't know the amount I've spent any more, but I know that I have spent something". So in some cases all 10 subcategory-spendings and the overall spendings variables might be not NA. In some cases some of the variable might be NA and in some cases all of the variables could be NA.

My goal is to impute the missing data, but i have no idea how to deal with the constraint of spendings_subcategorie_1 + spendings_subcategorie 2 + ... + spedings_subcategorie_10 = spendings_overall.

Usually i would try to hit the missings values with missForest, but i don't think, that there is any possibility to include the constraint i need (or at least i have no idea how to do so).

So i would like to ask which approaches i could try for the given problem. Any hints and tips are very welcome.

Additional Information about the data collecting process: In a first step participants were asked if they had any spendings or not for each of the 10 subcategories (not the overall spendings, since only "true" customers and not "just visitors" were asked). If they answered with yes they were asked about how much they did spend for the given subcategory. Here it was possible to leave the input-field blank and instead select the checkbox "i cant remember my spendings any more". After this there was a final question about the total amount spent (again with the possibility to answer "i cant remember my spendings"). Unfortunately the sum of subcategory spendings and the answered spendings overall do not add up to the same total amount in some cases (considering only cases without any missing data in subcategory and overall spendings). But we want to "unify" sum of subcategories and overall spendings before starting data-imputation (either by averaging the two values or selecting one of both and adapting the sub-categorie spendings on the total spendings). We also talked about inputing before "unifying", but decided against it.

Unfortunately i cant share the original data, but the dataframe looks like this:

set.seed(123)

data_spendings = data.frame(matrix(rep(NA, 140), ncol = 14))
names(data_spendings) = c("age", "sex", "customergroup", "spendings_overall", paste0("spendings_subcat_", 1:10))

data_spendings$age = round(rnorm(10, 50, 20)) # participants age
data_spendings$sex = sample(c("male", "female"), 10, replace = T) # participants gender
data_spendings$customergroup = sample(c(1:5), 10, replace = T) # grouping of customers, depending on crs data
data_spendings[5:14] = matrix(rnorm(100, 100, 20), ncol = 10) # spendings on 10 different subcategories (like spendings_clothing, spendings_hardware, spendings_service etc.)
data_spendings$spendings_overall = rowSums(data_spendings[5:14]) # overall spendings of the person (which should be the sum of the single subcategorie spendings)

# Problem: People had the option to say "i know i spent something, but i can't remember how much it was"

cant_remebers = rep(FALSE, NROW(data_spendings)*11)
cant_remebers[sample(1:length(cant_remebers), round(length(cant_remebers)) *0.3)] = TRUE # approximately 30% of the spendings cant be remembered
data_spendings[4:14][matrix(cant_remebers, ncol = 11, byrow = T)] = NA

data_spendings
Improve this question
$\endgroup$
4
  • $\begingroup$ Is the overall spending a calculated or measured quantity? That is, are the subjects asked for the category spending and then the analyst sums them up? Or are the subjects asked for the categories and the total. If the latter, do the categories, in fact, add up to the total? If the former, do you ever have a non-missing total when you have any missing category amounts (and why)? $\endgroup$ – Bill Nov 12 '18 at 21:18
  • $\begingroup$ Thanks for the feedback, @Bill. I will edit the information into the original post. Also thanks Erik Markhauser for correcting my bad spelling :) $\endgroup$ – TinglTanglBob Nov 13 '18 at 8:57
  • $\begingroup$ OK, and I suppose you don't want to impute prior to the unifying step because you don't want the imputation of one variable to then pollute other variables. $\endgroup$ – Bill Nov 13 '18 at 12:57
  • $\begingroup$ Yes, we hope that the unifying step does increase the quality/accuracy of the data and imputation afterwards can benefit from it allready. $\endgroup$ – TinglTanglBob Nov 13 '18 at 13:14
1
$\begingroup$

Not knowing can be an indication as well, for a kaggle competition i once just put in a column unknown age, the reason was not knowing their age could be because of a reason. The reason in your case might be truly forgotten, or don't want to tell (privacy), or the number was to low to remember. Maybe they buy goods they rather not tell about, and maybe thats typical for these people as well. People who spend money and cannot remember might be high-value customers (if you run a casino).

You might group by similar people and then by similar spendings, then take that average. or / and them to a certainly not remembering boolean. (let the neural net find out itself, how valuable such a column would be).

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.