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: