In [143]:
# Import Libraries
import pandas as pd
import geopandas as gpd
from shapely.geometry import Point, Polygon
import lxml
import os
import glob
import time
import datetime
import json
import itertools
import math
In [2]:
# Set Output Folder
output_folder = os.path.abspath("output")
if not os.path.exists(output_folder):
    os.makedirs(output_folder)
In [3]:
# Import data folders
data_folder = os.path.abspath("data")
In [4]:
# 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['dyear'] = 0
acdf.loc[(acdf['month'] > 9) | (acdf['year'] == 2008), 'dyear'] = 1
acdf['dyear'] = acdf['year'] + acdf['dyear']
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[:-5]]
acdf
Out[4]:
state_name state_code constituency_no year month day dyear datetime OBJECTID ST_CODE ST_NAME DT_CODE DIST_NAME AC_NO AC_NAME PC_NO PC_NAME
0 Jammu & Kashmir 1 1 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 1 KARNAH 1 BARAMULLA
1 Jammu & Kashmir 1 1 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 1 KARNAH 1 BARAMULLA
2 Jammu & Kashmir 1 2 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 2 KUPWARA 1 BARAMULLA
3 Jammu & Kashmir 1 2 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 2 KUPWARA 1 BARAMULLA
4 Jammu & Kashmir 1 3 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 3 LOLAB 1 BARAMULLA
5 Jammu & Kashmir 1 3 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 3 LOLAB 1 BARAMULLA
6 Jammu & Kashmir 1 4 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 4 HANDWARA 1 BARAMULLA
7 Jammu & Kashmir 1 4 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 4 HANDWARA 1 BARAMULLA
8 Jammu & Kashmir 1 5 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 5 LANGATE 1 BARAMULLA
9 Jammu & Kashmir 1 5 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 5 LANGATE 1 BARAMULLA
10 Jammu & Kashmir 1 6 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 6 URI 1 BARAMULLA
11 Jammu & Kashmir 1 6 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 6 URI 1 BARAMULLA
12 Jammu & Kashmir 1 7 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 7 RAFIABAD 1 BARAMULLA
13 Jammu & Kashmir 1 7 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 7 RAFIABAD 1 BARAMULLA
14 Jammu & Kashmir 1 8 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 8 SOPORE 1 BARAMULLA
15 Jammu & Kashmir 1 8 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 8 SOPORE 1 BARAMULLA
16 Jammu & Kashmir 1 9 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 9 GUREZ 1 BARAMULLA
17 Jammu & Kashmir 1 9 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 9 GUREZ 1 BARAMULLA
18 Jammu & Kashmir 1 10 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 10 BANDIPORA 1 BARAMULLA
19 Jammu & Kashmir 1 10 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 10 BANDIPORA 1 BARAMULLA
20 Jammu & Kashmir 1 11 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 11 SONAWARI 1 BARAMULLA
21 Jammu & Kashmir 1 11 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 11 SONAWARI 1 BARAMULLA
22 Jammu & Kashmir 1 12 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 12 SANGRAMA 1 BARAMULLA
23 Jammu & Kashmir 1 12 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 12 SANGRAMA 1 BARAMULLA
24 Jammu & Kashmir 1 13 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 13 BARAMULA 1 BARAMULLA
25 Jammu & Kashmir 1 13 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 13 BARAMULA 1 BARAMULLA
26 Jammu & Kashmir 1 14 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 14 GULMARG 1 BARAMULLA
27 Jammu & Kashmir 1 14 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 14 GULMARG 1 BARAMULLA
28 Jammu & Kashmir 1 15 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 15 PATTAN 1 BARAMULLA
29 Jammu & Kashmir 1 15 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 15 PATTAN 1 BARAMULLA
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
8328 Puducherry 34 16 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 16 Orleampeth 1 PONDICHERRY
8329 Puducherry 34 16 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 16 Orleampeth 1 PONDICHERRY
8330 Puducherry 34 17 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 17 Nellithope 1 PONDICHERRY
8331 Puducherry 34 17 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 17 Nellithope 1 PONDICHERRY
8332 Puducherry 34 18 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 18 Mudaliarpet 1 PONDICHERRY
8333 Puducherry 34 18 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 18 Mudaliarpet 1 PONDICHERRY
8334 Puducherry 34 19 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 19 Ariankuppam 1 PONDICHERRY
8335 Puducherry 34 19 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 19 Ariankuppam 1 PONDICHERRY
8336 Puducherry 34 20 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 20 Manavely 1 PONDICHERRY
8337 Puducherry 34 20 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 20 Manavely 1 PONDICHERRY
8338 Puducherry 34 21 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 21 Embalam (SC) 1 PONDICHERRY
8339 Puducherry 34 21 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 21 Embalam (SC) 1 PONDICHERRY
8340 Puducherry 34 22 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 22 Nettapakkam (SC) 1 PONDICHERRY
8341 Puducherry 34 22 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 22 Nettapakkam (SC) 1 PONDICHERRY
8342 Puducherry 34 23 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 23 Bahour 1 PONDICHERRY
8343 Puducherry 34 23 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 23 Bahour 1 PONDICHERRY
8344 Puducherry 34 24 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 24 Nedungadu (SC) 1 PONDICHERRY
8345 Puducherry 34 24 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 24 Nedungadu (SC) 1 PONDICHERRY
8346 Puducherry 34 25 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 25 Thirunallar 1 PONDICHERRY
8347 Puducherry 34 25 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 25 Thirunallar 1 PONDICHERRY
8348 Puducherry 34 26 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 26 Karaikal North 1 PONDICHERRY
8349 Puducherry 34 26 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 26 Karaikal North 1 PONDICHERRY
8350 Puducherry 34 27 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 27 Karaikal South 1 PONDICHERRY
8351 Puducherry 34 27 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 27 Karaikal South 1 PONDICHERRY
8352 Puducherry 34 28 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 28 Neravy- T.R. Pattin 1 PONDICHERRY
8353 Puducherry 34 28 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 28 Neravy- T.R. Pattin 1 PONDICHERRY
8354 Puducherry 34 29 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 3.0 MAHE 29 Mahe 1 PONDICHERRY
8355 Puducherry 34 29 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 3.0 MAHE 29 Mahe 1 PONDICHERRY
8356 Puducherry 34 30 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 1.0 YANAM 30 Yanam 1 PONDICHERRY
8357 Puducherry 34 30 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 1.0 YANAM 30 Yanam 1 PONDICHERRY

