In [1]:
# Import Librariesa
import pandas as pd
import geopandas as gpd
import lxml
import os
import glob
import time
import datetime
import json
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")
download_folder = os.path.join(data_folder, "Downloaded")
folders = os.listdir(data_folder)
## Make Noise
print("data found for these themes:")
i = 0
for folder in folders:
    print(i,") ",folder)
    i+=1

data found for these themes:
0 )  Data User_2011_12
1 )  Data User_2011_12.zip
2 )  Data User_2012_13
3 )  Data User_2012_13.zip
4 )  Data User_2013_14
5 )  Data User_2013_14.zip
6 )  Data User_2014_15
7 )  Data User_2014_15.zip
8 )  Data User_2015_16
9 )  Data User_2015_16.zip
10 )  Data User_2016_17
11 )  Data User_2016_17.zip
12 )  Data User_2017_18
13 )  Data User_2017_18.zip
14 )  Downloaded
In [5]:
# Read Constituency Data
ac_gdf = None
ac_filepath = os.path.join(data_folder, "Downloaded", "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[:-4]]
acdf

---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
<ipython-input-5-3f97d095d03e> in <module>()
      7 # Read Constituency Data
      8 ac_file = os.path.join(data_folder, "General_Later_Ashoka_alldata.csv")
----> 9 acdf = pd.read_csv(ac_file)
     10 acdf['state_name'] = acdf['state_name'].str.replace("_", " ")
     11 #acdf = acdf[['state_name', 'constituency_no', 'constituency_name', 'year', 'month']]

~\AppData\Local\conda\conda\envs\geo\lib\site-packages\pandas\io\parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, doublequote, delim_whitespace, low_memory, memory_map, float_precision)
    676                     skip_blank_lines=skip_blank_lines)
    677 
--> 678         return _read(filepath_or_buffer, kwds)
    679 
    680     parser_f.__name__ = name

~\AppData\Local\conda\conda\envs\geo\lib\site-packages\pandas\io\parsers.py in _read(filepath_or_buffer, kwds)
    438 
    439     # Create the parser.
--> 440     parser = TextFileReader(filepath_or_buffer, **kwds)
    441 
    442     if chunksize or iterator:

~\AppData\Local\conda\conda\envs\geo\lib\site-packages\pandas\io\parsers.py in __init__(self, f, engine, **kwds)
    785             self.options['has_index_names'] = kwds['has_index_names']
    786 
--> 787         self._make_engine(self.engine)
    788 
    789     def close(self):

~\AppData\Local\conda\conda\envs\geo\lib\site-packages\pandas\io\parsers.py in _make_engine(self, engine)
   1012     def _make_engine(self, engine='c'):
   1013         if engine == 'c':
-> 1014             self._engine = CParserWrapper(self.f, **self.options)
   1015         else:
   1016             if engine == 'python':

~\AppData\Local\conda\conda\envs\geo\lib\site-packages\pandas\io\parsers.py in __init__(self, src, **kwds)
   1706         kwds['usecols'] = self.usecols
   1707 
-> 1708         self._reader = parsers.TextReader(src, **kwds)
   1709 
   1710         passed_names = self.names is None

pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader.__cinit__()

pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._setup_parser_source()

FileNotFoundError: File b'E:\\workspace\\sourav sarkar\\Task 18 - Join new AISHE data\\data\\General_Later_Ashoka_alldata.csv' does not exist
In [52]:
# Colleges data

## Read General data
year_data_folder = os.path.join(data_folder, "Data User_2016_17")
college = os.path.join(year_data_folder, "college.csv")
cdf = pd.read_csv(college)
cdf

## Read Institution data
college_institution = os.path.join(year_data_folder, "college_institution.csv")
cidf = pd.read_csv(college_institution)
cidf

## Read Geocoded data
geocoded_data_folder = os.path.join(data_folder, "Geocoded")
Colleges_geocoded = os.path.join(geocoded_data_folder, "Colleges_geocoded.shp.csv")
cgdf = pd.read_csv(Colleges_geocoded)
cgdf

## merge data
colleges_merge_df = pd.merge(cgdf, cdf, on=['id'], how='inner')
colleges_merge_df = pd.merge(colleges_merge_df, cidf, on=['id'], how='inner')

## create Datetime Column
colleges_merge_df['year'] = colleges_merge_df['year_of_establishment'].astype(float).fillna(0.0).astype(int)
colleges_merge_df = colleges_merge_df[colleges_merge_df['year'] > 2000]
colleges_merge_df['month'] = 9
colleges_merge_df['day'] = 30
colleges_merge_df['datetime'] = pd.to_datetime(colleges_merge_df[['year', 'month', 'day']])

colleges_merge_df

