# 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
# Set Output Folder
output_folder = os.path.abspath("output")
if not os.path.exists(output_folder):
os.makedirs(output_folder)
# Import data folders
data_folder = os.path.abspath("data")
# Helping Functions
def remove_consecutive_duplicates(x):
return ''.join(i for i, _ in itertools.groupby(x))
# 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
| 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
# Read Match Geocoded Data
match_geocoded_file = os.path.join(data_folder, "match_geocoded.csv")
match_geocoded_df = pd.read_csv(match_geocoded_file)
match_geocoded_df
| panchayat | district | state | ac_code | state_code | |
|---|---|---|---|---|---|
| 0 | ARARIA BASTI | ARARIA | BIHAR | 49.0 | 10.0 |
| 1 | BANGAMA | ARARIA | BIHAR | 49.0 | 10.0 |
| 2 | BANSBARI | ARARIA | BIHAR | 49.0 | 10.0 |
| 3 | BASANTPUR | ARARIA | BIHAR | 49.0 | 10.0 |
| 4 | BATURBARI | ARARIA | BIHAR | 49.0 | 10.0 |
| 5 | BELWA | ARARIA | BIHAR | 49.0 | 10.0 |
| 6 | BOCHI | ARARIA | BIHAR | 49.0 | 10.0 |
| 7 | CHATAR | ARARIA | BIHAR | 49.0 | 10.0 |
| 8 | DIYARI | ARARIA | BIHAR | 49.0 | 10.0 |
| 9 | GAIYARI | ARARIA | BIHAR | 49.0 | 10.0 |
| 10 | HAYATPUR | ARARIA | BIHAR | 49.0 | 10.0 |
| 11 | JAMUA | ARARIA | BIHAR | 49.0 | 10.0 |
| 12 | JHAMTA | ARARIA | BIHAR | 49.0 | 10.0 |
| 13 | KAMALDAHA | ARARIA | BIHAR | 49.0 | 10.0 |
| 14 | KAMALDAHA | ARARIA | BIHAR | 51.0 | 10.0 |
| 15 | KISMAT KHAWASPUR | ARARIA | BIHAR | 49.0 | 10.0 |
| 16 | PAIKTOLA | ARARIA | BIHAR | 49.0 | 10.0 |
| 17 | RAMPUR KODARKATTI | ARARIA | BIHAR | 49.0 | 10.0 |
| 18 | SAHASMAL | ARARIA | BIHAR | 49.0 | 10.0 |
| 19 | SHARANPUR | ARARIA | BIHAR | 49.0 | 10.0 |
| 20 | BAGNAGAR | ARARIA | BIHAR | 50.0 | 10.0 |
| 21 | BARA ISTAMBARAR | ARARIA | BIHAR | 50.0 | 10.0 |
| 22 | BHAGWANPUR | ARARIA | BIHAR | 50.0 | 10.0 |
| 23 | BHUNA MAJGAMA | ARARIA | BIHAR | 50.0 | 10.0 |
| 24 | CHAKAI | ARARIA | BIHAR | 50.0 | 10.0 |
| 25 | CHIRAH | ARARIA | BIHAR | 50.0 | 10.0 |
| 26 | DUBBA | ARARIA | BIHAR | 50.0 | 10.0 |
| 27 | GIRDA | ARARIA | BIHAR | 50.0 | 10.0 |
| 28 | HARDAR | ARARIA | BIHAR | 50.0 | 10.0 |
| 29 | KAKAN | ARARIA | BIHAR | 50.0 | 10.0 |
| ... | ... | ... | ... | ... | ... |
| 123628 | BOJJAGUDEM THANDA | NALGONDA | TELANGANA | 95.0 | 28.0 |
| 123629 | MAMILLAGUDEM | NALGONDA | TELANGANA | 92.0 | 28.0 |
| 123630 | THUMMALA PALLE | NALGONDA | TELANGANA | 93.0 | 28.0 |
| 123631 | KANCHAN PALLE | NALGONDA | TELANGANA | 92.0 | 28.0 |
| 123632 | KESHAVAPURAM | NALGONDA | TELANGANA | 88.0 | 28.0 |
| 123633 | KESHAVAPURAM | NALGONDA | TELANGANA | 92.0 | 28.0 |
| 123634 | YELLA PURAM | NALGONDA | TELANGANA | 86.0 | 28.0 |
| 123635 | YELLA PURAM | NALGONDA | TELANGANA | 87.0 | 28.0 |
| 123636 | LINGAM PALLE | NALGONDA | TELANGANA | 87.0 | 28.0 |
| 123637 | POCHAMPALLE | NALGONDA | TELANGANA | 87.0 | 28.0 |
| 123638 | RAJAPET | NALGONDA | TELANGANA | 92.0 | 28.0 |
| 123639 | RAMACHANDRA PURAM | NALGONDA | TELANGANA | 95.0 | 28.0 |
| 123640 | SUNKI SHALA | NALGONDA | TELANGANA | 93.0 | 28.0 |
| 123641 | SUNKI SHALA | NALGONDA | TELANGANA | 87.0 | 28.0 |
| 123642 | KACHARAM | NALGONDA | TELANGANA | 86.0 | 28.0 |
| 123643 | TIRMALAPUR | NIZAMABAD | TELANGANA | 14.0 | 28.0 |
| 123644 | BASWAPUR | NIZAMABAD | TELANGANA | 19.0 | 28.0 |
| 123645 | BASWAPUR | NIZAMABAD | TELANGANA | 14.0 | 28.0 |
| 123646 | MIRZAPUR | NIZAMABAD | TELANGANA | 14.0 | 28.0 |
| 123647 | MALKAPUR | NIZAMABAD | TELANGANA | 18.0 | 28.0 |
| 123648 | MALKAPUR | NIZAMABAD | TELANGANA | 17.0 | 28.0 |
| 123649 | MALKAPUR | NIZAMABAD | TELANGANA | 14.0 | 28.0 |
| 123650 | HANGARGA | NIZAMABAD | TELANGANA | 12.0 | 28.0 |
| 123651 | DEVANPALLE | NIZAMABAD | TELANGANA | 19.0 | 28.0 |
| 123652 | AREPALLE | NIZAMABAD | TELANGANA | 18.0 | 28.0 |
| 123653 | MACHAPUR | NIZAMABAD | TELANGANA | 12.0 | 28.0 |
| 123654 | GOBINDAPUR | PASCHIM BARDHAMAN | WEST BENGAL | 275.0 | 19.0 |
| 123655 | BHURI | PASCHIM BARDHAMAN | WEST BENGAL | 279.0 | 19.0 |
| 123656 | KHASA | AMRITSAR | PUNJAB | 20.0 | 3.0 |
| 123657 | HEIR | AMRITSAR | PUNJAB | 20.0 | 3.0 |
123658 rows × 5 columns
# Google Geocoded villages
google_geocoded_file = os.path.join(data_folder, 'google_geocoded.csv')
google_df = pd.read_csv(google_geocoded_file, encoding = "ISO-8859-1")
google_df_columns = list(google_df.columns)
google_df = google_df[google_df_columns[1:4] + google_df_columns[9:11]]
#Change Google Geocoded villages dataframe to geospatial and get AC data
geometry = [Point(xy) for xy in zip(google_df._longitude.apply(pd.to_numeric, errors='coerce'), google_df._latitude.apply(pd.to_numeric, errors='coerce'))]
crs = {'init': 'epsg:4326'}
google_geo_df = gpd.GeoDataFrame(google_df, crs=crs, geometry=geometry)
# Spatial Join
google_geo_df = gpd.sjoin(google_geo_df, ac_gdf, how='inner', op='within')
google_geo_df_columns = list(google_geo_df.columns)
google_geo_df = google_geo_df[google_geo_df_columns[0:3] + google_geo_df_columns[12:13] + google_geo_df_columns[8:9]]
google_geo_df.columns = ['panchayat', 'state', 'district', 'ac_code', 'state_code']
google_geo_df
| panchayat | state | district | ac_code | state_code | |
|---|---|---|---|---|---|
| 0 | CHANDARDEI | BIHAR | ARARIA | 235 | 10 |
| 87 | BELWAN | BIHAR | AURANAGABAD | 235 | 10 |
| 1464 | GOVIND BIGHA | BIHAR | NAWADA | 235 | 10 |
| 1525 | AMAWA WEST | BIHAR | NAWADA | 235 | 10 |
| 1526 | ANDHARWARI | BIHAR | NAWADA | 235 | 10 |
| 1527 | RAJAULI WEST | BIHAR | NAWADA | 235 | 10 |
| 1529 | DHAMANI | BIHAR | NAWADA | 235 | 10 |
| 1534 | RAJAULI EAST | BIHAR | NAWADA | 235 | 10 |
| 36440 | AKRI PANDEY BIGHA PANCHYAT | BIHAR | NAWADA | 235 | 10 |
| 36442 | BIJU BIGHA PANCHYAT | BIHAR | NAWADA | 235 | 10 |
| 36443 | BISIAIT PANCHYAT | BIHAR | NAWADA | 235 | 10 |
| 36444 | MESKAUR PANCHYAT | BIHAR | NAWADA | 235 | 10 |
| 36445 | MIRZAPUR PANCHYAT | BIHAR | NAWADA | 235 | 10 |
| 1 | HARIYA | BIHAR | ARARIA | 49 | 10 |
| 2 | KUSIYARGAON | BIHAR | ARARIA | 49 | 10 |
| 5 | MADANPUR (E) | BIHAR | ARARIA | 49 | 10 |
| 6 | RAMPUR MOHANPUR (E) | BIHAR | ARARIA | 49 | 10 |
| 7 | MADANPUR (W) | BIHAR | ARARIA | 49 | 10 |
| 11 | RAMPUR MOHANPUR (W) | BIHAR | ARARIA | 49 | 10 |
| 15 | CHOUKTA | BIHAR | ARARIA | 49 | 10 |
| 19 | SISOUNA | BIHAR | ARARIA | 49 | 10 |
| 22 | DEHTI (N) | BIHAR | ARARIA | 49 | 10 |
| 24 | MIYANPUR | BIHAR | ARARIA | 49 | 10 |
| 2398 | KHOKHA DAKSHIN | BIHAR | PURNIA | 49 | 10 |
| 10377 | AMOUNA | BIHAR | ARARIA | 49 | 10 |
| 10383 | RAMPUR (S) | BIHAR | ARARIA | 49 | 10 |
| 3 | GAIRA | BIHAR | ARARIA | 203 | 10 |
| 1002 | KHARASARA | BIHAR | KAIMUR (BHABUA) | 203 | 10 |
| 71154 | KESHOPUR | UTTAR PRADESH | ETAWAH | 203 | 10 |
| 4 | PAKHARIYA | BIHAR | ARARIA | 174 | 10 |
| ... | ... | ... | ... | ... | ... |
| 154564 | MOLKONBUNG | MANIPUR | SENAPATI | 7 | 14 |
| 154572 | POIROU TANGKHUL | MANIPUR | SENAPATI | 31 | 14 |
| 155046 | DHIR | PUNJAB | GURDASPUR | 9 | 3 |
| 155049 | LONGOWAL | PUNJAB | GURDASPUR | 9 | 3 |
| 155264 | HABAT PINDI | PUNJAB | PATHANKOT | 9 | 3 |
| 155048 | KOTLA BAJJA SINGH | PUNJAB | GURDASPUR | 8 | 3 |
| 155051 | KHUNDI | PUNJAB | GURDASPUR | 8 | 3 |
| 155089 | CHEEMA | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155090 | DAGAN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155093 | KOLPUR | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155101 | ULAH | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155103 | BAROTI | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155104 | BASSI GULAM HUSSAIN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155111 | SAINCHAN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155112 | SARAIN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155115 | BAHADURPUR BAHIAN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155118 | CHAK SADHU | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155119 | CHOHAL | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155120 | DADA | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155122 | HARMOYA | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155123 | HUKRAN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155127 | NARI | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155142 | BHANGALA NEW | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155145 | DEVI DAS | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155160 | BATWARA | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155163 | BEH JOGAN | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155167 | BHAVNAUR | PUNJAB | HOSHIARPUR | 43 | 3 |
| 155212 | TAJPUR BET | PUNJAB | LUDHIANA | 60 | 3 |
| 155218 | GHARKHAN | PUNJAB | LUDHIANA | 60 | 3 |
| 155222 | LALLAURI KALAN | PUNJAB | LUDHIANA | 60 | 3 |
154996 rows × 5 columns
geocoded_df = pd.concat([match_geocoded_df, google_geo_df])
geocoded_df
C:\Users\sandyjones\AppData\Local\conda\conda\envs\geo\lib\site-packages\ipykernel_launcher.py:1: 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'. """Entry point for launching an IPython kernel.
| ac_code | district | panchayat | state | state_code | |
|---|---|---|---|---|---|
| 0 | 49.0 | ARARIA | ARARIA BASTI | BIHAR | 10.0 |
| 1 | 49.0 | ARARIA | BANGAMA | BIHAR | 10.0 |
| 2 | 49.0 | ARARIA | BANSBARI | BIHAR | 10.0 |
| 3 | 49.0 | ARARIA | BASANTPUR | BIHAR | 10.0 |
| 4 | 49.0 | ARARIA | BATURBARI | BIHAR | 10.0 |
| 5 | 49.0 | ARARIA | BELWA | BIHAR | 10.0 |
| 6 | 49.0 | ARARIA | BOCHI | BIHAR | 10.0 |
| 7 | 49.0 | ARARIA | CHATAR | BIHAR | 10.0 |
| 8 | 49.0 | ARARIA | DIYARI | BIHAR | 10.0 |
| 9 | 49.0 | ARARIA | GAIYARI | BIHAR | 10.0 |
| 10 | 49.0 | ARARIA | HAYATPUR | BIHAR | 10.0 |
| 11 | 49.0 | ARARIA | JAMUA | BIHAR | 10.0 |
| 12 | 49.0 | ARARIA | JHAMTA | BIHAR | 10.0 |
| 13 | 49.0 | ARARIA | KAMALDAHA | BIHAR | 10.0 |
| 14 | 51.0 | ARARIA | KAMALDAHA | BIHAR | 10.0 |
| 15 | 49.0 | ARARIA | KISMAT KHAWASPUR | BIHAR | 10.0 |
| 16 | 49.0 | ARARIA | PAIKTOLA | BIHAR | 10.0 |
| 17 | 49.0 | ARARIA | RAMPUR KODARKATTI | BIHAR | 10.0 |
| 18 | 49.0 | ARARIA | SAHASMAL | BIHAR | 10.0 |
| 19 | 49.0 | ARARIA | SHARANPUR | BIHAR | 10.0 |
| 20 | 50.0 | ARARIA | BAGNAGAR | BIHAR | 10.0 |
| 21 | 50.0 | ARARIA | BARA ISTAMBARAR | BIHAR | 10.0 |
| 22 | 50.0 | ARARIA | BHAGWANPUR | BIHAR | 10.0 |
| 23 | 50.0 | ARARIA | BHUNA MAJGAMA | BIHAR | 10.0 |
| 24 | 50.0 | ARARIA | CHAKAI | BIHAR | 10.0 |
| 25 | 50.0 | ARARIA | CHIRAH | BIHAR | 10.0 |
| 26 | 50.0 | ARARIA | DUBBA | BIHAR | 10.0 |
| 27 | 50.0 | ARARIA | GIRDA | BIHAR | 10.0 |
| 28 | 50.0 | ARARIA | HARDAR | BIHAR | 10.0 |
| 29 | 50.0 | ARARIA | KAKAN | BIHAR | 10.0 |
| ... | ... | ... | ... | ... | ... |
| 154564 | 7.0 | SENAPATI | MOLKONBUNG | MANIPUR | 14.0 |
| 154572 | 31.0 | SENAPATI | POIROU TANGKHUL | MANIPUR | 14.0 |
| 155046 | 9.0 | GURDASPUR | DHIR | PUNJAB | 3.0 |
| 155049 | 9.0 | GURDASPUR | LONGOWAL | PUNJAB | 3.0 |
| 155264 | 9.0 | PATHANKOT | HABAT PINDI | PUNJAB | 3.0 |
| 155048 | 8.0 | GURDASPUR | KOTLA BAJJA SINGH | PUNJAB | 3.0 |
| 155051 | 8.0 | GURDASPUR | KHUNDI | PUNJAB | 3.0 |
| 155089 | 43.0 | HOSHIARPUR | CHEEMA | PUNJAB | 3.0 |
| 155090 | 43.0 | HOSHIARPUR | DAGAN | PUNJAB | 3.0 |
| 155093 | 43.0 | HOSHIARPUR | KOLPUR | PUNJAB | 3.0 |
| 155101 | 43.0 | HOSHIARPUR | ULAH | PUNJAB | 3.0 |
| 155103 | 43.0 | HOSHIARPUR | BAROTI | PUNJAB | 3.0 |
| 155104 | 43.0 | HOSHIARPUR | BASSI GULAM HUSSAIN | PUNJAB | 3.0 |
| 155111 | 43.0 | HOSHIARPUR | SAINCHAN | PUNJAB | 3.0 |
| 155112 | 43.0 | HOSHIARPUR | SARAIN | PUNJAB | 3.0 |
| 155115 | 43.0 | HOSHIARPUR | BAHADURPUR BAHIAN | PUNJAB | 3.0 |
| 155118 | 43.0 | HOSHIARPUR | CHAK SADHU | PUNJAB | 3.0 |
| 155119 | 43.0 | HOSHIARPUR | CHOHAL | PUNJAB | 3.0 |
| 155120 | 43.0 | HOSHIARPUR | DADA | PUNJAB | 3.0 |
| 155122 | 43.0 | HOSHIARPUR | HARMOYA | PUNJAB | 3.0 |
| 155123 | 43.0 | HOSHIARPUR | HUKRAN | PUNJAB | 3.0 |
| 155127 | 43.0 | HOSHIARPUR | NARI | PUNJAB | 3.0 |
| 155142 | 43.0 | HOSHIARPUR | BHANGALA NEW | PUNJAB | 3.0 |
| 155145 | 43.0 | HOSHIARPUR | DEVI DAS | PUNJAB | 3.0 |
| 155160 | 43.0 | HOSHIARPUR | BATWARA | PUNJAB | 3.0 |
| 155163 | 43.0 | HOSHIARPUR | BEH JOGAN | PUNJAB | 3.0 |
| 155167 | 43.0 | HOSHIARPUR | BHAVNAUR | PUNJAB | 3.0 |
| 155212 | 60.0 | LUDHIANA | TAJPUR BET | PUNJAB | 3.0 |
| 155218 | 60.0 | LUDHIANA | GHARKHAN | PUNJAB | 3.0 |
| 155222 | 60.0 | LUDHIANA | LALLAURI KALAN | PUNJAB | 3.0 |
278654 rows × 5 columns
geocoded_df.to_csv(os.path.join(output_folder, 'geocoded.csv'), encoding='utf-8', index=False)