# Normalizing matrix values python/R

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.

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.

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

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

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

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.

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()

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.

• thanks a lot for your time and efforts. Could you please explain last line ds1 = ds1.apply(lambda x: x.fillna(ds2_mean[1])) – user2129623 May 5 '15 at 19:03
• @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? – Anzel May 5 '15 at 20:32
• 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 – user2129623 May 6 '15 at 4:00

try fillna and dropna functions in pandas

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