8358 rows × 17 columns

In [134]:
#Read Pincode Data
df_list = []
file = os.path.join(data_folder, 'BranchPO', 'BranchPO.shp')
df = gpd.read_file(file)
df['pincode'] = df['PINCODE']
df_list.append(df)
file = os.path.join(data_folder, 'GeneralPO', 'GeneralPO.shp')
df = gpd.read_file(file)
df_list.append(df)
file = os.path.join(data_folder, 'HeadPO', 'HeadPO.shp')
df = gpd.read_file(file)
df_list.append(df)
file = os.path.join(data_folder, 'SubPO', 'SubPO.shp')
df = gpd.read_file(file)
df_list.append(df)
df = pd.concat(df_list)
df['longitude'] = df.geometry.x
df['latitude'] = df.geometry.y
# Spatial Join
df = gpd.sjoin(df, ac_gdf, how='inner', op='within')
pincode_geodf = df[['pincode', 'AC_NO', 'ST_CODE']]
pincode_geodf = pincode_geodf.groupby(['pincode']).first().reset_index()
pincode_geodf

C:\Users\sandyjones\AppData\Local\conda\conda\envs\geo\lib\site-packages\ipykernel_launcher.py:16: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.

To accept the future behavior, pass 'sort=False'.

To retain the current behavior and silence the warning, pass 'sort=True'.

  app.launch_new_instance()