C:\Users\sandyjones\AppData\Local\conda\conda\envs\geo\lib\site-packages\IPython\core\interactiveshell.py:2785: DtypeWarning: Columns (37,38,39,40,44,45,49) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)
Out[52]:
id latitude_x longitude_x geometry index_right OBJECTID ST_CODE ST_NAME DT_CODE DIST_NAME ... pin_code has_fellowships fellowships_id other_affiliated_university_id has_other_minority_data block_city_town year month day datetime
1 29888 27.030001 93.900001 POINT (93.900001 27.030001) 83 9 18 ASSAM 12.0 LAKHIMPUR ... 453331 False NaN NaN False NaN 2011 9 30 2011-09-30
2 25724 27.030010 93.900010 POINT (93.90000999999999 27.03001) 83 9 18 ASSAM 12.0 LAKHIMPUR ... 500110 False NaN NaN False NaN 2001 9 30 2001-09-30
4 1132 16.450001 74.310001 POINT (74.310001 16.450001) 2943 47 27 MAHARASHTRA 34.0 KOLHAPUR ... 395008 False NaN NaN False NaN 2003 9 30 2003-09-30
6 19228 16.513010 74.174310 POINT (74.17431000000001 16.51301) 2943 47 27 MAHARASHTRA 34.0 KOLHAPUR ... 470004 False NaN NaN False NaN 2005 9 30 2005-09-30
7 14686 16.420120 74.141230 POINT (74.14122999999999 16.42012) 2943 47 27 MAHARASHTRA 34.0 KOLHAPUR ... 639117 False NaN NaN False NaN 2007 9 30 2007-09-30
8 15944 16.222321 74.347365 POINT (74.34736499999998 16.222321) 2943 47 27 MAHARASHTRA 34.0 KOLHAPUR ... 276121 False NaN NaN False NaN 2004 9 30 2004-09-30
11 19618 16.452167 74.300181 POINT (74.30018099999998 16.452167) 2943 47 27 MAHARASHTRA 34.0 KOLHAPUR ... 506003 False NaN NaN False NaN 2008 9 30 2008-09-30
13 27508 16.267350 74.348290 POINT (74.34829000000001 16.26735) 2943 47 27 MAHARASHTRA 34.0 KOLHAPUR ... 506001 False NaN NaN False NaN 2003 9 30 2003-09-30
24 36722 29.200478 74.781897 POINT (74.78189740000001 29.2004785) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335523 False NaN NaN True NaN 2006 9 30 2006-09-30
25 36758 29.294451 74.571958 POINT (74.5719579 29.2944507) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335524 False NaN NaN False NaN 2011 9 30 2011-09-30
26 36857 29.324314 74.897236 POINT (74.89723640000001 29.3243141) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335523 False NaN 0790 True NaN 2007 9 30 2007-09-30
27 36869 28.944968 74.217647 POINT (74.2176475 28.9449682) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335524 False NaN NaN False NaN 2008 9 30 2008-09-30
28 36886 29.244420 74.519548 POINT (74.5195485 29.24442) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335524 False NaN NaN False NaN 2011 9 30 2011-09-30
29 36892 28.930222 74.209128 POINT (74.20912800000001 28.9302218) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335524 False NaN NaN True NaN 2008 9 30 2008-09-30
30 40663 29.185556 74.770465 POINT (74.7704653 29.1855559) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335523 False NaN NaN False NaN 2011 9 30 2011-09-30
31 40671 29.179889 74.772101 POINT (74.77210059999999 29.1798889) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335523 False NaN 0761 True NaN 2003 9 30 2003-09-30
32 51591 29.292240 74.565459 POINT (74.56545899999999 29.29224) 3417 3 8 RAJASTHAN 2.0 HANUMANGARH * ... 335524 False NaN NaN True NaN 2012 9 30 2012-09-30
34 14728 9.543693 78.591534 POINT (78.59153430000001 9.543692500000001) 645 35 33 TAMIL NADU 27.0 RAMANATHAPURAM ... 623707 False NaN NaN True NaN 2008 9 30 2008-09-30
38 17994 10.568950 76.254890 POINT (76.25489 10.56895) 366 10 32 KERALA 7.0 THRISSUR ... 522213 False NaN NaN False NaN 2006 9 30 2006-09-30
39 32638 10.553110 76.226910 POINT (76.22691 10.55311) 366 10 32 KERALA 7.0 THRISSUR ... 522646 False NaN NaN False NaN 2002 9 30 2002-09-30
40 30217 10.500420 76.211518 POINT (76.21151759999999 10.5004199) 366 10 32 KERALA 7.0 THRISSUR ... 516329 False NaN NaN False NaN 2006 9 30 2006-09-30
42 51782 10.566073 76.245857 POINT (76.2458575 10.5660734) 366 10 32 KERALA 7.0 THRISSUR ... 680028 False NaN NaN False NaN 2014 9 30 2014-09-30
46 8279 10.537122 76.196638 POINT (76.19663800000001 10.537122) 366 10 32 KERALA 7.0 THRISSUR ... 680581 False NaN NaN False NaN 2002 9 30 2002-09-30
47 43331 10.521003 76.226776 POINT (76.226776 10.521003) 366 10 32 KERALA 7.0 THRISSUR ... 680005 False NaN 0761 True NaN 2003 9 30 2003-09-30
48 52126 10.519263 76.185279 POINT (76.1852791 10.5192632) 366 10 32 KERALA 7.0 THRISSUR ... 680012 False NaN NaN True NaN 2011 9 30 2011-09-30
49 55797 10.562975 76.241988 POINT (76.2419881 10.5629749) 366 10 32 KERALA 7.0 THRISSUR ... 680631 False NaN NaN True NaN 2016 9 30 2016-09-30
52 6331 21.603177 71.222083 POINT (71.222083 21.603177) 1895 14 24 GUJARAT 13.0 AMRELI ... 700114 False NaN 0590 False NaN 2003 9 30 2003-09-30
53 7118 21.602871 71.218170 POINT (71.21817 21.602871) 1895 14 24 GUJARAT 13.0 AMRELI ... 741155 False NaN NaN False NaN 2008 9 30 2008-09-30
55 866 21.589182 71.221602 POINT (71.2216016 21.5891818) 1895 14 24 GUJARAT 13.0 AMRELI ... 365601 False NaN NaN False NaN 2002 9 30 2002-09-30
58 997 21.587991 71.223432 POINT (71.22343190000001 21.5879911) 1895 14 24 GUJARAT 13.0 AMRELI ... 365601 False NaN NaN False NaN 2001 9 30 2001-09-30
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
26075 48459 11.224637 76.255278 POINT (76.25527840000001 11.2246368) 334 4 32 KERALA 5.0 MALAPPURAM ... 679339 False NaN NaN False NaN 2013 9 30 2013-09-30
26076 50868 11.208118 76.228672 POINT (76.2286723 11.2081181) 334 4 32 KERALA 5.0 MALAPPURAM ... 679328 False NaN NaN False NaN 2014 9 30 2014-09-30
26077 48473 33.629493 74.270994 POINT (74.27099390000001 33.6294927) 241 6 1 JAMMU & KASHMIR 11.0 PUNCH ... 185121 False NaN NaN True NaN 2011 9 30 2011-09-30
26078 48529 33.647416 75.009635 POINT (75.00963540000001 33.6474161) 251 3 1 JAMMU & KASHMIR 6.0 ANANTNAG ... 192233 False NaN NaN False NaN 2011 9 30 2011-09-30
26079 48773 33.037685 74.485495 POINT (74.4854945 33.0376847) 266 6 1 JAMMU & KASHMIR 12.0 RAJAURI ... 185153 False NaN 0196 False NaN 2011 9 30 2011-09-30
26080 48962 27.494258 80.972913 POINT (80.97291290000001 27.4942584) 3914 30 9 UTTAR PRADESH 24.0 SITAPUR ... 261201 False NaN NaN False NaN 2013 9 30 2013-09-30
26081 48965 27.552364 81.223458 POINT (81.22345770000001 27.552364) 3911 30 9 UTTAR PRADESH 24.0 SITAPUR ... 261205 False NaN NaN False NaN 2013 9 30 2013-09-30
26082 49125 25.588930 84.741499 POINT (84.74149940000001 25.5889298) 1464 32 10 BIHAR 29.0 BHOJPUR ... 802314 False NaN NaN False NaN 2010 9 30 2010-09-30
26083 49362 26.851585 83.713230 POINT (83.71323000000001 26.8515848) 4005 65 9 UTTAR PRADESH 59.0 KUSHINAGAR * ... 274305 False NaN NaN False NaN 2012 9 30 2012-09-30
26084 49558 25.410338 86.195746 POINT (86.195746 25.410338) 1498 24 10 BIHAR 20.0 BEGUSARAI ... 851129 False NaN 0434 False NaN 2013 9 30 2013-09-30
26085 55747 25.428148 86.103985 POINT (86.10398480000001 25.4281485) 1498 24 10 BIHAR 20.0 BEGUSARAI ... 851218 False NaN NaN False NaN 2016 9 30 2016-09-30
26086 49556 26.313298 87.243293 POINT (87.24329329999999 26.3132984) 1352 9 10 BIHAR 7.0 ARARIA ... 854318 False NaN NaN False NaN 2014 9 30 2014-09-30
26087 50710 15.307222 73.964269 POINT (73.96426940000001 15.3072215) 1758 2 30 GOA 2.0 SOUTH GOA ... 403720 False NaN NaN False NaN 2014 9 30 2014-09-30
26089 51294 30.887896 75.870098 POINT (75.870098 30.8878956) 3359 7 3 PUNJAB 9.0 LUDHIANA ... 141401 False NaN NaN True NaN 2011 9 30 2011-09-30
26090 51437 28.009100 79.683900 POINT (79.68389999999999 28.0091) 3870 27 9 UTTAR PRADESH 22.0 SHAHJAHANPUR ... 242001 False NaN NaN False NaN 2013 9 30 2013-09-30
26091 51568 23.779022 91.303017 POINT (91.30301709999999 23.7790221) 3684 1 16 TRIPURA 1.0 WEST TRIPURA ... 799004 True 8381.0 NaN False NaN 2014 9 30 2014-09-30
26092 52243 32.642498 74.903025 POINT (74.90302459999999 32.6424982) 289 6 1 JAMMU & KASHMIR 13.0 JAMMU ... 181133 False NaN NaN True NaN 2008 9 30 2008-09-30
26093 52309 25.111231 83.621573 POINT (83.621573 25.111231) 1535 34 10 BIHAR 31.0 KAIMUR (BHABUA) * ... 821109 False NaN NaN False NaN 2013 9 30 2013-09-30
26094 53035 27.517872 82.408938 POINT (82.4089378 27.5178722) 3901 58 9 UTTAR PRADESH 52.0 BALRAMPUR * ... 271208 True 8789.0 0848 False NaN 2012 9 30 2012-09-30
26095 53107 22.921156 73.842641 POINT (73.8426415 22.921156) 1824 18 24 GUJARAT 17.0 PANCH MAHALS ... 389115 False NaN NaN False NaN 2015 9 30 2015-09-30
26097 54172 29.537709 79.749863 POINT (79.7498631 29.5377085) 3760 3 5 UTTARKHAND 9.0 ALMORA ... 263625 False NaN 0045 False NaN 2014 9 30 2014-09-30
26098 54367 21.964179 87.480107 POINT (87.48010749999999 21.964179) 892 34 19 WEST BENGAL 15.0 PASCHIM MEDINAPUR ... 721445 False NaN NaN False NaN 2015 9 30 2015-09-30
26099 54448 21.870949 87.424283 POINT (87.42428270000001 21.8709491) 892 34 19 WEST BENGAL 15.0 PASCHIM MEDINAPUR ... 721436 False NaN NaN False NaN 2015 9 30 2015-09-30
26100 54434 28.052335 79.405865 POINT (79.40586450000001 28.0523352) 3874 24 9 UTTAR PRADESH 19.0 BUDAUN ... 243635 False NaN NaN False NaN 2015 9 30 2015-09-30
26101 54634 32.180276 75.925793 POINT (75.9257928 32.1802761) 2044 1 2 HIMACHAL PRADESH 2.0 KANGRA ... 176038 False NaN NaN False NaN 2015 9 30 2015-09-30
26102 54888 31.779333 77.191435 POINT (77.1914354 31.779333) 2052 2 2 HIMACHAL PRADESH 5.0 MANDI ... 175121 False NaN NaN False NaN 2015 9 30 2015-09-30
26103 55062 24.436022 88.134851 POINT (88.1348515 24.4360221) 732 9 19 WEST BENGAL 7.0 MURSHIDABAD ... 742213 False NaN NaN False NaN 2014 9 30 2014-09-30
26104 55418 26.369225 88.324879 POINT (88.32487929999999 26.3692245) 701 4 19 WEST BENGAL 4.0 UTTAR DINAJPUR ... 733207 False NaN NaN True NaN 2013 9 30 2013-09-30
26105 56111 22.279130 87.422840 POINT (87.4228401 22.2791301) 900 32 19 WEST BENGAL 15.0 PASCHIM MEDINAPUR ... 721301 False NaN NaN False NaN 2016 9 30 2016-09-30
26106 56122 22.324798 73.183052 POINT (73.1830521 22.3247981) 1873 20 24 GUJARAT 19.0 VADODARA ... 384235 False NaN NaN False NaN 2016 9 30 2016-09-30

