In [16]:
#Import Libraries
import numpy as np
import pandas as pd
import geopandas as gpd
from shapely.geometry import Point, Polygon
import json
import requests
import math
import os
import time
import datetime
In [17]:
# Import folders
data_folder = os.path.abspath('data')
# Set Output Folder
output_folder = os.path.abspath("output")
if not os.path.exists(output_folder):
    os.makedirs(output_folder)
In [18]:
# Read Constituency Data
ac_gdf = None
ac_filepath = os.path.join(data_folder, "AC", "India_AC.shp")
ac_gdf = gpd.read_file(ac_filepath)
ac_gdf

# Read Constituency Data
ac_file = os.path.join(data_folder, "General_Later_Ashoka_alldata.csv")
acdf = pd.read_csv(ac_file)
acdf['state_name'] = acdf['state_name'].str.replace("_", " ")
#acdf = acdf[['state_name', 'constituency_no', 'constituency_name', 'year', 'month']]
acdf = acdf.drop_duplicates(subset=None, keep="first", inplace=False).reset_index(drop=True)
acdf.loc[acdf['newstate_code'] == 36, 'newstate_code'] = 28 #Telangana  Fix
acdf = acdf[['state_name', 'state_code', 'constituency_no', 'year', 'month']]
acdf['day'] = 1
acdf['datetime'] = pd.to_datetime(acdf[['year', 'month', 'day']])
acdf = acdf.drop_duplicates().reset_index(drop=True)
acdf = pd.merge(acdf, ac_gdf,  how='inner', left_on=['state_code', 'constituency_no'], right_on = ['ST_CODE','AC_NO'])#[['ST_CODE', 'ST_NAME','DT_CODE', 'DIST_NAME', 'AC_NO', 'AC_NAME', 'PC_NO', 'PC_NAME']]
acdf = acdf[acdf.columns[:-1]]
acdf
Out[18]:
state_name state_code constituency_no year month day datetime OBJECTID ST_CODE ST_NAME DT_CODE DIST_NAME AC_NO AC_NAME PC_NO PC_NAME PC_ID STATUS Shape_Leng Shape_Area
0 Jammu & Kashmir 1 1 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 1 KARNAH 1 BARAMULLA 0 Pre delimitation 3.078761 0.166017
1 Jammu & Kashmir 1 1 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 1 KARNAH 1 BARAMULLA 0 Pre delimitation 3.078761 0.166017
2 Jammu & Kashmir 1 2 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 2 KUPWARA 1 BARAMULLA 0 Pre delimitation 1.875372 0.024868
3 Jammu & Kashmir 1 2 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 2 KUPWARA 1 BARAMULLA 0 Pre delimitation 1.875372 0.024868
4 Jammu & Kashmir 1 3 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 3 LOLAB 1 BARAMULLA 0 Pre delimitation 1.440284 0.081811
5 Jammu & Kashmir 1 3 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 3 LOLAB 1 BARAMULLA 0 Pre delimitation 1.440284 0.081811
6 Jammu & Kashmir 1 4 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 4 HANDWARA 1 BARAMULLA 0 Pre delimitation 2.101115 0.047072
7 Jammu & Kashmir 1 4 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 4 HANDWARA 1 BARAMULLA 0 Pre delimitation 2.101115 0.047072
8 Jammu & Kashmir 1 5 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 5 LANGATE 1 BARAMULLA 0 Pre delimitation 1.557770 0.037407
9 Jammu & Kashmir 1 5 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 5 LANGATE 1 BARAMULLA 0 Pre delimitation 1.557770 0.037407
10 Jammu & Kashmir 1 6 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 6 URI 1 BARAMULLA 0 Pre delimitation 1.703610 0.078021
11 Jammu & Kashmir 1 6 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 6 URI 1 BARAMULLA 0 Pre delimitation 1.703610 0.078021
12 Jammu & Kashmir 1 7 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 7 RAFIABAD 1 BARAMULLA 0 Pre delimitation 0.891246 0.030647
13 Jammu & Kashmir 1 7 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 7 RAFIABAD 1 BARAMULLA 0 Pre delimitation 0.891246 0.030647
14 Jammu & Kashmir 1 8 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 8 SOPORE 1 BARAMULLA 0 Pre delimitation 0.652104 0.019088
15 Jammu & Kashmir 1 8 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 8 SOPORE 1 BARAMULLA 0 Pre delimitation 0.652104 0.019088
16 Jammu & Kashmir 1 9 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 9 GUREZ 1 BARAMULLA 0 Pre delimitation 2.069633 0.101066
17 Jammu & Kashmir 1 9 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 9 GUREZ 1 BARAMULLA 0 Pre delimitation 2.069633 0.101066
18 Jammu & Kashmir 1 10 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 10 BANDIPORA 1 BARAMULLA 0 Pre delimitation 1.716179 0.090918
19 Jammu & Kashmir 1 10 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 10 BANDIPORA 1 BARAMULLA 0 Pre delimitation 1.716179 0.090918
20 Jammu & Kashmir 1 11 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 11 SONAWARI 1 BARAMULLA 0 Pre delimitation 0.960085 0.029207
21 Jammu & Kashmir 1 11 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 11 SONAWARI 1 BARAMULLA 0 Pre delimitation 0.960085 0.029207
22 Jammu & Kashmir 1 12 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 12 SANGRAMA 1 BARAMULLA 0 Pre delimitation 0.915233 0.014017
23 Jammu & Kashmir 1 12 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 12 SANGRAMA 1 BARAMULLA 0 Pre delimitation 0.915233 0.014017
24 Jammu & Kashmir 1 13 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 13 BARAMULA 1 BARAMULLA 0 Pre delimitation 0.976087 0.021035
25 Jammu & Kashmir 1 13 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 13 BARAMULA 1 BARAMULLA 0 Pre delimitation 0.976087 0.021035
26 Jammu & Kashmir 1 14 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 14 GULMARG 1 BARAMULLA 0 Pre delimitation 1.429250 0.043373
27 Jammu & Kashmir 1 14 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 14 GULMARG 1 BARAMULLA 0 Pre delimitation 1.429250 0.043373
28 Jammu & Kashmir 1 15 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 15 PATTAN 1 BARAMULLA 0 Pre delimitation 0.902348 0.011106
29 Jammu & Kashmir 1 15 2014 12 1 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 15 PATTAN 1 BARAMULLA 0 Pre delimitation 0.902348 0.011106
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
8328 Puducherry 34 16 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 16 Orleampeth 1 PONDICHERRY 3401 None 0.045315 0.000106
8329 Puducherry 34 16 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 16 Orleampeth 1 PONDICHERRY 3401 None 0.045315 0.000106
8330 Puducherry 34 17 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 17 Nellithope 1 PONDICHERRY 3401 None 0.052031 0.000071
8331 Puducherry 34 17 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 17 Nellithope 1 PONDICHERRY 3401 None 0.052031 0.000071
8332 Puducherry 34 18 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 18 Mudaliarpet 1 PONDICHERRY 3401 None 0.073962 0.000236
8333 Puducherry 34 18 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 18 Mudaliarpet 1 PONDICHERRY 3401 None 0.073962 0.000236
8334 Puducherry 34 19 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 19 Ariankuppam 1 PONDICHERRY 3401 None 0.142251 0.000822
8335 Puducherry 34 19 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 19 Ariankuppam 1 PONDICHERRY 3401 None 0.142251 0.000822
8336 Puducherry 34 20 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 20 Manavely 1 PONDICHERRY 3401 None 0.256803 0.001678
8337 Puducherry 34 20 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 20 Manavely 1 PONDICHERRY 3401 None 0.256803 0.001678
8338 Puducherry 34 21 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 21 Embalam (SC) 1 PONDICHERRY 3401 None 0.412711 0.002462
8339 Puducherry 34 21 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 21 Embalam (SC) 1 PONDICHERRY 3401 None 0.412711 0.002462
8340 Puducherry 34 22 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 22 Nettapakkam (SC) 1 PONDICHERRY 3401 None 0.376843 0.002113
8341 Puducherry 34 22 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 22 Nettapakkam (SC) 1 PONDICHERRY 3401 None 0.376843 0.002113
8342 Puducherry 34 23 2011 5 1 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 23 Bahour 1 PONDICHERRY 3401 None 0.323299 0.002570
8343 Puducherry 34 23 2016 5 1 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 23 Bahour 1 PONDICHERRY 3401 None 0.323299 0.002570
8344 Puducherry 34 24 2011 5 1 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 24 Nedungadu (SC) 1 PONDICHERRY 3401 None 0.356111 0.003683
8345 Puducherry 34 24 2016 5 1 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 24 Nedungadu (SC) 1 PONDICHERRY 3401 None 0.356111 0.003683
8346 Puducherry 34 25 2011 5 1 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 25 Thirunallar 1 PONDICHERRY 3401 None 0.360203 0.003771
8347 Puducherry 34 25 2016 5 1 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 25 Thirunallar 1 PONDICHERRY 3401 None 0.360203 0.003771
8348 Puducherry 34 26 2011 5 1 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 26 Karaikal North 1 PONDICHERRY 3401 None 0.161909 0.001563
8349 Puducherry 34 26 2016 5 1 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 26 Karaikal North 1 PONDICHERRY 3401 None 0.161909 0.001563
8350 Puducherry 34 27 2011 5 1 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 27 Karaikal South 1 PONDICHERRY 3401 None 0.163859 0.001069
8351 Puducherry 34 27 2016 5 1 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 27 Karaikal South 1 PONDICHERRY 3401 None 0.163859 0.001069
8352 Puducherry 34 28 2011 5 1 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 28 Neravy- T.R. Pattin 1 PONDICHERRY 3401 None 0.542616 0.003242
8353 Puducherry 34 28 2016 5 1 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 28 Neravy- T.R. Pattin 1 PONDICHERRY 3401 None 0.542616 0.003242
8354 Puducherry 34 29 2011 5 1 2011-05-01 1 34 PUDUCHERRY 3.0 MAHE 29 Mahe 1 PONDICHERRY 3401 None 0.210761 0.000755
8355 Puducherry 34 29 2016 5 1 2016-05-01 1 34 PUDUCHERRY 3.0 MAHE 29 Mahe 1 PONDICHERRY 3401 None 0.210761 0.000755
8356 Puducherry 34 30 2011 5 1 2011-05-01 1 34 PUDUCHERRY 1.0 YANAM 30 Yanam 1 PONDICHERRY 3401 None 0.388887 0.002640
8357 Puducherry 34 30 2016 5 1 2016-05-01 1 34 PUDUCHERRY 1.0 YANAM 30 Yanam 1 PONDICHERRY 3401 None 0.388887 0.002640