Out[134]:
pincode AC_NO ST_CODE
0 0.0 21 22
1 68959.0 92 32
2 82421.0 226 10
3 110001.0 84 6
4 110002.0 20 7
5 110003.0 40 7
6 110004.0 40 7
7 110005.0 23 7
8 110006.0 20 7
9 110007.0 19 7
10 110008.0 24 7
11 110009.0 18 7
12 110010.0 39 7
13 110011.0 40 7
14 110012.0 39 7
15 110013.0 40 7
16 110014.0 41 7
17 110015.0 39 7
18 110016.0 43 7
19 110017.0 40 7
20 110018.0 31 7
21 110019.0 53 7
22 110020.0 51 7
23 110021.0 40 7
24 110022.0 44 7
25 110023.0 40 7
26 110024.0 41 7
27 110025.0 51 7
28 110026.0 25 7
29 110027.0 29 7
... ... ... ...
17953 854315.0 56 10
17954 854316.0 46 10
17955 854317.0 32 19
17956 854318.0 54 10
17957 854325.0 49 10
17958 854326.0 57 10
17959 854327.0 49 10
17960 854328.0 48 10
17961 854329.0 56 10
17962 854330.0 49 10
17963 854331.0 49 10
17964 854332.0 49 10
17965 854333.0 58 10
17966 854334.0 46 10
17967 854335.0 46 10
17968 854336.0 55 10
17969 854337.0 164 9
17970 854339.0 45 10
17971 854340.0 45 10
17972 855101.0 52 10
17973 855102.0 35 19
17974 855105.0 56 10
17975 855107.0 30 19
17976 855108.0 55 10
17977 855113.0 65 10
17978 855114.0 64 10
17979 855115.0 55 10
17980 855116.0 54 23
17981 855117.0 29 19
17982 855456.0 11 10

17983 rows × 3 columns

In [141]:
# READ STOCKS DATA
stocks_file = os.path.join(data_folder,  'Stock_price_data.xlsx')
df = pd.read_excel(stocks_file, skiprows=3)
df = df[1:]
columns = df.columns
_columns = []
for column in columns:
    column = str(column)
    column = column.split('.')[0]
    _columns.append(column)
df.columns = _columns
_df = df[df.columns[4:5]]
_df.columns = ['pincode']
# Average high and low price for same date
df1 = pd.read_excel(stocks_file, skiprows=2)
dates = pd.DataFrame(df1[0:1].T[5:][0].unique()).values
for date in dates:
    date = str(date).split('.')[0][2:].replace('T', ' ')
    i = 0
    while i < 4:
        _date = date.split(' ')[0]
        '''
        if i > 0:
            _date = _date + "." + str(i)
        '''
        i+=1
        _df[_date] = df[date].mean(axis=1)
        #print(_date)
#Group the dataframe on pincode
_df = _df[_df['pincode'].notnull()]
_df = _df.groupby(['pincode']).mean()#.reset_index()
#unpivot the table 
_df = _df.unstack().reset_index(name='value')
_df = _df[_df['value'].notnull()]
_df = pd.merge(_df,pincode_geodf,on=['pincode'],  how='inner')
_df['year'] = _df['level_0'].str.split('-').str[0]
_df['month'] = _df['level_0'].str.split('-').str[1]
_df['day'] = _df['level_0'].str.split('-').str[2]
_df['datetime'] = pd.to_datetime(_df[['year', 'month', 'day']])
data_df = _df
data_df