15878 rows × 78 columns

In [53]:
# University data

## Read General data
year_data_folder = os.path.join(data_folder, "Data User_2016_17")
university = os.path.join(year_data_folder, "university.csv")
udf = pd.read_csv(university)
udf

## Read Geocoded data
geocoded_data_folder = os.path.join(data_folder, "Geocoded")
university_geocoded = os.path.join(geocoded_data_folder, "university_geocoded.shp.csv")
ugdf = pd.read_csv(university_geocoded)
ugdf

## merge data
university_merge_df = pd.merge(ugdf, udf, on=['id'], how='inner')

## create Datetime Column
university_merge_df['year'] = university_merge_df['year_of_establishment'].astype(float).fillna(0.0).astype(int)
university_merge_df = university_merge_df[university_merge_df['year'] > 2000]
university_merge_df['month'] = 9
university_merge_df['day'] = 30
university_merge_df['datetime'] = pd.to_datetime(university_merge_df[['year', 'month', 'day']])

university_merge_df
Out[53]:
id latitude_x longitude_x geometry index_right OBJECTID ST_CODE ST_NAME DT_CODE DIST_NAME ... fellowships_id has_other_minority_data block_city_town is_university_uploaded_act_statues is_university_complying_rules is_university180_actual_teaching_days year month day datetime
5 147 23.000000 72.000000 POINT (72 23) 1801 9 24 GUJARAT 8.0 SURENDRANAGAR ... 8537.0 True NaN NaN NaN NaN 2007 9 30 2007-09-30
6 147 23.146049 72.651515 POINT (72.65151463021385 23.14604890263672) 1810 7 24 GUJARAT 6.0 GANDHINAGAR ... 8537.0 True NaN NaN NaN NaN 2007 9 30 2007-09-30
7 594 23.000010 72.000010 POINT (72.00001 23.00001) 1801 9 24 GUJARAT 8.0 SURENDRANAGAR ... NaN False NaN NaN NaN NaN 2009 9 30 2009-09-30
8 734 23.000120 72.000310 POINT (72.00031 23.00012) 1801 9 24 GUJARAT 8.0 SURENDRANAGAR ... NaN False NaN NaN NaN NaN 2013 9 30 2013-09-30
9 82 19.000000 82.000000 POINT (82 19) 1656 10 22 CHHATTISGARH 15.0 BASTER ... NaN True NaN NaN NaN NaN 2008 9 30 2008-09-30
10 82 18.863822 82.072980 POINT (82.07297981034584 18.86382162248797) 1656 10 22 CHHATTISGARH 15.0 BASTER ... NaN True NaN NaN NaN NaN 2008 9 30 2008-09-30
13 870 24.668863 73.700119 POINT (73.7001192 24.6688628) 3586 19 8 RAJASTHAN 26.0 UDAIPUR ... NaN False NaN NaN NaN NaN 2016 9 30 2016-09-30
14 706 23.584795 72.954487 POINT (72.954487 23.584795) 1795 5 24 GUJARAT 5.0 SABAR KANTHA ... NaN False NaN NaN NaN NaN 2009 9 30 2009-09-30
15 706 23.237800 72.703350 POINT (72.70335 23.2378) 1810 7 24 GUJARAT 6.0 GANDHINAGAR ... NaN False NaN NaN NaN NaN 2009 9 30 2009-09-30
16 430 27.338930 88.606510 POINT (88.60651 27.33893) 3622 1 11 SIKKIM 4.0 EAST ... NaN True NaN NaN NaN NaN 2007 9 30 2007-09-30
18 420 26.801658 75.828883 POINT (75.82888259999999 26.8016582) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN False NaN NaN NaN NaN 2005 9 30 2005-09-30
19 401 26.850001 75.900000 POINT (75.9000001 26.850001) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN True NaN NaN NaN NaN 2002 9 30 2002-09-30
20 607 26.779378 75.775887 POINT (75.77588699999998 26.7793779) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN False NaN NaN NaN NaN 2009 9 30 2009-09-30
21 607 26.744414 75.765122 POINT (75.76512210049169 26.74441350892278) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN False NaN NaN NaN NaN 2009 9 30 2009-09-30
22 752 26.777560 75.845421 POINT (75.845421 26.77756) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN True NaN NaN NaN NaN 2012 9 30 2012-09-30
23 748 26.811507 75.892244 POINT (75.89224400000001 26.811507) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN True NaN NaN NaN NaN 2012 9 30 2012-09-30
25 799 26.844470 75.811310 POINT (75.81131000000001 26.84447) 3501 7 8 RAJASTHAN 12.0 JAIPUR ... NaN True NaN NaN NaN NaN 2013 9 30 2013-09-30
26 72 25.620589 85.171850 POINT (85.17185000000001 25.620589) 1478 30 10 BIHAR 28.0 PATNA ... NaN True NaN NaN NaN NaN 2004 9 30 2004-09-30
27 72 25.618470 85.168360 POINT (85.16836000000001 25.61847) 1478 30 10 BIHAR 28.0 PATNA ... NaN True NaN NaN NaN NaN 2004 9 30 2004-09-30
31 689 26.280010 73.020010 POINT (73.02001 26.28001) 3521 16 8 RAJASTHAN 15.0 JODHPUR ... NaN False NaN NaN NaN NaN 2012 9 30 2012-09-30
32 340 25.568140 91.883382 POINT (91.88338227 25.5681398) 3048 1 17 MEGHALAYA 6.0 EAST KHASI HILLS ... 8813.0 True NaN NaN NaN NaN 2005 9 30 2005-09-30
33 619 25.573764 91.894167 POINT (91.894167 25.573764) 3048 1 17 MEGHALAYA 6.0 EAST KHASI HILLS ... 9402.0 True NaN NaN NaN NaN 2010 9 30 2010-09-30
34 343 25.567454 91.899960 POINT (91.89996020000001 25.5674539) 3048 1 17 MEGHALAYA 6.0 EAST KHASI HILLS ... NaN False NaN NaN NaN NaN 2005 9 30 2005-09-30
37 741 26.922070 75.778880 POINT (75.77888 26.92207) 3604 7 8 RAJASTHAN 12.0 JAIPUR ... 8039.0 True NaN NaN NaN NaN 2012 9 30 2012-09-30
38 657 26.900010 75.800010 POINT (75.80001 26.90001) 3604 7 8 RAJASTHAN 12.0 JAIPUR ... NaN True NaN NaN NaN NaN 2011 9 30 2011-09-30
43 388 27.176402 75.956887 POINT (75.95688699999998 27.1764023) 3479 6 8 RAJASTHAN 12.0 JAIPUR ... NaN False NaN NaN NaN NaN 2007 9 30 2007-09-30
44 415 27.186890 75.953090 POINT (75.95309 27.18689) 3479 6 8 RAJASTHAN 12.0 JAIPUR ... NaN False NaN NaN NaN NaN 2008 9 30 2008-09-30
46 273 22.719569 75.857726 POINT (75.85772579999998 22.7195687) 2588 26 23 MADHYA PRADESH 26.0 INDORE ... 7832.0 False NaN NaN NaN NaN 2009 9 30 2009-09-30
48 822 30.663689 76.300493 POINT (76.3004926 30.6636894) 3375 8 3 PUNJAB 8.0 FATEHGARH SAHIB * ... NaN True NaN NaN NaN NaN 2015 9 30 2015-09-30
51 834 22.619820 75.804508 POINT (75.8045084 22.6198202) 2592 26 23 MADHYA PRADESH 26.0 INDORE ... NaN True NaN NaN NaN NaN 2015 9 30 2015-09-30
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
726 390 26.534705 74.645953 POINT (74.64595266409808 26.53470452902219) 3491 13 8 RAJASTHAN 21.0 AJMER ... NaN False NaN NaN NaN NaN 2008 9 30 2008-09-30
732 176 32.083885 75.780756 POINT (75.78075633327012 32.08388519833935) 2042 1 2 HIMACHAL PRADESH 2.0 KANGRA ... NaN True NaN NaN NaN NaN 2009 9 30 2009-09-30
733 349 26.011884 94.016435 POINT (94.01643493638429 26.01188390700705) 14 1 13 NAGALAND 5.0 WOKHA ... NaN False NaN NaN NaN NaN 2007 9 30 2007-09-30
735 818 28.402000 77.292190 POINT (77.29219000000001 28.402) 2013 10 6 HARYANA 19.0 FARIDABAD ... NaN False NaN NaN NaN NaN 2014 9 30 2014-09-30
736 599 25.920542 82.203121 POINT (82.20312129407306 25.92054219449888) 4095 39 9 UTTAR PRADESH 43.0 PRATAPGARH ... NaN True NaN NaN NaN NaN 2010 9 30 2010-09-30
738 512 26.847860 80.885000 POINT (80.88500000000001 26.84786) 4002 35 9 UTTAR PRADESH 27.0 LUCKNOW ... NaN False NaN NaN NaN NaN 2008 9 30 2008-09-30
742 777 28.577175 94.652234 POINT (94.65223393705809 28.57717502498661) 1267 1 12 ARUNACHAL PRADESH 7.0 WEST SIANG ... NaN False NaN NaN NaN NaN 2014 9 30 2014-09-30
743 848 27.381432 83.079680 POINT (83.07968013438986 27.38143204656178) 3930 60 9 UTTAR PRADESH 54.0 SIDDHARTHNAGAR ... NaN False NaN NaN NaN NaN 2015 9 30 2015-09-30
744 204 25.777043 73.322215 POINT (73.32221536436916 25.77704284793257) 3536 15 8 RAJASTHAN 20.0 PALI ... NaN True NaN NaN NaN NaN 2009 9 30 2009-09-30
745 816 24.880957 72.861389 POINT (72.86138916473699 24.88095719041128) 3560 18 8 RAJASTHAN 19.0 SIROHI ... NaN False NaN NaN NaN NaN 2011 9 30 2011-09-30
746 551 29.991330 78.192470 POINT (78.19247 29.99133) 3736 5 5 UTTARKHAND 5.0 DEHRADUN ... 8918.0 False NaN NaN NaN NaN 2002 9 30 2002-09-30
747 800 28.929079 77.021128 POINT (77.02112806121298 28.92907859087477) 1997 6 6 HARYANA 8.0 SONIPAT ... NaN True NaN NaN NaN NaN 2014 9 30 2014-09-30
750 2 17.818651 83.024750 POINT (83.0247503450119 17.81865138317337) 1044 22 28 ANDHRA PRADESH 13.0 VISAKHAPATNAM ... NaN False NaN NaN NaN NaN 2008 9 30 2008-09-30
753 793 27.285131 77.497403 POINT (77.49740334724723 27.2851308817623) 3468 9 8 RAJASTHAN 7.0 BHARATPUR ... NaN False NaN NaN NaN NaN 2012 9 30 2012-09-30
756 259 9.897265 76.326444 POINT (76.32644361015421 9.897264773152934) 388 12 32 KERALA 8.0 ERNAKULAM ... NaN False NaN NaN NaN NaN 2010 9 30 2010-09-30
758 802 9.430000 76.390000 POINT (76.39 9.43) 404 16 32 KERALA 11.0 ALAPPUZHA ... NaN False NaN NaN NaN NaN 2015 9 30 2015-09-30
759 225 15.903860 74.526970 POINT (74.52697000000001 15.90386) 2250 2 29 KARNATAKA 1.0 BELGAUM ... NaN False NaN NaN NaN NaN 2006 9 30 2006-09-30
760 264 10.043080 76.328580 POINT (76.32858 10.04308) 380 12 32 KERALA 8.0 ERNAKULAM ... NaN True NaN NaN NaN NaN 2002 9 30 2002-09-30
762 544 28.742759 78.783940 POINT (78.78394023379346 28.74275936065301) 3820 8 9 UTTAR PRADESH 4.0 MORADABAD ... NaN False NaN NaN NaN NaN 2008 9 30 2008-09-30
764 784 17.339698 78.377592 POINT (78.37759164231555 17.33969761008107) 1084 10 28 ANDHRA PRADESH 6.0 RANGAREDDI ... 9207.0 True NaN NaN NaN NaN 2014 9 30 2014-09-30
765 631 32.604780 75.043140 POINT (75.04313999999999 32.60478) 289 6 1 JAMMU & KASHMIR 13.0 JAMMU ... 9215.0 True NaN NaN NaN NaN 2011 9 30 2011-09-30
766 174 28.835484 76.535464 POINT (76.53546430668329 28.83548396356228) 1993 7 6 HARYANA 14.0 ROHTAK ... NaN False NaN NaN NaN NaN 2008 9 30 2008-09-30
767 535 26.141006 81.023732 POINT (81.02373219215931 26.14100605620296) 4077 36 9 UTTAR PRADESH 28.0 RAE BARELI ... NaN False NaN NaN NaN NaN 2007 9 30 2007-09-30
771 839 23.799354 91.325810 POINT (91.32580990529392 23.79935362940698) 3684 1 16 TRIPURA 1.0 WEST TRIPURA ... NaN False NaN NaN NaN NaN 2015 9 30 2015-09-30
776 810 25.350439 78.802641 POINT (78.80264135177472 25.35043934132806) 2441 6 23 MADHYA PRADESH 8.0 TIKAMGARH ... NaN True NaN NaN NaN NaN 2009 9 30 2009-09-30
782 229 16.830626 75.640583 POINT (75.64058349999999 16.8306258) 2212 4 29 KARNATAKA 3.0 BIJAPUR ... NaN False NaN NaN NaN NaN 2003 9 30 2003-09-30
784 737 30.192774 78.164742 POINT (78.1647423 30.1927738) 3731 5 5 UTTARKHAND 5.0 DEHRADUN ... NaN False NaN NaN NaN NaN 2013 9 30 2013-09-30
785 346 23.800644 92.727956 POINT (92.7279564 23.800644) 3084 1 15 MIZORAM 3.0 AIZAWL ... NaN True NaN NaN NaN NaN 2006 9 30 2006-09-30
787 760 13.555559 80.026880 POINT (80.0268804 13.5555593) 1254 40 28 ANDHRA PRADESH 23.0 CHITTOOR ... NaN False NaN NaN NaN NaN 2013 9 30 2013-09-30
788 290 22.806404 81.749488 POINT (81.7494883 22.8064043) 2559 12 23 MADHYA PRADESH 47.0 ANUPPUR ... 9261.0 True NaN NaN NaN NaN 2008 9 30 2008-09-30

