Method for predicting price based on Geographical market, Product, and Company

Question

I have a dataset which tracks the prices of 21 products, charged by 24 companies, in 150 different cities across the globe. However, the data set has missing values--that is, I might have Company X's price for Product A in London, but not New York. I am looking for guidance on how to impute these values given the data that I already have.

There are trends to be exploited in the data. Such as:

• Geographical trends: i.e, London and New York are similar markets as measured by the median price of a given product, but markets like Cairo and Johannesburg tend to be much more expensive (though in varying degrees for different products)
• Product Trends: Within a given market, for a given company, products are priced hierarchically, although the relationship is nonlinear. For example, if the products are "Good", "Better" and "Best", the price of "Better" might be 2X"Good", and the price of "Best" might be 2.5X "Good", though these relationships are not strictly held.
• Company Trends: Certain companies tend to have higher prices than others in most markets, but again, these relationships are not strictly held, and may differ for certain subsets of products or markets where a company has a competitive advantage.

I describe the method that I have tried so far below, though I feel it is overly simplistic. I am looking for suggestions as to how to better capture the complexity of my data and obtain a reasonable accurate imputation for the missing values. Any advice is appreciated.

The method I have tried so far is to estimate coefficients for each category. For example, holding Company and Product constant, what is the average price multiple of Market B over Market A? Market C over Market A? Then, if I know I have a price for (Company X, product "Good") in markets B and C, I'll multiply them by those average multiples in order to obtain a "best guess based on market". Then I repeat that holding constant Company and Market, and Market and Product. At the end, I'm left with three "best guesses". Then the issue is converting my three best guesses into a single guess, because, based on some testing of this method, the true price may lie either between or outside these three best guesses. This is the histogram of errors based on a training sample:

Answer 1

It may be usefull to put what you are doing in a formal context, that way you can look at some standard solutions to your problem.

You have obsvations of a (random) variable X (price), together with some explanatory variables L, C, P (L="location" = city, C=company, P=price). So your data consists of cuadruples $(C_i, L_i, P_i, X_i)$.

You are assuming a $\mathbf{multiplicative}$ model:
$$ E(X|C,L,P) = \lambda \space f_C \space g_L \space h_P .$$

This model fits nicely in the context of generalized linear models, since: $$ \ln (E(X|C,L,P)) = \ln(\lambda) + \ln( f_C) + \ln( g_L) + \ln( h_P) .$$

You are fitting the model by a variation of iterative scaling. I don't know if there is a probabilistic model that corresponds to that way of fitting the models. Both the multiplicative model and iterative scaling have long traditions, so the answer might be known.

In any case, once in the context of generalized linear models, you can fit the parameters making some assumptions on the error. Since $X$ counts the number of monetary units per item, you can start by trying a Poisson model (and a Poisson model with overdisperssion). It is also worth trying other GLM's.

Answer 2

Your method looks interesting. I will borrow it when I have the chance. Let me make a couple of suggestions ("suggestions", not "prescriptions"):

1. The price multiple is usually refer as "index." Consider indexing against an over-all weighted average, instead of doing the pairwise indexing. A weighted average index will make comparisons against a more stable denominator. To circumvent the missing values in the index, you can use the indexed lagged one unit of time, use it with today's values, and make updates (replacing today NA's with predictions) to be used tomorrow.

2. In the past I tried the following: if the value of a quantity $X$ at time $t$ is not available, use $\hat X(t) = \text{most recent observed value of $X$}$. It did not work very well in my case for what I was trying to do, but if the dynamics of the quantities you are tracking is not too wild, it might work for you. You may combine both ideas, using 2) to update the baseline of the index in 1).

Remember that there are no "best predictions". There are only "reasonable prediction methods", and "useful predictions." My suggestion of using some average as baline for the index is borne in from the fact that averages are more stable than single observations.

Answer 3

I would train multiple machine learning models based on information that is available. First of all I would standardize the data per product, so that the average is 0. That way you can better compare different price categories. I would add the mean price known to your set however, standardized over your whole training data, because I can imagine policies being different for different price groups. Now you can train different models for different products. The more features you use, the less data you have so this is a trade-off that you have to test. Here is an example of what I'm talking about (non-standardized):

ID  Price1 Price2 Price3 Feat1 Feat2
1   1      2      NA     3     8
2   4      NA     4      6     9
3   2      NA     3      5     3
4   NA     2      NA     3     4
5   5      1      NA     4     5
6   NA     3      5      2     3
7   8      5      7      4     1

Now to impute the missing Price1 values you could use:

• Price1 = F(Price2, Price3, Feat1, Feat2) using only 1 row (ID: 7)

• Price1 = F(Price2, Feat1, Feat2) using 3 rows (IDs: 1, 5, 7)

• Price1 = F(Feat1, Feat2) using 5 rows (IDs: 1, 2, 3, 5, 7)

In this very short example the third one is probably better, but with more data using more features will lead to better results.

To compare imputation models you could per price remove a number of known entities, train on the rest and compare the output to the known price. You can use a crossvalidation scheme to get all the prices in there once if the process is fully automated. I would do this at the standardized prices so it's about the relative mistakes and not absolute.