C:\Users\sandyjones\AppData\Local\conda\conda\envs\geo\lib\site-packages\ipykernel_launcher.py:27: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
Out[141]:
level_0 pincode value AC_NO ST_CODE year month day datetime
0 1992-03-31 110001 116.160714 84 6 1992 03 31 1992-03-31
1 1992-09-30 110001 74.615385 84 6 1992 09 30 1992-09-30
2 1993-03-31 110001 53.566667 84 6 1993 03 31 1993-03-31
3 1993-06-30 110001 57.133333 84 6 1993 06 30 1993-06-30
4 1993-09-30 110001 68.277778 84 6 1993 09 30 1993-09-30
5 1994-03-31 110001 74.625000 84 6 1994 03 31 1994-03-31
6 1994-06-30 110001 84.300000 84 6 1994 06 30 1994-06-30
7 1994-09-30 110001 76.485000 84 6 1994 09 30 1994-09-30
8 1995-03-31 110001 62.004310 84 6 1995 03 31 1995-03-31
9 1995-06-30 110001 51.728526 84 6 1995 06 30 1995-06-30
10 1996-09-30 110001 35.673529 84 6 1996 09 30 1996-09-30
11 1996-12-31 110001 57.800000 84 6 1996 12 31 1996-12-31
12 1997-03-31 110001 38.287500 84 6 1997 03 31 1997-03-31
13 1997-06-30 110001 37.389000 84 6 1997 06 30 1997-06-30
14 1997-09-30 110001 34.833796 84 6 1997 09 30 1997-09-30
15 1997-12-31 110001 37.758333 84 6 1997 12 31 1997-12-31
16 1998-03-31 110001 39.910417 84 6 1998 03 31 1998-03-31
17 1998-06-30 110001 40.184375 84 6 1998 06 30 1998-06-30
18 1998-09-30 110001 37.945238 84 6 1998 09 30 1998-09-30
19 1998-12-31 110001 39.779545 84 6 1998 12 31 1998-12-31
20 1999-03-31 110001 43.716667 84 6 1999 03 31 1999-03-31
21 1999-06-30 110001 48.571023 84 6 1999 06 30 1999-06-30
22 1999-09-30 110001 51.740833 84 6 1999 09 30 1999-09-30
23 2000-03-31 110001 218.373438 84 6 2000 03 31 2000-03-31
24 2000-06-30 110001 234.834500 84 6 2000 06 30 2000-06-30
25 2001-12-31 110001 74.057031 84 6 2001 12 31 2001-12-31
26 2002-09-30 110001 45.630603 84 6 2002 09 30 2002-09-30
27 2002-12-31 110001 52.711538 84 6 2002 12 31 2002-12-31
28 2003-03-31 110001 46.703448 84 6 2003 03 31 2003-03-31
29 2003-06-30 110001 57.289609 84 6 2003 06 30 2003-06-30
... ... ... ... ... ... ... ... ... ...
55262 2016-09-30 226004 243.900000 313 9 2016 09 30 2016-09-30
55263 2017-03-31 226004 300.950000 313 9 2017 03 31 2017-03-31
55264 2015-12-31 110063 17.180000 26 7 2015 12 31 2015-12-31
55265 2017-03-31 110063 49.730000 26 7 2017 03 31 2017-03-31
55266 2015-12-31 144008 142.000000 36 3 2015 12 31 2015-12-31
55267 2016-09-30 144008 110.250000 36 3 2016 09 30 2016-09-30
55268 2015-12-31 422013 10.850000 124 27 2015 12 31 2015-12-31
55269 2016-03-31 422013 11.850000 124 27 2016 03 31 2016-03-31
55270 2015-12-31 500048 19.050000 60 28 2015 12 31 2015-12-31
55271 2016-03-31 500048 11.760000 60 28 2016 03 31 2016-03-31
55272 2016-03-31 110078 8.380000 34 7 2016 03 31 2016-03-31
55273 2016-06-30 110078 3.050000 34 7 2016 06 30 2016-06-30
55274 2016-09-30 110078 1.945000 34 7 2016 09 30 2016-09-30
55275 2017-03-31 110078 1.985000 34 7 2017 03 31 2017-03-31
55276 2016-03-31 110081 12.500000 7 7 2016 03 31 2016-03-31
55277 2017-03-31 110081 38.600000 7 7 2017 03 31 2017-03-31
55278 2016-06-30 302023 48.000000 46 8 2016 06 30 2016-06-30
55279 2016-09-30 302023 62.750000 46 8 2016 09 30 2016-09-30
55280 2016-09-30 396050 19.300000 178 24 2016 09 30 2016-09-30
55281 2016-09-30 641654 333.500000 112 33 2016 09 30 2016-09-30
55282 2017-03-31 641654 430.375000 112 33 2017 03 31 2017-03-31
55283 2017-03-31 303901 39.150000 58 8 2017 03 31 2017-03-31
55284 2017-03-31 360311 13.770000 74 24 2017 03 31 2017-03-31
55285 2017-03-31 361002 65.400000 78 24 2017 03 31 2017-03-31
55286 2017-03-31 361005 23.700000 79 24 2017 03 31 2017-03-31
55287 2017-03-31 394540 388.800000 156 24 2017 03 31 2017-03-31
55288 2017-03-31 431213 20.425000 106 27 2017 03 31 2017-03-31
55289 2017-03-31 490001 34.400000 63 22 2017 03 31 2017-03-31
55290 2017-03-31 531021 517.900000 151 28 2017 03 31 2017-03-31
55291 2017-03-31 700055 25.350000 117 19 2017 03 31 2017-03-31

