Create Turkish Voter Profile Database With Web Scraping

from selenium import webdriver
import chromedriver_autoinstaller
from selenium.webdriver.common.by import By
import time
chromedriver_autoinstaller.install()

###### Start webdriver
driver = webdriver.Chrome()
time.sleep(1)

###### Go to the main site
driver.get("https://biruni.tuik.gov.tr/secimdagitimapp/secimsecmen.zul")
time.sleep(1)

###### Select radio buttons for the desired areas
for span in driver.find_elements(By.CSS_SELECTOR, ".grid-od span"):
    if span.text == "2018":
        span.find_element(By.CSS_SELECTOR, "input").click()
        time.sleep(1)


for span in driver.find_elements(By.CSS_SELECTOR, ".grid-od span"):
    if span.text == "Yurt içi seçmen":
        span.find_element(By.CSS_SELECTOR, "input").click()
        time.sleep(1)

for span in driver.find_elements(By.CSS_SELECTOR, ".grid-od span"):
    if span.text == "Yaş grubu":
        span.find_element(By.CSS_SELECTOR, "input").click()
        time.sleep(1)

for span in driver.find_elements(By.CSS_SELECTOR, ".grid .gc span"):
    if span.text == "İBBS-Düzey4 (İlçe)":
        span.find_element(By.CSS_SELECTOR, "input").click()
        time.sleep(1)

###### Identify the cities in city (There should be 81)
tables = driver.find_elements(By.CSS_SELECTOR, ".listbox-btable")

for table in tables:
    if "Adana" in table.text:
        cities = table.find_elements(By.CSS_SELECTOR, "td")
        break

city_names = [city.text for city in cities]

###### Save data per district
for city in cities:
    city.click()
    time.sleep(2)

    tables = driver.find_elements(By.CSS_SELECTOR, ".listbox-btable")

    for table in tables:
        if "Tüm İlçeler" in table.text:
            districts = table.find_elements(By.CSS_SELECTOR, "td")
            break

    district_names = [district.text for district in districts]
    districts = [district for district in districts if "Tüm İlçeler" not in district.text]
    
    for district in districts:
        district.click()
        time.sleep(1)
    
        # Set Excel
        excel =  driver.find_element(By.CSS_SELECTOR, 'span[title="EXCEL"]').find_element(By.CSS_SELECTOR, 'input').click()
        time.sleep(1)
        
        ## Save data
        save =  driver.find_element(By.CSS_SELECTOR, 'input[value="Raporu Oluştur"]').click()
        
        ## Log, Sleep
        print(city.text, district.text)
        time.sleep(1)

import pandas as pd
import os
from bs4 import BeautifulSoup
import warnings
warnings.simplefilter(action='ignore')

folder = "<Address/with/downloaded/files/from/TUIK>"

paths = [f for f in os.listdir(folder) if f.endswith("xls")]
dfs = []

for path in paths:
    with open(f"{folder}/{path}", "r", encoding="iso-8859-9") as f:
        text = f.read()
        soup = BeautifulSoup(text, "html")
        tables = pd.read_html(str(soup.findAll("table")[1]))
        city = tables[0].iloc[0, 0]
        df = tables[2]
        df.drop(1, axis=1, inplace=True)
        district = df.iloc[0, 0]
        df.columns = ["age", "male", "female", "all"]
        df["city"] = city
        df["district"] = district
        df = df[1:]
        for col in df.columns:
            df[col] = df[col].str.replace(".", "", regex=False)
        df = df[df["age"] != "Toplam"]
        df["male"] = pd.to_numeric(df["male"])
        df["female"] = pd.to_numeric(df["female"])
        df["all"] = pd.to_numeric(df["all"])

        dfs.append(df)
        
df = pd.concat(dfs, axis=0, ignore_index=True)
df.to_csv("data.csv", index=False)