426 rows × 73 columns

In [54]:
# Standalone Institute data

## Read Institution data
year_data_folder = os.path.join(data_folder, "Data User_2016_17")
standalone_institution = os.path.join(year_data_folder, "standalone_institution.csv")
sidf = pd.read_csv(standalone_institution)
sidf

## Read Geocoded data
geocoded_data_folder = os.path.join(data_folder, "Geocoded")
standalone_geocoded = os.path.join(geocoded_data_folder, "standalone_geocoded.shp.csv")
sgdf = pd.read_csv(standalone_geocoded)
sgdf

## merge data
standalone_merge_df = pd.merge(sgdf, sidf, on=['id'], how='inner')

## create Datetime Column
standalone_merge_df['year'] = standalone_merge_df['year_of_establishment'].astype(float).fillna(0.0).astype(int)
standalone_merge_df = standalone_merge_df[standalone_merge_df['year'] > 2000]
standalone_merge_df['month'] = 9
standalone_merge_df['day'] = 30
standalone_merge_df['datetime'] = pd.to_datetime(standalone_merge_df[['year', 'month', 'day']])

standalone_merge_df
Out[54]:
id latitude_x longitude_x geometry index_right OBJECTID ST_CODE ST_NAME DT_CODE DIST_NAME ... pin_code has_fellowships fellowships_id ministry_id has_other_minority_data block_city_town year month day datetime
0 14518 28.587225 77.227095 POINT (77.22709500000001 28.587225) 1703 4 7 DELHI NaN NaN ... 110003 False NaN NaN False NaN 2014 9 30 2014-09-30
1 10811 28.610940 77.234482 POINT (77.234482 28.6109401) 1703 4 7 DELHI NaN NaN ... 413113 False NaN NaN True NaN 2006 9 30 2006-09-30
2 6791 28.610940 77.234482 POINT (77.234482 28.6109401) 1703 4 7 DELHI NaN NaN ... 577501 False NaN NaN False NaN 2005 9 30 2005-09-30
3 9733 28.610940 77.234482 POINT (77.234482 28.61094) 1703 4 7 DELHI NaN NaN ... 431702 False NaN NaN False NaN 2005 9 30 2005-09-30
4 13972 28.610940 77.234482 POINT (77.234482 28.6109401) 1703 4 7 DELHI NaN NaN ... 380005 False NaN NaN True NaN 2012 9 30 2012-09-30
6 3494 28.610940 77.234482 POINT (77.234482 28.6109401) 1703 4 7 DELHI NaN NaN ... 501301 False NaN NaN True NaN 2005 9 30 2005-09-30
9 16631 28.610010 77.230010 POINT (77.23000999999999 28.61001) 1703 4 7 DELHI NaN NaN ... 322001 False NaN NaN False NaN 2016 9 30 2016-09-30
13 12625 15.354562 75.135566 POINT (75.13556599999998 15.354562) 2266 11 29 KARNATAKA 9.0 DHARWAD ... 580023 False NaN NaN False NaN 2004 9 30 2004-09-30
14 6618 15.364708 75.123955 POINT (75.12395499999998 15.364708) 2266 11 29 KARNATAKA 9.0 DHARWAD ... 580025 False NaN NaN False NaN 2005 9 30 2005-09-30
15 4404 15.354562 75.135566 POINT (75.13556599999998 15.354562) 2266 11 29 KARNATAKA 9.0 DHARWAD ... 580023 False NaN NaN False NaN 2001 9 30 2001-09-30
16 1259 15.398356 75.130445 POINT (75.13044499999998 15.398356) 2266 11 29 KARNATAKA 9.0 DHARWAD ... 580021 False NaN NaN True NaN 2009 9 30 2009-09-30
17 6409 15.364708 75.121240 POINT (75.12123955 15.364708) 2266 11 29 KARNATAKA 9.0 DHARWAD ... 580020 False NaN NaN False NaN 2005 9 30 2005-09-30
20 4225 15.341170 75.106370 POINT (75.10637 15.34117) 2266 11 29 KARNATAKA 9.0 DHARWAD ... 580024 False NaN NaN False NaN 2003 9 30 2003-09-30
21 13767 20.520000 75.700000 POINT (75.70000009999998 20.5200001) 2711 18 27 MAHARASHTRA 19.0 AURANGABAD ... 425111 False NaN NaN False NaN 2011 9 30 2011-09-30
23 10245 18.481541 73.827420 POINT (73.82742 18.481541) 2889 34 27 MAHARASHTRA 25.0 PUNE ... 411051 False NaN NaN False NaN 2006 9 30 2006-09-30
24 14148 28.455001 77.517001 POINT (77.51700099999999 28.455001) 3853 13 9 UTTAR PRADESH 10.0 GAUTAM BUDDHA NAGAR * ... 201308 False NaN NaN True NaN 2005 9 30 2005-09-30
25 932 28.356010 77.578010 POINT (77.57801000000001 28.35601) 3853 13 9 UTTAR PRADESH 10.0 GAUTAM BUDDHA NAGAR * ... 131039 False NaN NaN False NaN 2006 9 30 2006-09-30
26 8633 28.347697 77.553344 POINT (77.55334399999998 28.347697) 3853 13 9 UTTAR PRADESH 10.0 GAUTAM BUDDHA NAGAR * ... 203201 False NaN NaN False NaN 2005 9 30 2005-09-30
27 8633 28.350712 77.551318 POINT (77.5513181 28.3507117) 3853 13 9 UTTAR PRADESH 10.0 GAUTAM BUDDHA NAGAR * ... 203201 False NaN NaN False NaN 2005 9 30 2005-09-30
28 435 28.458697 77.491139 POINT (77.49113873 28.45869699) 3853 13 9 UTTAR PRADESH 10.0 GAUTAM BUDDHA NAGAR * ... 201306 False NaN NaN False NaN 2008 9 30 2008-09-30
30 9384 23.250001 75.250001 POINT (75.25000099999998 23.250001) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 431203 False NaN NaN False NaN 2008 9 30 2008-09-30
32 15147 23.292615 75.071686 POINT (75.071686 23.292615) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 457001 False NaN NaN False NaN 2006 9 30 2006-09-30
35 5806 23.256720 75.256720 POINT (75.25672 23.25672) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 389151 False NaN NaN False NaN 2002 9 30 2002-09-30
36 13778 23.250010 75.250010 POINT (75.25001 23.25001) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 626117 False NaN NaN False NaN 2011 9 30 2011-09-30
37 14171 23.256720 75.256720 POINT (75.25672 23.25672) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 244901 False NaN NaN False NaN 2010 9 30 2010-09-30
38 4437 23.250010 75.256720 POINT (75.25672 23.25001) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 683503 False NaN NaN True NaN 2005 9 30 2005-09-30
40 14589 23.256720 75.256720 POINT (75.25672 23.25672) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 422103 False NaN NaN False NaN 2006 9 30 2006-09-30
42 5617 23.250010 75.250010 POINT (75.25001 23.25001) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 518502 False NaN NaN False NaN 2007 9 30 2007-09-30
43 5414 23.256720 75.256720 POINT (75.25672 23.25672) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 243001 False NaN NaN False NaN 2002 9 30 2002-09-30
45 2006 23.256720 75.025672 POINT (75.025672 23.25672) 2533 24 23 MADHYA PRADESH 20.0 RATLAM ... 431002 False NaN NaN False NaN 2010 9 30 2010-09-30
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
4553 16664 26.080010 83.300070 POINT (83.30007000000001 26.08001) 4089 69 9 UTTAR PRADESH 61.0 AZAMGARH ... 274202 False NaN NaN False NaN 2016 9 30 2016-09-30
4555 16501 19.234904 77.346099 POINT (77.34609899999998 19.234904) 2815 16 27 MAHARASHTRA 15.0 NANDED ... 431704 False NaN NaN True NaN 2015 9 30 2015-09-30
4556 15911 30.445340 77.602120 POINT (77.60212 30.44534) 2098 4 2 HIMACHAL PRADESH 10.0 SIRMAUR ... 173025 False NaN NaN False NaN 2015 9 30 2015-09-30
4557 14442 20.580010 74.160010 POINT (74.16001 20.58001) 2692 2 27 MAHARASHTRA 20.0 NASHIK ... 424304 False NaN NaN False NaN 2012 9 30 2012-09-30
4558 4982 31.430010 75.720010 POINT (75.72001 31.43001) 3319 4 3 PUNJAB 4.0 JALANDHAR ... 144102 False NaN NaN False NaN 2005 9 30 2005-09-30
4559 16628 25.200010 75.800010 POINT (75.80001 25.20001) 3567 24 8 RAJASTHAN 30.0 KOTA ... 324005 False NaN NaN False NaN 2008 9 30 2008-09-30
4560 16661 25.234282 75.891489 POINT (75.89148900000001 25.234282) 3567 24 8 RAJASTHAN 30.0 KOTA ... 324002 False NaN NaN False NaN 2007 9 30 2007-09-30
4561 7637 26.697419 76.921637 POINT (76.921637 26.697419) 3506 10 8 RAJASTHAN 9.0 KARAULI * ... 322220 False NaN NaN True NaN 2004 9 30 2004-09-30
4562 15756 27.301550 79.090030 POINT (79.09003 27.30155) 3936 21 9 UTTAR PRADESH 18.0 MAINPURI ... 207247 False NaN NaN False NaN 2010 9 30 2010-09-30
4564 14976 31.320560 75.570580 POINT (75.57058000000001 31.32056) 3333 4 3 PUNJAB 4.0 JALANDHAR ... 144106 False NaN NaN False NaN 2014 9 30 2014-09-30
4565 15771 24.678563 78.449324 POINT (78.449324 24.678563) 4176 46 9 UTTAR PRADESH 37.0 LALITPUR ... 284403 False NaN NaN False NaN 2014 9 30 2014-09-30
4566 14818 25.300010 87.350010 POINT (87.35000999999998 25.30001) 1499 26 10 BIHAR 22.0 BHAGALPUR ... 803111 False NaN NaN False NaN 2010 9 30 2010-09-30
4569 15634 11.437124 75.765892 POINT (75.76589229999999 11.4371235) 323 5 32 KERALA 4.0 KOZHIKODE ... 673323 False NaN NaN False NaN 2015 9 30 2015-09-30
4570 4603 12.029814 75.339770 POINT (75.33976970000001 12.0298139) 308 1 32 KERALA 2.0 KANNUR ... 670143 False NaN NaN False NaN 2001 9 30 2001-09-30
4571 4585 12.073890 75.295961 POINT (75.29596120000001 12.0738897) 308 1 32 KERALA 2.0 KANNUR ... 670503 False NaN NaN True NaN 2002 9 30 2002-09-30
4572 4587 12.484214 74.992198 POINT (74.992198 12.484214) 301 1 32 KERALA 1.0 KASARAGOD ... 671121 False NaN NaN False NaN 2002 9 30 2002-09-30
4573 16136 12.930885 77.620721 POINT (77.62072090000001 12.9308848) 2385 26 29 KARNATAKA 20.0 BANGALORE ... 523257 False NaN NaN False NaN 2016 9 30 2016-09-30
4574 4351 12.930885 77.620721 POINT (77.62072090000001 12.9308848) 2385 26 29 KARNATAKA 20.0 BANGALORE ... 576007 False NaN NaN False NaN 2003 9 30 2003-09-30
4575 4336 12.956910 77.541050 POINT (77.5410505 12.95691) 2379 26 29 KARNATAKA 20.0 BANGALORE ... 562162 False NaN NaN False NaN 2004 9 30 2004-09-30
4576 15492 19.281366 84.791995 POINT (84.791995 19.2813656) 3250 20 21 ORISSA 19.0 GANJAM ... 760010 False NaN NaN False NaN 2011 9 30 2011-09-30
4577 2283 19.299307 84.863917 POINT (84.8639167 19.2993072) 3250 20 21 ORISSA 19.0 GANJAM ... 760010 False NaN NaN False NaN 2004 9 30 2004-09-30
4578 12459 22.360494 82.757349 POINT (82.757349 22.3604944) 1585 4 22 CHHATTISGARH 5.0 KORBA * ... 495667 False NaN NaN False NaN 2005 9 30 2005-09-30
4579 4848 23.742035 92.720956 POINT (92.7209562 23.7420348) 3089 1 15 MIZORAM 3.0 AIZAWL ... 796009 False NaN NaN True NaN 2005 9 30 2005-09-30
4582 2591 25.034508 73.859927 POINT (73.8599269 25.0345077) 3565 22 8 RAJASTHAN 25.0 RAJSAMAND * ... 313326 False NaN NaN False NaN 2006 9 30 2006-09-30
4583 4847 25.544750 91.886897 POINT (91.88689649999999 25.5447502) 3051 1 17 MEGHALAYA 6.0 EAST KHASI HILLS ... 793010 False NaN NaN False NaN 2007 9 30 2007-09-30
4584 8937 25.558905 91.904315 POINT (91.9043147 25.5589052) 3048 1 17 MEGHALAYA 6.0 EAST KHASI HILLS ... 793014 False NaN 22.0 False NaN 2008 9 30 2008-09-30
4586 3295 26.367552 79.629555 POINT (79.62955479999999 26.3675518) 4043 41 9 UTTAR PRADESH 33.0 KANPUR DEHAT ... 209125 False NaN NaN False NaN 2014 9 30 2014-09-30
4587 14490 31.634966 74.837252 POINT (74.83725150000001 31.6349661) 3310 2 3 PUNJAB 2.0 AMRITSAR ... 143002 True 8087.0 NaN False NaN 2010 9 30 2010-09-30
4588 3857 31.994829 76.789611 POINT (76.7896109 31.9948287) 2053 2 2 HIMACHAL PRADESH 5.0 MANDI ... 175015 False NaN NaN False NaN 2009 9 30 2009-09-30
4589 15889 16.757173 81.679963 POINT (81.67996290000001 16.7571731) 1143 26 28 ANDHRA PRADESH 15.0 WEST GODAVARI ... 634211 False NaN NaN False NaN 2015 9 30 2015-09-30

2995 rows × 63 columns

In [55]:
delta_year = None
#Get all variables
variables = [
    'colleges_opened',
    'colleges_opened1',
    'colleges_opened2',
    'colleges_opened3',
    'colleges_opened4',
    'colleges_opened5',
    'universities_opened',
    'universities_opened1',
    'universities_opened2',
    'universities_opened3',
    'universities_opened4',
    'universities_opened5',
    'standalone_institutes_opened',
    'standalone_institutes_opened1',
    'standalone_institutes_opened2',
    'standalone_institutes_opened3',
    'standalone_institutes_opened4',
    'standalone_institutes_opened5'
]
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 = colleges_merge_df[(colleges_merge_df['ST_CODE'] == row['state_code']) & (colleges_merge_df['AC_NO'] == row['constituency_no']) & (colleges_merge_df['datetime'] > row['datetime']) & (colleges_merge_df['datetime'] <= next_elect['datetime'].values[0])]
    colleges_opened = df1.shape[0]
    colleges_opened1 = df1[df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+0))].shape[0]
    colleges_opened2 = df1[(df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+1))) & (df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+0)))].shape[0]
    colleges_opened3 = df1[(df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+2))) & (df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+1)))].shape[0]
    colleges_opened4 = df1[(df1['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+3))) & (df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+2)))].shape[0]
    colleges_opened5 = df1[df1['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+3))].shape[0]
    
    df2 = university_merge_df[(university_merge_df['ST_CODE'] == row['state_code']) & (university_merge_df['AC_NO'] == row['constituency_no']) & (university_merge_df['datetime'] > row['datetime']) & (university_merge_df['datetime'] <= next_elect['datetime'].values[0])]
    universities_opened = df2.shape[0]
    universities_opened1 = df2[df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+0))].shape[0]
    universities_opened2 = df2[(df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+1))) & (df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+0)))].shape[0]
    universities_opened3 = df2[(df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+2))) & (df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+1)))].shape[0]
    universities_opened4 = df2[(df2['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+3))) & (df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+2)))].shape[0]
    universities_opened5 = df2[df2['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+3))].shape[0]
    
    df3 = standalone_merge_df[(standalone_merge_df['ST_CODE'] == row['state_code']) & (standalone_merge_df['AC_NO'] == row['constituency_no']) & (standalone_merge_df['datetime'] > row['datetime']) & (standalone_merge_df['datetime'] <= next_elect['datetime'].values[0])]
    standalone_institutes_opened = df3.shape[0]
    standalone_institutes_opened1 = df3[df3['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+0))].shape[0]
    standalone_institutes_opened2 = df3[(df3['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+1))) & (df3['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+0)))].shape[0]
    standalone_institutes_opened3 = df3[(df3['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+2))) & (df3['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+1)))].shape[0]
    standalone_institutes_opened4 = df3[(df3['datetime'] <= (row['datetime'] + pd.DateOffset(years=delta_year+3))) & (df3['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+2)))].shape[0]
    standalone_institutes_opened5 = df3[df3['datetime'] > (row['datetime'] + pd.DateOffset(years=delta_year+3))].shape[0]

    return colleges_opened, colleges_opened1, colleges_opened2, colleges_opened3, colleges_opened4, colleges_opened5, universities_opened, universities_opened1, universities_opened2, universities_opened3, universities_opened4, universities_opened5, standalone_institutes_opened, standalone_institutes_opened1, standalone_institutes_opened2, standalone_institutes_opened3, standalone_institutes_opened4, standalone_institutes_opened5
#Apply This Function
acdf[variables] = acdf.apply(get_ac_variables, axis=1, result_type='expand')
acdf.sort_values(axis=0, by='colleges_opened')
Out[55]:
state_name state_code constituency_no year month day datetime OBJECTID ST_CODE ST_NAME ... universities_opened2 universities_opened3 universities_opened4 universities_opened5 standalone_institutes_opened standalone_institutes_opened1 standalone_institutes_opened2 standalone_institutes_opened3 standalone_institutes_opened4 standalone_institutes_opened5
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
4796 Odisha 21 95 2009 5 1 2009-05-01 15 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4795 Odisha 21 94 2014 5 1 2014-05-01 15 21 ORISSA ... 0 0 0 0 1 1 0 0 0 0
4793 Odisha 21 93 2014 5 1 2014-05-01 14 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4792 Odisha 21 93 2009 5 1 2009-05-01 14 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4791 Odisha 21 92 2014 5 1 2014-05-01 16 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4789 Odisha 21 91 2014 5 1 2014-05-01 14 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4788 Odisha 21 91 2009 5 1 2009-05-01 14 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4787 Odisha 21 90 2014 5 1 2014-05-01 14 21 ORISSA ... 0 0 0 0 1 1 0 0 0 0
4786 Odisha 21 90 2009 5 1 2009-05-01 14 21 ORISSA ... 0 0 0 0 1 0 0 1 0 0
4785 Odisha 21 89 2014 5 1 2014-05-01 14 21 ORISSA ... 0 0 0 0 1 1 0 0 0 0
4797 Odisha 21 95 2014 5 1 2014-05-01 15 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4783 Odisha 21 88 2014 5 1 2014-05-01 14 21 ORISSA ... 1 0 0 0 1 1 0 0 0 0
4780 Odisha 21 87 2009 5 1 2009-05-01 14 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4779 Odisha 21 86 2014 5 1 2014-05-01 13 21 ORISSA ... 0 0 0 0 1 1 0 0 0 0
4778 Odisha 21 86 2009 5 1 2009-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4777 Odisha 21 85 2014 5 1 2014-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4776 Odisha 21 85 2009 5 1 2009-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4775 Odisha 21 84 2014 5 1 2014-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4773 Odisha 21 83 2014 5 1 2014-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4772 Odisha 21 83 2009 5 1 2009-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4771 Odisha 21 82 2014 5 1 2014-05-01 13 21 ORISSA ... 0 0 0 0 1 1 0 0 0 0
4770 Odisha 21 82 2009 5 1 2009-05-01 13 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4781 Odisha 21 87 2014 5 1 2014-05-01 14 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4769 Odisha 21 81 2014 5 1 2014-05-01 11 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4798 Odisha 21 96 2009 5 1 2009-05-01 15 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4800 Odisha 21 97 2009 5 1 2009-05-01 15 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4825 Odisha 21 109 2014 5 1 2014-05-01 17 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4824 Odisha 21 109 2009 5 1 2009-05-01 17 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
4823 Odisha 21 108 2014 5 1 2014-05-01 17 21 ORISSA ... 0 0 0 0 0 0 0 0 0 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5108 Madhya Pradesh 23 14 2008 12 1 2008-12-01 3 23 MADHYA PRADESH ... 1 0 0 0 2 0 0 0 1 1
1828 Uttar Pradesh 9 159 2012 3 1 2012-03-01 32 9 UTTAR PRADESH ... 0 0 0 0 0 0 0 0 0 0
1406 Rajasthan 8 148 2008 12 1 2008-12-01 18 8 RAJASTHAN ... 0 0 0 2 0 0 0 0 0 0
6529 Andhra Pradesh 28 48 2014 5 1 2014-05-01 14 28 ANDHRA PRADESH ... 0 0 0 0 0 0 0 0 0 0
7427 Karnataka 29 203 2013 5 1 2013-05-01 17 29 KARNATAKA ... 0 0 0 0 0 0 0 0 0 0
1223 Rajasthan 8 56 2013 12 1 2013-12-01 7 8 RAJASTHAN ... 0 0 0 0 0 0 0 0 0 0
5620 Gujarat 24 44 2012 12 1 2012-12-01 8 24 GUJARAT ... 0 1 0 0 2 1 1 0 0 0
1126 Rajasthan 8 9 2008 12 1 2008-12-01 1 8 RAJASTHAN ... 0 0 0 0 2 0 1 0 0 1
6278 Maharashtra 27 211 2009 10 1 2009-10-01 35 27 MAHARASHTRA ... 0 0 0 0 1 0 1 0 0 0
6528 Andhra Pradesh 28 48 2009 5 1 2009-05-01 14 28 ANDHRA PRADESH ... 0 0 0 0 3 1 0 0 2 0
5388 Madhya Pradesh 23 154 2008 12 1 2008-12-01 19 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
8074 Tamil Nadu 33 122 2011 5 1 2011-05-01 21 33 TAMIL NADU ... 0 0 0 0 1 0 0 0 1 0
5716 Gujarat 24 104 2012 12 1 2012-12-01 15 24 GUJARAT ... 0 0 0 0 0 0 0 0 0 0
7388 Karnataka 29 184 2008 5 1 2008-05-01 23 29 KARNATAKA ... 0 0 0 0 1 0 1 0 0 0
6534 Andhra Pradesh 28 51 2009 5 1 2009-05-01 10 28 ANDHRA PRADESH ... 0 0 0 0 4 2 0 0 2 0
5510 Madhya Pradesh 23 219 2008 12 1 2008-12-01 24 23 MADHYA PRADESH ... 0 0 0 0 6 1 2 2 1 0
5380 Madhya Pradesh 23 150 2008 12 1 2008-12-01 19 23 MADHYA PRADESH ... 0 0 0 0 0 0 0 0 0 0
7320 Karnataka 29 150 2008 5 1 2008-05-01 27 29 KARNATAKA ... 0 0 0 0 2 0 2 0 0 0
5608 Gujarat 24 38 2012 12 1 2012-12-01 6 24 GUJARAT ... 0 0 0 0 0 0 0 0 0 0
1368 Rajasthan 8 129 2008 12 1 2008-12-01 16 8 RAJASTHAN ... 0 0 1 0 1 0 1 0 0 0
5732 Gujarat 24 112 2012 12 1 2012-12-01 16 24 GUJARAT ... 0 0 0 0 0 0 0 0 0 0
6262 Maharashtra 27 203 2009 10 1 2009-10-01 35 27 MAHARASHTRA ... 0 0 0 0 3 2 0 0 0 1
5390 Madhya Pradesh 23 155 2008 12 1 2008-12-01 19 23 MADHYA PRADESH ... 0 0 1 0 3 1 2 0 0 0
6232 Maharashtra 27 188 2009 10 1 2009-10-01 33 27 MAHARASHTRA ... 0 0 0 0 1 1 0 0 0 0
1180 Rajasthan 8 35 2008 12 1 2008-12-01 5 8 RAJASTHAN ... 0 0 1 0 2 2 0 0 0 0
7168 Karnataka 29 74 2008 5 1 2008-05-01 11 29 KARNATAKA ... 1 0 0 0 1 1 0 0 0 0
6524 Andhra Pradesh 28 46 2009 5 1 2009-05-01 7 28 ANDHRA 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
7426 Karnataka 29 203 2008 5 1 2008-05-01 17 29 KARNATAKA ... 0 0 0 0 0 0 0 0 0 0
1222 Rajasthan 8 56 2008 12 1 2008-12-01 7 8 RAJASTHAN ... 0 0 2 1 1 0 1 0 0 0

8358 rows × 35 columns

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