55292 rows × 9 columns

In [151]:
#Get all variables
def get_ac_variables(row):
    #print(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 = pd.DataFrame(row).T
        next_elect['dyear'] = next_elect['dyear'] + 5
        next_elect['datetime'] = next_elect['datetime'] + pd.DateOffset(years=5)
    #print(next_elect)
    delta_year = math.floor((next_elect['datetime'].dt.year - row['datetime'].year)/5)
    if not delta_year:
        delta_year = 1
    #print(delta_year)
    df1 = data_df[(data_df['ST_CODE'] == row['state_code']) & (data_df['AC_NO'] == row['constituency_no']) & (data_df['datetime'] >= row['datetime']) & (data_df['datetime'] < next_elect['datetime'].values[0])]
    return df1['value'].mean()
#Apply This Function
acdf['avg_share_price'] = acdf.apply(get_ac_variables, axis=1)
acdf
Out[151]:
state_name state_code constituency_no year month day dyear datetime OBJECTID ST_CODE ST_NAME DT_CODE DIST_NAME AC_NO AC_NAME PC_NO PC_NAME avg_share_price
0 Jammu & Kashmir 1 1 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 1 KARNAH 1 BARAMULLA NaN
1 Jammu & Kashmir 1 1 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 1 KARNAH 1 BARAMULLA NaN
2 Jammu & Kashmir 1 2 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 2 KUPWARA 1 BARAMULLA NaN
3 Jammu & Kashmir 1 2 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 2 KUPWARA 1 BARAMULLA NaN
4 Jammu & Kashmir 1 3 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 3 LOLAB 1 BARAMULLA NaN
5 Jammu & Kashmir 1 3 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 3 LOLAB 1 BARAMULLA NaN
6 Jammu & Kashmir 1 4 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 4 HANDWARA 1 BARAMULLA NaN
7 Jammu & Kashmir 1 4 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 4 HANDWARA 1 BARAMULLA NaN
8 Jammu & Kashmir 1 5 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 5 LANGATE 1 BARAMULLA NaN
9 Jammu & Kashmir 1 5 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 1.0 KUPWARA 5 LANGATE 1 BARAMULLA NaN
10 Jammu & Kashmir 1 6 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 6 URI 1 BARAMULLA NaN
11 Jammu & Kashmir 1 6 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 6 URI 1 BARAMULLA NaN
12 Jammu & Kashmir 1 7 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 7 RAFIABAD 1 BARAMULLA NaN
13 Jammu & Kashmir 1 7 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 7 RAFIABAD 1 BARAMULLA NaN
14 Jammu & Kashmir 1 8 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 8 SOPORE 1 BARAMULLA NaN
15 Jammu & Kashmir 1 8 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 8 SOPORE 1 BARAMULLA NaN
16 Jammu & Kashmir 1 9 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 9 GUREZ 1 BARAMULLA NaN
17 Jammu & Kashmir 1 9 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 9 GUREZ 1 BARAMULLA NaN
18 Jammu & Kashmir 1 10 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 10 BANDIPORA 1 BARAMULLA NaN
19 Jammu & Kashmir 1 10 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 10 BANDIPORA 1 BARAMULLA NaN
20 Jammu & Kashmir 1 11 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 11 SONAWARI 1 BARAMULLA NaN
21 Jammu & Kashmir 1 11 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 11 SONAWARI 1 BARAMULLA NaN
22 Jammu & Kashmir 1 12 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 12 SANGRAMA 1 BARAMULLA NaN
23 Jammu & Kashmir 1 12 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 12 SANGRAMA 1 BARAMULLA NaN
24 Jammu & Kashmir 1 13 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 13 BARAMULA 1 BARAMULLA NaN
25 Jammu & Kashmir 1 13 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 13 BARAMULA 1 BARAMULLA NaN
26 Jammu & Kashmir 1 14 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 14 GULMARG 1 BARAMULLA NaN
27 Jammu & Kashmir 1 14 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 14 GULMARG 1 BARAMULLA NaN
28 Jammu & Kashmir 1 15 2008 12 1 2009 2008-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 15 PATTAN 1 BARAMULLA NaN
29 Jammu & Kashmir 1 15 2014 12 1 2015 2014-12-01 1 1 JAMMU & KASHMIR 2.0 BARAMULA 15 PATTAN 1 BARAMULLA NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
8328 Puducherry 34 16 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 16 Orleampeth 1 PONDICHERRY NaN
8329 Puducherry 34 16 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 16 Orleampeth 1 PONDICHERRY NaN
8330 Puducherry 34 17 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 17 Nellithope 1 PONDICHERRY NaN
8331 Puducherry 34 17 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 17 Nellithope 1 PONDICHERRY NaN
8332 Puducherry 34 18 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 18 Mudaliarpet 1 PONDICHERRY NaN
8333 Puducherry 34 18 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 18 Mudaliarpet 1 PONDICHERRY NaN
8334 Puducherry 34 19 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 19 Ariankuppam 1 PONDICHERRY NaN
8335 Puducherry 34 19 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 19 Ariankuppam 1 PONDICHERRY NaN
8336 Puducherry 34 20 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 20 Manavely 1 PONDICHERRY NaN
8337 Puducherry 34 20 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 20 Manavely 1 PONDICHERRY NaN
8338 Puducherry 34 21 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 21 Embalam (SC) 1 PONDICHERRY NaN
8339 Puducherry 34 21 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 21 Embalam (SC) 1 PONDICHERRY NaN
8340 Puducherry 34 22 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 22 Nettapakkam (SC) 1 PONDICHERRY NaN
8341 Puducherry 34 22 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 22 Nettapakkam (SC) 1 PONDICHERRY NaN
8342 Puducherry 34 23 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 23 Bahour 1 PONDICHERRY NaN
8343 Puducherry 34 23 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 2.0 PONDICHERRY 23 Bahour 1 PONDICHERRY NaN
8344 Puducherry 34 24 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 24 Nedungadu (SC) 1 PONDICHERRY NaN
8345 Puducherry 34 24 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 24 Nedungadu (SC) 1 PONDICHERRY NaN
8346 Puducherry 34 25 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 25 Thirunallar 1 PONDICHERRY NaN
8347 Puducherry 34 25 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 25 Thirunallar 1 PONDICHERRY NaN
8348 Puducherry 34 26 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 26 Karaikal North 1 PONDICHERRY NaN
8349 Puducherry 34 26 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 26 Karaikal North 1 PONDICHERRY NaN
8350 Puducherry 34 27 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 27 Karaikal South 1 PONDICHERRY NaN
8351 Puducherry 34 27 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 27 Karaikal South 1 PONDICHERRY NaN
8352 Puducherry 34 28 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 28 Neravy- T.R. Pattin 1 PONDICHERRY NaN
8353 Puducherry 34 28 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 4.0 KARAIKAL 28 Neravy- T.R. Pattin 1 PONDICHERRY NaN
8354 Puducherry 34 29 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 3.0 MAHE 29 Mahe 1 PONDICHERRY NaN
8355 Puducherry 34 29 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 3.0 MAHE 29 Mahe 1 PONDICHERRY NaN
8356 Puducherry 34 30 2011 5 1 2011 2011-05-01 1 34 PUDUCHERRY 1.0 YANAM 30 Yanam 1 PONDICHERRY NaN
8357 Puducherry 34 30 2016 5 1 2016 2016-05-01 1 34 PUDUCHERRY 1.0 YANAM 30 Yanam 1 PONDICHERRY NaN

8358 rows × 18 columns

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