8358 rows × 20 columns

In [19]:
# Read District Headquarter Data
filename = 'New_Headquarters 2017 with lat lon.xlsx'
filepath = os.path.join(data_folder, filename)
disthq_df = pd.read_excel(filepath)
disthq_df['n_statename'] = disthq_df['n_statename'].str.upper()
disthq_df = disthq_df[(disthq_df['n_latitude'] > 0) & (disthq_df['n_longitude'] > 0) & (disthq_df['n_year'] > 0)].reset_index()
disthq_df.loc[disthq_df['n_month'].isnull(), 'n_month'] = 6
disthq_df.loc[disthq_df['n_day'].isnull(), 'n_day'] = 1
disthq_df['n_month'] = disthq_df['n_month'].astype(int)
disthq_df['n_day'] = disthq_df['n_day'].astype(int)
disthq_df['datetime'] = pd.to_datetime(disthq_df[['n_year', 'n_month', 'n_day']].rename(columns={"n_year": "year", "n_month": "month", "n_day": "day"}))
disthq_df
Out[19]:
index DistrictCode n_statename n_distn n_yearsincelastelection n_statecode n_day n_month n_year Alternative Date ... NewHQ1961_71 NewHQ1971_81 NewHQ1981_91 NewHQ1991_2001 NewHQ2001_2011 _merge Latitude Longitude St_C datetime
0 0 627 TAMIL NADU Thoothkundi NaN 33 1 6 1986 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 8.784375 78.099030 33 1986-06-01
1 1 599 KERALA Pathanamthitta NaN 32 1 6 1982 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 9.267448 76.780006 32 1982-06-01
2 2 625 TAMIL NADU Virudhunagar NaN 33 1 6 1980 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 9.556142 77.947655 33 1980-06-01
3 3 596 KERALA Idukki (Painavu) NaN 32 1 6 1972 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 9.847555 76.942200 32 1972-06-01
4 4 622 TAMIL NADU Sivaganga NaN 33 1 6 1984 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 9.860354 78.490913 33 1984-06-01
5 5 624 TAMIL NADU THENI 1.0 33 1 6 1997 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 10.007098 77.504021 33 1997-06-01
6 6 621 TAMIL NADU Pudukkottai NaN 33 1 6 1974 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 10.374989 78.817513 33 1974-06-01
7 7 612 TAMIL NADU Dindigul NaN 33 1 6 1985 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 10.390798 77.963226 33 1985-06-01
8 8 619 TAMIL NADU THIRUVARUR 1.0 33 1 6 1997 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 10.781123 79.606239 33 1997-06-01
9 9 618 TAMIL NADU NAGAPATTINAM 0.0 33 1 6 1991 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 10.799657 79.838799 33 1991-06-01
10 10 613 TAMIL NADU KARUR 4.0 33 1 6 1995 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 10.918594 78.091080 33 1995-06-01
11 11 592 KERALA Malappuram NaN 32 1 6 1969 NaN ... 1.0 0.0 0.0 0.0 0.0 matched (3) 11.038883 76.078011 32 1969-06-01
12 12 633 TAMIL NADU TIRUPPUR 3.0 33 24 10 2008 22/2/09 ... NaN NaN NaN NaN 1.0 matched (3) 11.082498 77.341759 33 2008-10-24
13 13 616 TAMIL NADU ARIYALUR 1.0 33 1 6 2007 NaN ... 0.0 0.0 0.0 0.0 0.0 matched (3) 11.128263 79.085762 33 2007-06-01
14 14 615 TAMIL NADU PERAMBALUR 4.0 33 1 6 1995 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 11.224085 78.875381 33 1995-06-01
15 15 609 TAMIL NADU NAMAKKAL 1.0 33 1 6 1997 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 11.249276 78.128059 33 1997-06-01
16 16 610 TAMIL NADU Erode NaN 33 1 6 1979 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 11.334123 77.708046 33 1979-06-01
17 17 590 KERALA Wayanad (Kalpetta) NaN 32 1 6 1980 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 11.626405 76.088821 32 1980-06-01
18 18 578 KARNATAKA CHAMARAJANAGAR 3.0 29 1 2 1997 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 11.930238 76.946121 29 1997-02-01
19 19 607 TAMIL NADU VILUPPURAM 2.0 33 1 6 1993 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 11.933658 79.482239 33 1993-06-01
20 20 630 TAMIL NADU Dharmapuri NaN 33 1 6 1965 NaN ... 1.0 0.0 0.0 0.0 0.0 matched (3) 12.105276 78.147217 33 1965-06-01
21 21 606 TAMIL NADU TIRUVANNAMALAI 5.0 33 1 6 1989 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 12.268747 79.074142 33 1989-06-01
22 22 588 KERALA Kasaragod NaN 32 1 6 1984 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 12.520589 75.017349 32 1984-06-01
23 23 631 TAMIL NADU KRISHNAGIRI 3.0 33 1 6 2004 NaN ... NaN NaN NaN NaN 1.0 matched (3) 12.551512 78.197426 33 2004-06-01
24 24 584 KARNATAKA RAMANAGARA 3.0 29 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 12.732888 77.295738 29 2007-06-01
25 25 639 ANDAMAN NORTH & MIDDLE ANDAMAN NaN 35 1 6 2006 NaN ... NaN NaN NaN NaN 1.0 matched (3) 12.918849 92.898102 35 2006-06-01
26 26 602 TAMIL NADU THIRUVALLUR 2.0 33 1 6 1991 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 13.142318 79.894447 33 1991-06-01
27 27 583 KARNATAKA Bangalore Rural NaN 29 1 6 1986 NaN ... NaN NaN NaN NaN NaN matched (3) 13.280302 77.622391 29 1986-06-01
28 28 569 KARNATAKA UDUPI 3.0 29 1 2 1997 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 13.362514 74.785057 29 1997-02-01
29 29 582 KARNATAKA CHIKKABALLAPURA 3.0 29 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 13.430833 77.757195 29 2007-06-01
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
336 338 71 HARYANA YAMUNANAGAR 2.0 6 1 6 1989 NaN ... NaN NaN 1.0 0.0 0.0 matched (3) 30.144167 77.284187 6 1989-06-01
337 339 58 UTTARAKHAND Rudraprayag 1.0 5 1 6 1997 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 30.290751 78.986137 5 1997-06-01
338 341 54 PUNJAB BARNALA 4.0 3 1 6 2006 NaN ... NaN NaN NaN NaN 1.0 matched (3) 30.357227 75.531654 3 2006-06-01
339 342 10018 PUNJAB Fazilka 4.0 3 1 10 2016 NaN ... NaN NaN NaN NaN NaN master only (1) 30.396332 74.031212 3 2016-10-01
340 343 44 PUNJAB MUKTSAR 3.0 3 1 6 1995 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 30.465672 74.536308 3 1995-06-01
341 344 40 PUNJAB FATEHGARH SAHIB 0.0 3 1 6 1992 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 30.648081 76.392090 3 1992-06-01
342 345 45 PUNJAB faridkot NaN 3 1 6 1972 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 30.681202 74.760254 3 1972-06-01
343 346 69 HARYANA PANCHKULA 4.0 6 1 6 1995 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 30.702576 76.870193 6 1995-06-01
344 347 52 PUNJAB SAHIBZADA AJIT SINGH NAGAR 4.0 3 1 6 2006 NaN ... NaN NaN NaN NaN 1.0 matched (3) 30.729118 76.721588 3 2006-06-01
345 348 42 PUNJAB MOGA 3.0 3 1 6 1995 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 30.825523 75.154449 3 1995-06-01
346 349 31 HIMACHAL Solan NaN 2 1 6 1972 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 30.908302 77.099922 2 1972-06-01
347 351 39 PUNJAB SHAHID BHAGAT SINGH NAGAR 3.0 3 1 6 1995 NaN ... NaN NaN NaN 1.0 0.0 matched (3) 31.121868 76.124390 3 1995-06-01
348 352 50 PUNJAB TARN TARAN 4.0 3 1 6 2006 NaN ... NaN NaN NaN NaN 1.0 matched (3) 31.462645 74.934059 3 2006-06-01
349 353 29 HIMACHAL Una NaN 2 1 6 1972 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 31.466011 76.261978 2 1972-06-01
350 354 28 HIMACHAL Hamirpur NaN 2 1 6 1972 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 31.690594 76.519760 2 1972-06-01
351 355 26 HIMACHAL Kulu NaN 2 1 6 1963 NaN ... 1.0 0.0 0.0 0.0 0.0 matched (3) 31.957668 77.111107 2 1963-06-01
352 356 10030 PUNJAB Pathankot 4.0 3 1 10 2016 NaN ... NaN NaN NaN NaN NaN master only (1) 32.273041 75.651665 3 2016-10-01
353 357 22 JAMMU & KASHMIR SAMBA 4.0 1 1 6 2006 NaN ... NaN NaN NaN NaN 1.0 matched (3) 32.563286 75.088333 1 2006-06-01
354 358 17 JAMMU & KASHMIR RAMBAN 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 33.228233 75.241371 1 2007-06-01
355 359 20 JAMMU & KASHMIR REASI 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 33.313744 74.856857 1 2007-06-01
356 360 18 JAMMU & KASHMIR KISHTWAR 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 33.331818 75.764091 1 2007-06-01
357 361 6 JAMMU & KASHMIR Rajauri NaN 1 1 6 1967 NaN ... 1.0 0.0 0.0 0.0 0.0 matched (3) 33.371613 74.315216 1 1967-06-01
358 362 15 JAMMU & KASHMIR KULGAM 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 33.648174 75.016785 1 2007-06-01
359 363 13 JAMMU & KASHMIR SHUPIYAN 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 33.716778 74.831169 1 2007-06-01
360 364 12 JAMMU & KASHMIR Pulwama NaN 1 1 6 1979 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 33.865913 74.899185 1 1979-06-01
361 365 2 JAMMU & KASHMIR Badgam NaN 1 1 6 1979 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 34.028461 74.721931 1 1979-06-01
362 366 11 JAMMU & KASHMIR GANDERBAL 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 34.214771 74.768097 1 2007-06-01
363 367 9 JAMMU & KASHMIR BANDIPORE 5.0 1 1 6 2007 NaN ... NaN NaN NaN NaN 1.0 matched (3) 34.417870 74.647034 1 2007-06-01
364 368 1 JAMMU & KASHMIR Kupwara NaN 1 1 6 1979 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 34.520351 74.259834 1 1979-06-01
365 369 4 JAMMU & KASHMIR Kargil NaN 1 1 6 1979 NaN ... NaN 1.0 0.0 0.0 0.0 matched (3) 34.541218 76.141327 1 1979-06-01

366 rows × 44 columns

In [20]:
# Read District Headquarter Data
filename = 'subdistrict_1.0_Poland.xlsx'
filepath = os.path.join(data_folder, filename)
subdist_df = pd.read_excel(filepath,skiprows=2)
subdist_df = subdist_df[(subdist_df['Latitude'] > 0) & (subdist_df['Longitude'] > 0) & (subdist_df['Year'] > 0)]
subdist_df.loc[subdist_df['Month'].isnull(), 'Month'] = 6
subdist_df.loc[subdist_df['Day'].isnull(), 'Day'] = 30
subdist_df['datetime'] = pd.to_datetime(subdist_df[['Year', 'Month', 'Day']].rename(columns={"Year": "year", "Month": "month", "Day": "day"}))
subdist_df
Out[20]:
Sub District Code Sub District Version Sub District Name District Code Census 2001 Code Census 2011 Code State Day Month Year ... Unnamed: 20 ADDRESS Comment Order Day Order Month Order Year Effective Day Effective Month Effective Year datetime
0 6191 1.0 DRASS 6.0 NaN 0.0 Jammu & Kashmir 21.0 10.0 2014.0 ... 34.429591, 75.752610 SDM Office, Dras,194102 verified near Kargil 21.0 10.0 2014.0 21 10.0 2014.0 2014-10-21
1 6192 1.0 SHAKER CHIKTAN 6.0 NaN 0.0 Jammu & Kashmir 21.0 10.0 2014.0 ... 34.403085, 76.562503 chiktan, 194106 verified near Kargil 21.0 10.0 2014.0 21 10.0 2014.0 2014-10-21
2 6194 1.0 Pahloo 622.0 NaN 0.0 Jammu & Kashmir 21.0 10.0 2014.0 ... 33.636205, 75.048704 Pahloo, 192231 verified in Kulgam 21.0 10.0 2014.0 21 10.0 2014.0 2014-10-21
3 6193 1.0 Qaimoh 622.0 NaN 0.0 Jammu & Kashmir 21.0 10.0 2014.0 ... 33.710428, 75.082495 Office of the Tehsildar Qaimoh\nKaimoh, Qaimoh... verified in Kulgam 21.0 10.0 2014.0 21 10.0 2014.0 2014-10-21
4 6092 1.0 Keegam 625.0 NaN 0.0 Jammu & Kashmir 23.0 7.0 2014.0 ... 33.787493, 74.861325 Keegam Fire Station\nPulwama-Srinagar Rd, Keeg... verified in Shopian 23.0 7.0 2014.0 12 10.0 2015.0 2014-07-23
5 6091 1.0 Keller 625.0 NaN 0.0 Jammu & Kashmir 23.0 7.0 2014.0 ... 33.779816, 74.761497 Kellar Fire Station\nKeller-Shadimarg Rd, Kell... verified in Shopian 23.0 7.0 2014.0 12 10.0 2015.0 2014-07-23
6 5984 1.0 KOTAKPURA 29.0 NaN 0.0 Punjab 15.0 12.0 2011.0 ... 30.583996, 74.819266 SDM Office\nLajpat Park Road, Lajpat Nagar, Ko... verified near Faridkot 15.0 12.0 2011.0 15 12.0 2011.0 2011-12-15
7 6011 1.0 GURUHARSAHAI 31.0 NaN 0.0 Punjab 27.0 7.0 2011.0 ... 30.715229, 74.401418 Guru Har Sahai, Punjab, 152022, India verified near Ferozepur 27.0 7.0 2011.0 27 7.0 2011.0 2011-07-27
8 5996 1.0 DHARAMKOT 38.0 NaN 0.0 Punjab 27.0 7.0 2011.0 ... 30.943272, 75.226779 Tehsil Office\nDharamkot - Fatehgarh Panjtur R... verified near Moga 27.0 7.0 2011.0 27 7.0 2011.0 2011-07-27
9 5993 1.0 BANGA 40.0 NaN 0.0 Punjab 8.0 12.0 2011.0 ... 31.187504, 76.004947 Tehsil Complex\nBanga, Punjab 144505, India\n3... verified near Nawanshahr 8.0 12.0 2011.0 4 7.0 2014.0 2011-12-08
10 6341 1.0 Bagwalipokhar 45.0 NaN 0.0 Uttarakhand 21.0 1.0 2016.0 ... 29.730143, 79.477969 State Bank of India\nAlmora Rd, Bagwalipokhar,... vd 21.0 1.0 2016.0 Gazette Pub Date NaN NaN 2016-01-21
11 6338 1.0 Dhaulchhina 45.0 NaN 0.0 Uttarakhand 3.0 12.0 2013.0 ... 29.673978, 79.781969 State Bank of India\nAlmora - Berinag Rd, Dhau... vd 3.0 12.0 2013.0 Gazette Pub Date NaN NaN 2013-12-03
12 6342 1.0 Dhyari 45.0 NaN 0.0 Uttarakhand 20.0 10.0 2016.0 ... 29.533996, 80.021771 Dhyari\nUttarakhand 263622, India vd 20.0 10.0 2016.0 Gazette Pub Date NaN NaN 2016-10-20
13 6340 1.0 Jalali 45.0 NaN 0.0 Uttarakhand 21.0 1.0 2016.0 ... 29.758849, 79.338489 State Bank of India\nJalali, Uttarakhand 26365... vd 21.0 1.0 2016.0 Gazette Pub Date NaN NaN 2016-01-21
14 6368 1.0 Lamgada 45.0 NaN 0.0 Uttarakhand 21.0 11.0 2013.0 ... 29.505575, 79.717595 post office, lamgara, Uttarakhand 263625, India vd 21.0 11.0 2013.0 Gazette Pub Date NaN NaN 2013-11-21
15 6339 1.0 Machhor 45.0 NaN 0.0 Uttarakhand 24.0 12.0 2014.0 ... 29.601065, 79.241199 Machhor\nUttarakhand 263646, India vd 24.0 12.0 2014.0 Gazette Pub Date NaN NaN 2014-12-24
16 6330 1.0 Syalde 45.0 NaN 0.0 Uttarakhand 7.0 11.0 2013.0 ... 29.822658, 79.210004 Syaldey\nUttarakhand 263661, India vd 7.0 11.0 2013.0 Gazette Pub Date NaN NaN 2013-11-07
17 6331 1.0 Dugnakuri 46.0 NaN 0.0 Uttarakhand 11.0 12.0 2013.0 ... NaN NaN NaN 11.0 12.0 2013.0 Gazette Pub Date NaN NaN 2013-12-11
18 6332 1.0 Kafligair 46.0 NaN 0.0 Uttarakhand 19.0 2.0 2014.0 ... NaN NaN NaN 19.0 2.0 2014.0 Gazette Pub Date NaN NaN 2014-02-19
19 6367 1.0 Shama 46.0 NaN 0.0 Uttarakhand 23.0 10.0 2013.0 ... NaN NaN NaN 23.0 10.0 2013.0 Gazette Pub Date NaN NaN 2013-10-23
20 6345 1.0 Adibadri 47.0 NaN 0.0 Uttarakhand 18.0 1.0 2013.0 ... NaN NaN NaN 18.0 1.0 2013.0 Gazette Pub Date NaN NaN 2013-01-18
21 6347 1.0 Dewal 47.0 NaN 0.0 Uttarakhand 21.0 2.0 2014.0 ... NaN NaN NaN 21.0 2.0 2014.0 17 4.0 2014.0 2014-02-21
22 6343 2.0 Ghaat 47.0 NaN 0.0 Uttarakhand 20.0 10.0 2016.0 ... NaN NaN NaN 20.0 10.0 2016.0 NaN NaN NaN 2016-10-20
23 6346 1.0 Jilasu 47.0 NaN 0.0 Uttarakhand 23.0 12.0 2014.0 ... NaN NaN NaN 23.0 12.0 2014.0 Gazette Pub Date NaN NaN 2014-12-23
24 6344 1.0 Nandprayag 47.0 NaN 0.0 Uttarakhand 20.0 10.0 2016.0 ... NaN NaN NaN 20.0 10.0 2016.0 Gazette Pub Date NaN NaN 2016-10-20
25 6333 1.0 Narayanbagar 47.0 NaN 0.0 Uttarakhand 17.0 4.0 2015.0 ... NaN NaN NaN 17.0 4.0 2015.0 Gazette Pub Date NaN NaN 2015-04-17
26 6377 1.0 Barakot 48.0 NaN 0.0 Uttarakhand 20.0 1.0 2014.0 ... NaN NaN NaN 20.0 1.0 2014.0 Gazette Pub Date NaN NaN 2014-01-20
27 6348 1.0 Manch 48.0 NaN 0.0 Uttarakhand 3.0 9.0 2015.0 ... NaN NaN NaN 3.0 9.0 2015.0 Gazette Pub Date NaN NaN 2015-09-03
28 6349 1.0 Pulla Gumdesh 48.0 NaN 0.0 Uttarakhand 31.0 1.0 2015.0 ... NaN NaN NaN 31.0 1.0 2015.0 Gazette Pub Date NaN NaN 2015-01-31
29 6335 1.0 Doiwala 49.0 NaN 0.0 Uttarakhand 14.0 2.0 2015.0 ... NaN NaN NaN 14.0 2.0 2015.0 Gazette Pub Date NaN NaN 2015-02-14
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
521 5953 1.0 veppur 570.0 NaN 0.0 Tamil Nadu 1.0 1.0 2010.0 ... 11.532831, 79.123135 Veppur Taluk Office\nNear Veppur Bus Stand, Ve... NaN 1.0 1.0 2010.0 2 5.0 2014.0 2010-01-01
522 5994 1.0 Athoor 572.0 NaN 0.0 Tamil Nadu 13.0 7.0 2010.0 ... 10.287104, 77.849346 Athoor Taluk Office\nMatha Kovil, Athoor, Tami... VD 13.0 7.0 2010.0 14 7.0 2014.0 2010-07-13
523 5980 1.0 DindigulEast 572.0 NaN 0.0 Tamil Nadu 11.0 5.0 2011.0 ... NaN NaN NaN 11.0 5.0 2011.0 3 5.0 2014.0 2011-05-11
524 5979 1.0 DindigulWest 572.0 NaN 0.0 Tamil Nadu 11.0 5.0 2011.0 ... NaN NaN NaN 11.0 5.0 2011.0 3 5.0 2014.0 2011-05-11
525 5969 1.0 Anthiyur 573.0 NaN 0.0 Tamil Nadu 9.0 6.0 2010.0 ... 11.577303, 77.587836 Tahsildar Office\nAnthiyur, Erode, SH-175, Bha... VD 9.0 6.0 2010.0 3 5.0 2014.0 2010-06-09
526 5958 1.0 Tiruporur 574.0 NaN 0.0 Tamil Nadu 21.0 11.0 2012.0 ... 12.719599, 80.185779 Thiruporur Tashildar Office\nThandalam, Tamil ... VD 21.0 11.0 2012.0 3 5.0 2014.0 2012-11-21
527 5985 1.0 Manmangalam 576.0 NaN 0.0 Tamil Nadu 30.0 10.0 2010.0 ... 11.017354, 78.069685 Taluk Office, Manmangalam\nKarur, Tamil Nadu 6... VD 30.0 10.0 2010.0 3 6.0 2014.0 2010-10-30
528 5960 1.0 MADURAI EAST 578.0 NaN 0.0 Tamil Nadu 2.0 2.0 2011.0 ... NaN NaN NaN 2.0 2.0 2011.0 3 5.0 2014.0 2011-02-02
529 5961 1.0 MADURAI WEST 578.0 NaN 0.0 Tamil Nadu 2.0 2.0 2011.0 ... NaN NaN NaN 2.0 2.0 2011.0 3 5.0 2014.0 2011-02-02
530 5962 1.0 THIRUPPARANKUNDRAM 578.0 NaN 0.0 Tamil Nadu 10.0 5.0 2011.0 ... 9.879440, 78.066798 Revenue Inspector Office\n296, Big Car St, Thi... vd 10.0 5.0 2011.0 4 5.0 2014.0 2011-05-10
532 5952 1.0 Alathur1 581.0 NaN 0.0 Tamil Nadu 1.0 1.0 2010.0 ... NaN NaN NaN 1.0 1.0 2010.0 2 5.0 2014.0 2010-01-01
534 5976 1.0 Kunnam 581.0 NaN 0.0 Tamil Nadu 16.0 6.0 2010.0 ... 11.237746, 79.015517 Taluk Office\nKunnam, Tamil Nadu 621708, India vd 16.0 6.0 2010.0 3 5.0 2014.0 2010-06-16
535 5973 1.0 Perambalur 581.0 NaN 0.0 Tamil Nadu 14.0 4.0 2010.0 ... 11.239227, 78.864004 Taluk Office\nMadharasa Rd, Super Nagar, Peram vd 14.0 4.0 2010.0 3 5.0 2014.0 2010-04-14
536 5974 1.0 Veppanthattai 581.0 NaN 0.0 Tamil Nadu 19.0 8.0 2010.0 ... 11.337563, 78.821046 Taluk Office\nAttur - Perambalur Rd, Veppanthatt vd 19.0 8.0 2010.0 3 5.0 2014.0 2010-08-19
537 5975 1.0 Veppur 581.0 NaN 0.0 Tamil Nadu 15.0 9.0 2010.0 ... 11.316495, 79.063792 Veppur Panchayath Union office\nKunnam-Veppur ... vd 15.0 9.0 2010.0 3 5.0 2014.0 2010-09-15
538 5970 1.0 Salem South 584.0 NaN 0.0 Tamil Nadu 17.0 8.0 2011.0 ... NaN NaN NaN 17.0 8.0 2011.0 3 5.0 2014.0 2011-08-17
539 5971 1.0 Salem West 584.0 NaN 0.0 Tamil Nadu 17.0 8.0 2011.0 ... NaN NaN NaN 17.0 8.0 2011.0 3 5.0 2014.0 2011-08-17
540 5995 1.0 Thiruppathur 585.0 NaN 0.0 Tamil Nadu 9.0 7.0 2009.0 ... 10.112749, 78.596185 Taluk Office\nTirupattur, Tamil Nadu 630211, I... vd 9.0 7.0 2009.0 21 7.0 2014.0 2009-07-09
541 5965 1.0 THIRUPPUVANAM 585.0 NaN 0.0 Tamil Nadu 4.0 3.0 2010.0 ... 9.823667, 78.260894 Tiruppuvanam Town Police Station\nMadurai-Rame... vd 4.0 3.0 2010.0 3 5.0 2014.0 2010-03-04
542 5966 1.0 Budalur 586.0 NaN 0.0 Tamil Nadu 7.0 4.0 2011.0 ... 10.795490, 78.975864 Budalur Taluk Office\nBudalur taluk office, Ag... vd 7.0 4.0 2011.0 3 5.0 2014.0 2011-04-07
543 5981 1.0 Maduravoyal 589.0 NaN 0.0 Tamil Nadu 10.0 5.0 2011.0 ... 13.040834, 80.166231 Maduravoyal Taluk Office\n600116, Ganesh Nagar... vd 10.0 5.0 2011.0 4 5.0 2014.0 2011-05-10
545 5954 1.0 MARUNGAPURI 591.0 NaN 0.0 Tamil Nadu 1.0 1.0 2010.0 ... 10.430154, 78.438713 Sub Taluk Office Marungapuri\nYagapuram, Tiruc... vd 1.0 1.0 2010.0 2 5.0 2014.0 2010-01-01
546 5959 1.0 Kalasapakkam 593.0 NaN 0.0 Tamil Nadu 4.0 1.0 2011.0 ... 12.432946, 79.103505 Taluk Office, Kalasapakkam\nKalasapakkam, Tami... vd 4.0 1.0 2011.0 3 5.0 2014.0 2011-01-04
547 5964 1.0 Anaicut 595.0 NaN 0.0 Tamil Nadu 3.0 2.0 2010.0 ... 12.867096, 78.985748 Taluk Office\nAnaicut, Tamil Nadu 632101, India vd 3.0 2.0 2010.0 3 5.0 2014.0 2010-02-03
548 5963 1.0 Natrampalli 595.0 NaN 0.0 Tamil Nadu 3.0 2.0 2010.0 ... 12.595664, 78.516072 Taluk Office Natrampalli\nNatrampalli, Tamil N... vd 3.0 2.0 2010.0 3 5.0 2014.0 2010-02-03
549 5977 1.0 Chinnasalem 596.0 NaN 0.0 Tamil Nadu 16.0 8.0 2011.0 ... 11.632347, 78.868271 Taluk Office\nChinnasalem, Tamil Nadu 606201, I vd 16.0 8.0 2011.0 3 5.0 2014.0 2011-08-16
550 5978 1.0 Vikravandi 596.0 NaN 0.0 Tamil Nadu 16.0 8.0 2011.0 ... 12.040962, 79.544248 Taluk Office\nnear, Police Station St, Vikrava... vd 16.0 8.0 2011.0 3 5.0 2014.0 2011-08-16
551 5983 1.0 Jayamkondam 610.0 NaN 0.0 Tamil Nadu 16.0 6.0 2010.0 ... 11.210304, 79.364723 Jayankondam Taluk Office\nJayamkondan, Tamil N... vd 16.0 6.0 2010.0 26 5.0 2014.0 2010-06-16
552 5967 1.0 Tiruppur North 634.0 NaN 0.0 Tamil Nadu 10.0 3.0 2011.0 ... NaN NaN NaN 10.0 3.0 2011.0 3 5.0 2014.0 2011-03-10
553 5968 1.0 Tiruppursouth 634.0 NaN 0.0 Tamil Nadu 10.0 3.0 2011.0 ... NaN NaN NaN 10.0 3.0 2011.0 3 5.0 2014.0 2011-03-10

