I am totally Beginner in python.
I wrote a program which is get the table from the website by selenium in webscrapping. save it in to the excel file at the end open this excel file , edited some of the data in column make it standart for our DATABASE then save it in to the another excel file.
I need this chapters 02,04,10,11,12,15,20,23,38
each chapter has for example 23 more or less pages of the tables.
The problem which I have is that whenever it save in first excel file any previous chapter eleminated. AT THE END I have just chapter 38 in my first excel and second excel file.
I really wonder if anybody can help me.
from selenium.webdriver.support.select import Select
from prettytable import PrettyTable
import os, platform
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.common.by import By
import pandas as pd
import time
# Clear the screen
def clear():
if platform.system() == 'Windows':
os.system('cls')
else:
os.system('clear')
# For Making the Pretty Table in the first Page
columns = ["Please Enter your number for each country", ""]
myTable = PrettyTable()
myTable.add_column(columns[0],["1- Canada", "2- United Kingdom","3- South Korea","4- Turkey","5- New Zealand"])
myTable.add_column(columns[1],["","","","",""])
print(myTable)
#------------((Canada- Import Procedure, For selecting the the chapters and retreieve the data))----------------
def Canada_Im():
Ca_Im_Start_Year = input("Start Year like '20XX':")
Ca_Im_Start_Month = input("Start Month like 'X':")
Ca_Im_End_Year = input("End Year like '20XX':")
Ca_Im_End_Month= input("End month like 'X':")
driver = webdriver.Chrome('C:\Webdriver\chromedriver.exe')
driver.get('https://www150.statcan.gc.ca/n1/pub/71-607-x/71-607-x2021004-eng.htm')
time.sleep(2)
# for clicking the import button
driver.find_element(By.XPATH,'//*[@id="cimt_import"]/p[1]/a').click()
time.sleep(2)
# for clicking the Data Extraction button
driver.find_element(By.XPATH,'//*[@id="topic3s"]').click()
time.sleep(1)
# for clicking the Start Year, month & End Year, month button
# Start year
dropdown = driver.find_element_by_id('fromYear')
dd = Select(dropdown)
dd.select_by_value(str(Ca_Im_Start_Year))
# Start month
dropdown_fM = driver.find_element_by_id('fromMonth')
dd_fM = Select(dropdown_fM)
dd_fM.select_by_value(str(Ca_Im_Start_Month))
# End year
dropdown_tY = driver.find_element_by_id('toYear')
dd_tY = Select(dropdown_tY)
dd_tY.select_by_value(str(Ca_Im_End_Year))
# End month
dropdown_tM = driver.find_element_by_id('toMonth')
dd_tM = Select(dropdown_tM)
dd_tM.select_by_value(str(Ca_Im_End_Month))
# Commodity: Specific Commodity filed
dropdown_rH = driver.find_element_by_id('report_hs')
dd_rH = Select(dropdown_rH)
dd_rH.select_by_value("1")
# Commodity: Specific Chapter
ch_numbers = ["02","04","10","11","12","15","20","23","38"]
for ch_number in ch_numbers:
dropdown_rC = driver.find_element_by_id('report_chapters')
dd_rC = Select(dropdown_rC)
dd_rC.select_by_value(ch_number)
# for retriving the data and store it in the excel form
driver.find_element(By.XPATH,'//*[@id="report"]/div[1]/div[3]/div[5]/p[2]/button').click()
time.sleep(3)
Canada_Result=[]
pages = len(driver.find_elements_by_xpath('//a[@class="paginate_button" or @class="paginate_button current" and @title]'))
pages = driver.find_element_by_xpath('//a[@onclick and @class="paginate_button" or @class="paginate_button current" and @title][%d]' % (pages)).text.strip("Page\n")
if pages == '':
pages = 1
for J in range (int(pages)):
commodities = driver.find_elements_by_xpath('.//*[@id="report_table"]/tbody/tr["i"]/td[2]/a')
Countries = driver.find_elements_by_xpath('.//*[@id="report_table"]/tbody/tr["i"]/td[4]')
quantities = driver.find_elements_by_xpath('.//*[@id="report_table"]/tbody/tr["i"]/td[7]')
weights = driver.find_elements_by_xpath('.//*[@id="report_table"]/tbody/tr["i"]/td[8]/abbr')
time.sleep(2)
period_entries = len(commodities)
time.sleep(1)
for i in range(period_entries):
temporary_data= {'Commodity': commodities[i].text,'Country': Countries[i].text,'quantity': quantities[i].text, 'weight': weights[i].text }
Canada_Result.append(temporary_data)
df_data = pd.DataFrame(Canada_Result)
df_data
df_data.to_excel('Canada_scrapping_result.xlsx', index=False)
if J == int(pages) - 1:
print("Done")
break
# click on the Next button
driver.find_element(By.XPATH,'//*[@id="report_results_next"]').click()
time.sleep(1)
#----------for convert the data in excel and save it again---------------
df = pd.read_excel('Canada_scrapping_result.xlsx')
for i in range(len(df[df.columns[0]])):
string = df["Commodity"][i]
split_str = string.split("—") # Splits string at "—"
number = split_str[0]
df['Commodity'][i] = number.replace(".", "")
df['Commodity'][i] = df['Commodity'][i][:-5]
df.to_csv("Remove_characters.csv")
time.sleep(1)
# Click Submit
driver.find_element(By.XPATH,'//*[@id="report"]/div[1]/div[3]/div[5]/p[2]/button').click()
time.sleep(2)
# ******************************************************************************
# -------------------------((Canada Main Page))------------------------------
def canada_function():
# for chosing import or export function
clear()
columns = ["Please Choose Import or Export For CANADA",""]
myTable_canada = PrettyTable()
myTable_canada.add_column(columns[0],["1- import", "2- export"])
myTable_canada.add_column(columns[1],["",""])
print(myTable_canada)
print("\n")
canada_Im_Ex=input("Im/Ex:")
match canada_Im_Ex:
case "1":
Canada_Im()
case "2":
print("Please Enter month:")
# for getting the country Code
country_number=input("number:")
match country_number:
case "1":
clear()
print("Canada")
canada_function()
case "2":
print("United Kingdom")