1
$\begingroup$

I am trying to fill missing values in 1000 x 1000 matrices. Dataset1 contains such 1000 x 1000 matrice with value ranging 1-100. Any point which has value 0, that means it is missing.

Sample dataset: Link

Dataset2 is historical data to get missing values. It has 4k rows with 2 column. column1 contains any values among 1-100 and column 2 corresponding state value ranging from 1-4.

It looks like this: Link

My approach to get the missing value:

  1. Get average state value for each 1-100 value in ds2
  2. replace each values 1-100 in ds1 with is mapping values in ds2
  3. to get missing value, get average of all neighbour for each point having values 0

Is it correct for missnig value retrival?

In python or R, is there any helping package for this stuff? doing mauanlly I could have done, but willing to know if any supportive function are there

$\endgroup$
  • 2
    $\begingroup$ Are you interested in statistical techniques for the imputation of missing data per se, or just coding issues? $\endgroup$ – chl May 5 '15 at 8:08
  • $\begingroup$ @chl: I would be happy to see statistical techniques too for the same $\endgroup$ – user2129623 May 5 '15 at 18:35
  • $\begingroup$ I think the question has some statistical content, though as I type this the answers don't seem to . $\endgroup$ – Glen_b May 6 '15 at 22:28
1
$\begingroup$

To elaborate how fillna can achieve what you want, I have an attempt as follow:

import pandas as pd
import numpy as np

# supposed the ds1 is your dataset1, here with 0..1000 as column, 0..100 as index
ds1 = pd.read_table('https://gist.githubusercontent.com/karimkhanp/2216bf373d9fbc9137b4/raw/dead5296ea0d69c85e3d3784a0317383c3c21fce/ds1', sep=' ', header=None)

ds1.head()
Out[23]: 
   0     1     2     3     4     5     6     7     8     9     ...   991   0    13    15    15   NaN   NaN   NaN    14   NaN   NaN   NaN  ...    NaN   
1    14    13    14    14    16   NaN   NaN   NaN    16   NaN  ...    NaN   
2    16   NaN    13    14   NaN    14    14   NaN    16   NaN  ...     16   
3    16   NaN    15    16    14    15    15   NaN   NaN    13  ...    NaN   
4   NaN    13    13    16    15   NaN    15   NaN    15   NaN  ...     16   

   992   993   994   995   996   997   998   999   1000  
0    14    14    13    13   NaN    15    15    16   NaN  
1    16   NaN    16    16    15    13   NaN    16   NaN  
2    14    14   NaN    15    14   NaN    15   NaN   NaN  
3    13    15    14    16    16    13    16    14   NaN  
4    15    15    14    14    14   NaN    14   NaN   NaN  

[5 rows x 1001 columns]

# and ds2 is your dataset2, [0, 1] as columns etc.
ds2 = pd.read_table('https://gist.githubusercontent.com/karimkhanp/1692f1f76718c35e939f/raw/6f6b348ab0879b702e1c3c5e362e9d2062e9e9bc/ds2', header=None, sep=' ')

ds2.head()
Out[24]: 
    0  1
0  11  1
1  19  1
2  42  2
3  16  1
4  63  3

# first get the mean of what first-column values of ds2
ds2_mean = ds2.groupby(0).mean()

ds2_mean.head()
Out[26]: 
           1
0           
10  1.103774
11  1.173554
12  1.168224
13  1.312500
14  1.133333

# replace all 0's in ds1 to nan
ds1.replace(0, np.nan, inplace=True)

# then apply the fillna on a per row basis and replace with relative ds2_mean[1]
# values according to x-index on ds1
ds1 = ds1.apply(lambda x: x.fillna(ds2_mean[1]))

It should achieve what you want. I am way past my sleeping time so there may be mistakes, please let me know in that case and I will correct it when I wake up, hope this helps.

$\endgroup$
  • $\begingroup$ thanks a lot for your time and efforts. Could you please explain last line ds1 = ds1.apply(lambda x: x.fillna(ds2_mean[1])) $\endgroup$ – user2129623 May 5 '15 at 19:03
  • $\begingroup$ @user2129623, not a problem. ds1.apply(...) means apply a function to the whole ds1, whereas lambda x: x.fillna(...) means on each row to apply the fillna <-- think of lambda as each row in this case. Finally, fill na with values from ds2_mean[1], [1] is the column header which ds2_mean[1] yields the values list with indexes/value pairs (think of Series with indexes), that is, fill the na with relative indexed value from ds2_mean. Does it all make sense now? $\endgroup$ – Anzel May 5 '15 at 20:32
  • $\begingroup$ thanks a lot, but I want little different. I dont want to replace with index value. also not the NA value. I want value in col1 of ds2 should be replace by corresponding col2 value in ds1 $\endgroup$ – user2129623 May 6 '15 at 4:00
0
$\begingroup$

try fillna and dropna functions in pandas

$\endgroup$
  • $\begingroup$ @cdaladenes: thanks a lot, but does approach i have used for missing value is reasonable? $\endgroup$ – user2129623 May 5 '15 at 2:18
  • 6
    $\begingroup$ Can you elaborate on this answer? $\endgroup$ – gung May 5 '15 at 2:22

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.