520 rows × 30 columns

In [21]:
#Change District Headquarter dataframe to geospatial and check by exporting to shp
geometry = [Point(xy) for xy in zip(disthq_df.Longitude, disthq_df.Latitude)]
crs = {'init': 'epsg:4326'}
disthq_geo_df = gpd.GeoDataFrame(disthq_df, crs=crs, geometry=geometry)
#geo_df.to_file(driver='ESRI Shapefile', filename=out_filepath.split('.')[0]+'.shp')

#Change Sub District dataframe to geospatial and check by exporting to shp
geometry = [Point(xy) for xy in zip(subdist_df.Longitude, subdist_df.Latitude)]
crs = {'init': 'epsg:4326'}
subdist_geo_df = gpd.GeoDataFrame(subdist_df, crs=crs, geometry=geometry)
#geo_df.to_file(driver='ESRI Shapefile', filename=out_filepath.split('.')[0]+'.shp')
In [22]:
# Spatial Join
disthq_geo_df = gpd.sjoin(disthq_geo_df, ac_gdf, how='inner', op='within')
subdist_geo_df = gpd.sjoin(subdist_geo_df, ac_gdf, how='inner', op='within')
In [25]:
delta_year = None
#Get all variables
variables = [
    'new_disthq',
    'new_disthq1',
    'new_disthq2',
    'new_disthq3',
    'new_disthq4',
    'new_disthq5',
    'new_subdist',
    'new_subdist1',
    'new_subdist2',
    'new_subdist3',
    'new_subdist4',
    'new_subdist5'
]
def get_ac_variables(row):
    df = pd.DataFrame(row).T
    next_elect = acdf[(acdf['state_code'] == row['state_code']) & (acdf['constituency_no'] == row['constituency_no']) & (acdf['datetime'] > row['datetime'])][0:1]
    if len(next_elect) < 1:
        next_elect = df.copy()
        next_elect['datetime'] = next_elect['datetime'] + pd.DateOffset(years=5)
    #print(next_elect)
    delta_year = math.ceil((next_elect['datetime'].dt.year - row['datetime'].year)/5)
    #print(delta_year)
    df1 = disthq_geo_df[(disthq_geo_df['ST_CODE'] == row['state_code']) & (disthq_geo_df['AC_NO'] == row['constituency_no']) & (disthq_geo_df['datetime'] > row['datetime']) & (disthq_geo_df['datetime'] < next_elect['datetime'].values[0])]
    new_disthq = df1.shape[0]
    new_disthq1 = df1[df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+0))].shape[0]
    new_disthq2 = df1[(df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+1))) & (df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+0)))].shape[0]
    new_disthq3 = df1[(df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+2))) & (df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+1)))].shape[0]
    new_disthq4 = df1[(df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+3))) & (df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+2)))].shape[0]
    new_disthq5 = df1[df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+3))].shape[0]

    df2 = subdist_geo_df[(subdist_geo_df['ST_CODE'] == row['state_code']) & (subdist_geo_df['AC_NO'] == row['constituency_no']) & (subdist_geo_df['datetime'] > row['datetime']) & (subdist_geo_df['datetime'] < next_elect['datetime'].values[0])]
    new_subdist = df2.shape[0]
    new_subdist1 = df2[df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+0))].shape[0]
    new_subdist2 = df2[(df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+1))) & (df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+0)))].shape[0]
    new_subdist3 = df2[(df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+2))) & (df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+1)))].shape[0]
    new_subdist4 = df2[(df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+3))) & (df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+2)))].shape[0]
    new_subdist5 = df2[df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+3))].shape[0]
    
    return new_disthq, new_disthq1, new_disthq2, new_disthq3, new_disthq4, new_disthq5, new_subdist, new_subdist1, new_subdist2, new_subdist3, new_subdist4, new_subdist5
