<jupyter_start><jupyter_text>We'd be deaiing with the missing values for Population [2011]Correlation between Female Population and Population [2011] comes out to be close to 0.98, which shows that only one of these values is sufficient to showcase the impact on Covid Cases. Also, that we can use the values of Female Population to generate the missing values. As we have already dealt with other categories, we can move towards cleaning the data now.<jupyter_code>train = pd.read_excel('train_dataset1.xlsx')
test = pd.read_excel('test_dataset1.xlsx')
from array import array<jupyter_output><empty_output><jupyter_text>Adjusting for missing Population [2011] values in training data<jupyter_code>available = train['Population [2011]'].isnull()
available_index = []
target_index = []
j = 0
for i in available:
    
    if(not i):
        available_index.append(j)
    else:
        target_index.append(j)
    j+=1
print(target_index)
fpi = train['Female Population'].isnull()
fpa = []
fpp = []
j = 0
for i in fpi:
    if(not i):
        fpa.append(j)
    else:
        fpp.append(j)
        
    j+=1
#print(fpa)
train['Population [2011]']
from array import array
xs = array('i')
ys = array('i')
j = 0
for i in available_index:
    if i in fpa:
        
        xs.append(int(train['Female Population'][i]))
        ys.append(int(train['Population [2011]'][i]))
    
    
xp = array('i')
yp = array('i')
for i in target_index:
    if i in fpa:
        print(i)
        xp.append(int(train['Female Population'][i]))
       
        
xt = np.array(xs).reshape(-1, 1)
yt = np.array(ys).reshape(-1, 1)

xpt = np.array(xp).reshape(-1, 1)

print(xpt)<jupyter_output>46
47
48
49
50
51
52
53
287
288
289
290
291
292
293
294
352
353
354
355
356
357
358
359
360
511
512
513
514
515
516
517
518
729
730
731
732
733
734
735
736<jupyter_text>Since the correlation between Female Population and Population [2011] is close to zero, we can use values from Female Population to predict the already missing values in our Population [2011] category<jupyter_code>from sklearn.linear_model import LinearRegression
lr = LinearRegression()
lr.fit(xt, yt)
s = lr.predict(xpt)
lst = []
mn = train['Population [2011]'].mean()

j = 0
for i in train['Population [2011]']:
    if pd.isnull(i):
        if j<41:
            lst.append(int(s[j]))
            j+=1
        else:
            lst.append(int(mn))
    else:
        lst.append(int(i))
ls = {'Population [2011]' : lst}
train['Population [2011]'] = pd.DataFrame(data = ls)
train['Population [2011]'].isnull().sum()<jupyter_output><empty_output><jupyter_text>adjusting for missing values in Test dataset<jupyter_code>available = test['Population [2011]'].isnull()
available_index = []
target_index = []
j = 0
for i in available:
    
    if(not i):
        available_index.append(j)
    else:
        target_index.append(j)
    j+=1

fpi = test['Female Population'].isnull()
fpa = []
fpp = []
j = 0
for i in fpi:
    if(not i):
        fpa.append(j)
    else:
        fpp.append(j)
        
    j+=1
#print(fpa)
from array import array
xs = array('i')
ys = array('i')
j = 0
for i in available_index:
    if i in fpa:
        
        xs.append(int(test['Female Population'][i]))
        ys.append(int(test['Population [2011]'][i]))
    
    
xp = array('i')
yp = array('i')
for i in target_index:
    if i in fpa:
        #print(i)
        xp.append(int(test['Female Population'][i]))
       
        
xt = np.array(xs).reshape(-1, 1)
yt = np.array(ys).reshape(-1, 1)

xpt = np.array(xp).reshape(-1, 1)

#print(xt)
from sklearn.linear_model import LinearRegression
lr = LinearRegression()
lr.fit(xt, yt)
s = lr.predict(xpt)
lst = []
mn = test['Population [2011]'].mean()

j = 0
for i in test['Population [2011]']:
    if pd.isnull(i):
        if j<41:
            lst.append(int(s[j]))
            j+=1
        else:
            lst.append(int(mn))
    else:
        lst.append(int(i))
ls = {'Population [2011]' : lst}
test['Population [2011]'] = pd.DataFrame(data = ls)
test['Population [2011]'].isnull().sum()
train.to_xlsx() #Provide target file name
test.to_xlsx()  #Provide target file name<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13697640828117477, 'fraction_numerical': 0.05536831969186327, 'mean_word_length': 3.222560975609756, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '631059', 'license': 'MIT', 'n_tokens_mistral': 1678, 'n_tokens_neox': 1464, 'n_words': 418, 'path': 'Task1/Preprocessing - Population.ipynb', 'repo_name': 'rahulv1999/flipr-hackathon', 'size': '4154'}