# How can I fix incomplete data using ranking column?

• Goal : I have to make my incomplete data into useful one.
• Here is example data

 product_id    date   sales_volume   sales_ranking
A         1001     95.75             3
A         1002        30             3
B         1002         5             4
C         1001        NA             1
D         1001       101             2
E         1001        79             4
E         1002      61.5             1
F         1002        NA             2


sales_ranking comes from comparing all product sold on same day.
For example, on date 1001, ranking is determined by sales_volume within four items.
> [C=1st, D=2nd, A=3rd, E=4th]. This ranking record is never changed.

However, sales_volume is corrupted after ranking process.

Maybe like

 product_id date    sales_volume_complete   sales_volume_corrupted  sales_ranking
A    1001              97                     95.75                  3
A    1002              30                     30                     3
B    1002              5                      5                      4
C    1001              134                    NA                     1
D    1001              101                    101                    2
E    1001              79                     79                     4
E    1002              65                     61.5                   1
F    1002              59                     NA                     2

• So, my data has problem that sales_volume has many NA and record is not accurate (such as 5.5, not integer value).
Data has 97620*4 dimension, and 77226 rows of sales_volume is NA. (almost 80% is NA)

• sales_ranking has 181 times 1 to 540 vector. It means top 540 products are ranked during 181 days.

• product_id is consists of 1295 products.

What I need is reasonable approach to estimate sales_volume or imputation. Because sales_ranking is reliable data, I try to search rank related statistical model such as rank regression, but that model has so many parameters .

Also, I tried this R data.table syntax to check relationship between sales_volume and sales_ranking.

> data[, sum(sales_volume, na.rm=T), by=sales_ranking]
sales_ranking      V1
1:    1         11993073725
2:    2         65963107175
3:    3         12593081075
4:    4         51582313750
5:    5         42917730800
---
536:  536             8821300
537:  537             3672425
538:  538             2415148
539:  539             1246525
540:  540             5016275


There are no simple pattern that sales_ranking is high, sum of sales_volume is always high. Some NA values are not included in sum data, so it cannot directly used to comparing difference of each ranking. For example, sum of sales ranking 540 is 5016275 which is bigger than ranking 537~539

• I have bachelor degree on statistic, but have no experience of business analysis. I use R mainly, but other language is okay. Thank you for your advice.
• Your question is very unclear. Maybe my blog post how to ask a statistics question can help you write a better question. – Peter Flom Oct 3 '17 at 23:24
• I still think this is unclear - for example we are told at the start that "sales_volume is corrupted after ranking process" but then told that "sales_ranking is reliable data". Is the ranking column really just sales_volume put it ranking order, as the title of this question suggests? If so I can't understand why "There are no simple pattern that sales_ranking is high, sum of sales_volume is always high." – Silverfish Oct 4 '17 at 11:31
• Added content explaining Sliverfish's comment. 1. As I know, ranking is calculated on complete dataset, and it never changed. Only sales_volume is changed. 2. Then, it is possible that sum of rank 540 is bigger than sum of rank 539 because of NA values. If there are unexplained material, please leave comment. Thanks for all comments. – wi11 Oct 4 '17 at 13:54