#Apply This Function
acdf[variables] = acdf.apply(get_ac_variables, axis=1, result_type='expand')
acdf.sort_values(axis=0, by='new_subdist')
Out[25]:
state_name state_code constituency_no year month day datetime OBJECTID ST_CODE ST_NAME ... new_disthq2 new_disthq3 new_disthq4 new_disthq5 new_subdist new_subdist1 new_subdist2 new_subdist3 new_subdist4 new_subdist5
0 Jammu & Kashmir 1 1 2008 12 1 2008-12-01 1 1 JAMMU & KASHMIR ... 0 0 0 0 0 0 0 0 0 0
5500 Madhya Pradesh 23 214 2008 12 1 2008-12-01 22 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5499 Madhya Pradesh 23 213 2013 12 1 2013-12-01 22 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5498 Madhya Pradesh 23 213 2008 12 1 2008-12-01 22 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5497 Madhya Pradesh 23 212 2013 12 1 2013-12-01 22 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5496 Madhya Pradesh 23 212 2008 12 1 2008-12-01 22 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5495 Madhya Pradesh 23 211 2013 12 1 2013-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5494 Madhya Pradesh 23 211 2008 12 1 2008-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5493 Madhya Pradesh 23 210 2013 12 1 2013-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5492 Madhya Pradesh 23 210 2008 12 1 2008-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5491 Madhya Pradesh 23 209 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5490 Madhya Pradesh 23 209 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5489 Madhya Pradesh 23 204 2013 12 1 2013-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5488 Madhya Pradesh 23 204 2008 12 1 2008-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5501 Madhya Pradesh 23 214 2013 12 1 2013-12-01 22 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5487 Madhya Pradesh 23 203 2013 12 1 2013-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5485 Madhya Pradesh 23 202 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5484 Madhya Pradesh 23 202 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5483 Madhya Pradesh 23 201 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5482 Madhya Pradesh 23 201 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5481 Madhya Pradesh 23 200 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5480 Madhya Pradesh 23 200 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5479 Madhya Pradesh 23 199 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5478 Madhya Pradesh 23 199 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5477 Madhya Pradesh 23 198 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5476 Madhya Pradesh 23 198 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5475 Madhya Pradesh 23 197 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5474 Madhya Pradesh 23 197 2008 12 1 2008-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5473 Madhya Pradesh 23 196 2013 12 1 2013-12-01 25 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
5486 Madhya Pradesh 23 203 2008 12 1 2008-12-01 26 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
6611 Andhra Pradesh 28 89 2014 5 1 2014-05-01 13 28 ANDHRA PRADESH ... 0 0 0 0 2 0 0 2 0 0
3397 Tripura 16 37 2013 2 1 2013-02-01 2 16 TRIPURA ... 0 0 1 0 2 2 0 0 0 0
6629 Andhra Pradesh 28 98 2014 5 1 2014-05-01 14 28 ANDHRA PRADESH ... 0 1 0 0 2 0 0 2 0 0
2978 Arunachal Pradesh 12 54 2014 5 1 2014-05-01 2 12 ARUNACHAL PRADESH ... 0 0 0 0 2 2 0 0 0 0
6465 Andhra Pradesh 28 16 2014 5 1 2014-05-01 5 28 ANDHRA PRADESH ... 0 0 0 0 2 0 0 2 0 0
6597 Andhra Pradesh 28 82 2014 5 1 2014-05-01 12 28 ANDHRA PRADESH ... 0 0 0 0 2 0 0 2 0 0
3411 Tripura 16 44 2013 2 1 2013-02-01 2 16 TRIPURA ... 0 0 0 0 2 2 0 0 0 0
854 Haryana 6 68 2009 10 1 2009-10-01 8 6 HARYANA ... 0 0 0 0 2 0 0 0 0 2
6461 Andhra Pradesh 28 14 2014 5 1 2014-05-01 5 28 ANDHRA PRADESH ... 0 0 0 0 2 0 0 2 0 0
6633 Andhra Pradesh 28 100 2014 5 1 2014-05-01 15 28 ANDHRA PRADESH ... 0 0 0 0 2 0 0 2 0 0
6625 Andhra Pradesh 28 96 2014 5 1 2014-05-01 14 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
6451 Andhra Pradesh 28 9 2014 5 1 2014-05-01 1 28 ANDHRA PRADESH ... 0 1 0 0 3 0 0 3 0 0
6647 Andhra Pradesh 28 107 2014 5 1 2014-05-01 15 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
6523 Andhra Pradesh 28 45 2014 5 1 2014-05-01 7 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
2904 Arunachal Pradesh 12 18 2014 5 1 2014-05-01 1 12 ARUNACHAL PRADESH ... 0 0 0 0 3 2 0 1 0 0
6535 Andhra Pradesh 28 51 2014 5 1 2014-05-01 10 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
2910 Arunachal Pradesh 12 21 2014 5 1 2014-05-01 1 12 ARUNACHAL PRADESH ... 0 0 0 0 3 3 0 0 0 0
2898 Arunachal Pradesh 12 15 2014 5 1 2014-05-01 1 12 ARUNACHAL PRADESH ... 0 0 0 0 3 3 0 0 0 0
2896 Arunachal Pradesh 12 14 2014 5 1 2014-05-01 1 12 ARUNACHAL PRADESH ... 0 0 0 0 3 3 0 0 0 0
6513 Andhra Pradesh 28 40 2014 5 1 2014-05-01 6 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
871 Haryana 6 76 2014 10 1 2014-10-01 9 6 HARYANA ... 0 0 0 0 3 0 0 3 0 0
2894 Arunachal Pradesh 12 13 2014 5 1 2014-05-01 1 12 ARUNACHAL PRADESH ... 0 0 0 0 3 3 0 0 0 0
1357 Rajasthan 8 123 2013 12 1 2013-12-01 16 8 RAJASTHAN ... 0 0 0 0 3 0 0 1 2 0
6503 Andhra Pradesh 28 35 2014 5 1 2014-05-01 5 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
6471 Andhra Pradesh 28 19 2014 5 1 2014-05-01 4 28 ANDHRA PRADESH ... 0 0 0 0 3 0 0 3 0 0
590 Uttarakhand 5 6 2012 3 1 2012-03-01 2 5 UTTARKHAND ... 0 0 0 0 3 1 0 1 0 1
670 Uttarakhand 5 46 2012 3 1 2012-03-01 3 5 UTTARKHAND ... 0 0 0 0 3 0 2 0 0 1
6469 Andhra Pradesh 28 18 2014 5 1 2014-05-01 4 28 ANDHRA PRADESH ... 0 0 0 0 5 0 0 5 0 0
2946 Arunachal Pradesh 12 38 2014 5 1 2014-05-01 2 12 ARUNACHAL PRADESH ... 0 0 0 0 5 5 0 0 0 0
2902 Arunachal Pradesh 12 17 2014 5 1 2014-05-01 1 12 ARUNACHAL PRADESH ... 0 0 0 0 8 8 0 0 0 0

8358 rows × 32 columns

In [26]:
# Output to CSV
acdf.to_csv(os.path.join(output_folder, "Assembly_Constituencies_Variables_Categories.csv"), encoding='utf-8', index=False)
In [ ]:
In [88]:
In [ ]: