<jupyter_start><jupyter_text>SCRAPING DEI <NAME><jupyter_code>#install packages for web scraping:Selenium (emulate a user on a web site) + chromium
!pip install selenium
!apt-get update
!apt install chromium-chromedriver
!cp /usr/lib/chromium-browser/chromedriver /usr/bin
import sys #to setup the path of chromedriver
import logging
from selenium import webdriver
from selenium.webdriver.remote.remote_connection import LOGGER
LOGGER.setLevel(logging.WARNING)
sys.path.insert(0,'/usr/lib/chromium-browser/chromedriver')
from tqdm import tqdm_notebook as tqdm
import pandas as pd
import json #to work with json file
import pprint #to print stuff in a more aesthetic way
#other stuff to work in a browser
chrome_options = webdriver.ChromeOptions() #With webdriver we can start the phantom browser, emulate the user navigation and scrape our data.
chrome_options.add_argument('--headless')
chrome_options.add_argument('--no-sandbox')
chrome_options.add_argument('--disable-dev-shm-usage')
chrome_options.add_argument("user-agent=Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.212 Safari/537.36")<jupyter_output><empty_output><jupyter_text>START SCRAPING<jupyter_code>wd = webdriver.Chrome('chromedriver',options=chrome_options) #è un oggetto che mi permette di interfacciarmi con web tramite richieste
def parse_train(train,railway):  
    departure_station = ""
    arrival_station = ""
    date = ""
    departure_hour =""
    return_hour = ""
    change = ""
    price = ""
    type_of_train = ""

    try:
      if (len(wd.find_elements_by_css_selector('div[id*="inner"]> div > div.col-info.result-column > div.top-row > div > div > div.bfLabel.bf-transportation')) > 0):
        pass
      if (len(train.find_elements_by_css_selector('div[id*="info-leg-0-origin-airport"]')) > 0):
          departure_station = train.find_elements_by_css_selector('div[id*="info-leg-0-origin-airport"]')[0].text
      if (len(train.find_elements_by_css_selector('div[id*="info-leg-0-destination-airport"]')) > 0):
          arrival_station = train.find_elements_by_css_selector('div[id*="info-leg-0-destination-airport"]')[0].text   
      if (len(train.find_elements_by_css_selector("span.date-text")) > 0):
          date = train.find_elements_by_css_selector("span.date-text")[0].text       
      if (len(train.find_elements_by_css_selector('div[id*="info-leg-0"]> div > div.section.times > div.top > span:nth-child(1) > span')) > 0):
          departure_hour = train.find_elements_by_css_selector('div[id*="info-leg-0"]> div > div.section.times > div.top > span:nth-child(1) > span')[0].text
      if (len(train.find_elements_by_css_selector('div[id*="info-leg-0"]> div > div.section.times > div.top > span:nth-child(3) > span')) > 0):
          return_hour = train.find_elements_by_css_selector('div[id*="info-leg-0"]> div > div.section.times > div.top > span:nth-child(3) > span')[0].text 
      if (len(train.find_elements_by_css_selector('div[id*="info-leg-0"]> div > div.section.stops > div.top > span')) > 0):
          change = train.find_elements_by_css_selector('div[id*="info-leg-0"]> div > div.section.stops > div.top > span')[0].text       
      if (len(train.find_elements_by_css_selector('span[id*="price-text"]')) > 0):
          price = train.find_elements_by_css_selector('span[id*="price-text"]')[0].text    
      if (len(train.find_elements_by_css_selector('div[id*="extra-info"]> div > div > span')) > 0):
          type_of_train = train.find_elements_by_css_selector('div[id*="extra-info"]> div > div > span')[0].text 
    except:
      pass
    return {'railway': railway,
            'departure station': departure_station,
            'arrival station': arrival_station, 
            'date' : departure_date,
            'departure time': departure_hour,
            'arrival time': return_hour,
            'change': change,
            'price': price,
            'type of train': type_of_train
            }
import time
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
from selenium.common.exceptions import WebDriverException
from tqdm import tqdm
import numpy as np

train_details = []
train_details_raw = []

#elenco delle tratte ferroviarie
# list_railways = ['MIL-TRN','MIL-ROM','MIL-PAR','MIL-GVA','MIL-VIE','MIL-ZRH','MIL-PRG','MIL-NAP','MUC-STR','GVA-PAR','BRU-PAR','BRU-AMS','BRU-HAJ','AMS-PAR','BER-HAJ','BER-PAR','ZRH-VIE','PRG-MUC','BUD-PRG','VIE-MUC','ZAG-VIE','PAR-NCE','ROM-PAR','ROM-GVA','ROM-VIE','ROM-ZRH','ROM-MUC','ROM-TRN','FRA-PAR','FRA-AMS','FRA-MUC','FRA-PRG','BER-MUC','PRG-VIE','WAW-PRG','WAW-BER','AMS-HAM','HAM-BER','HAM-BRU','LON-PAR','LON-BRU','LON-AMS','MAD-VLC','PAR-LYS','TRN-LYS','MIL-LYS','BLQ-ROM','TRN-PAR','LYS-NCE','MIL-NCE','TRN-NCE','BCN-MAD','BCN-VLC','LON-MAN','MAD-SVQ','AMS-DUS','LON-EDI','ROM-VCE','BOL-NAP','ROM-NAP','TRN-MIL','ROM-MIL','PAR-MIL','GVA-MIL','VIE-MIL','ZRH-MIL','PRG-MIL','NAP-MIL','STR-MUC','PAR-GVA','PAR-BRU','AMS-BRU','HAJ-BRU','PAR-AMS','HAJ-BER','PAR-BER','VIE-ZRH','MUC-PRG','PRG-BUD','MUC-VIE','VIE-ZAG','NCE-PAR','PAR-ROM','GVA-ROM','VIE-ROM','ZRH-ROM','MUC-ROM','TRN-ROM','PAR-FRA','AMS-FRA','MUC-FRA','PRG-FRA','MUC-BER','VIE-PRG','PRG-WAW','BER-WAW','HAM-AMS','BER-HAM','BRU-HAM','PAR-LON','BRU-LON','AMS-LON','VLC-MAD','LYS-PAR','LYS-TRN','LYS-MIL','ROM-BLQ','PAR-TRN','NCE-LYS','NCE-MIL','NCE-TRN','MAD-BCN','VLC-BCN','MAN-LON','SVQ-MAD','DUS-AMS','EDI-LON','VCE-ROM','NAP-BOL','NAP-ROM''VIE-ZAG','VIE-STR','VIE-WAW','VIE-BEG','VIE-BUD','VIE-PRG','BRU-LIL','BRU-DUS','BRU-FRA','ZAG-MUC','ZAG-VCE','ZAG-BUD','BOD-LYS','BOD-NCE','BOD-TLS','BOD-GVA','LIL-PAR','LIL-AMS','LIL-DUS','LIL-LON','LYS-GVA','LYS-TLS','LYS-ZRH','NCE-GVA','TLS-BCN','HAM-DUS','HAM-HAJ','BER-PRG','BER-FRA','FRA-STR','FRA-ZRH','FRA-DUS','DUS-HAJ','MUC-ZRH','MUC-FRA','MUC-MIL','MUC-VCE','STR-ZRH','STR-DUS','BOL-MIL','BOL-VCE','WAW-BUD','VLC-SVQ','ZAG-VIE','STR-VIE','WAW-VIE','BEG-VIE','BUD-VIE','PRG-VIE','LIL-BRU','DUS-BRU','FRA-BRU','MUC-ZAG','VCE-ZAG','BUD-ZAG','LYS-BOD','NCE-BOD','TLS-BOD','GVA-BOD','PAR-LIL','AMS-LIL','DUS-LIL','LON-LIL','GVA-LYS','TLS-LYS','ZRH-LYS','GVA-NCE','BCN-TLS','DUS-HAM','HAJ-HAM','PRG-BER','FRA-BER','STR-FRA','ZRH-FRA','DUS-FRA','HAJ-DUS','ZRH-MUC','FRA-MUC','MIL-MUC','VCE-MUC','ZRH-STR','DUS-STR','MIL-BOL','VCE-BOL','BUD-WAW','SVQ-VLC']
list_railways = ['MIL-ROM']
departure_date = '2021-10-06'
len(list_railways)
# elenco dei viaggi diretti o con 1 cambio
for railway in tqdm(list_railways):
  wd.get(f"https://www.kayak.it/flights/{railway}/{departure_date}")
  time.sleep(5) #inserisco una pausa di 5 secondi dopo essere andato sul sito, per aspettare il caricamento

  if (len(wd.find_elements_by_css_selector("button.Common-Widgets-Button-StyleJamButton.ui-button.size-m._id7._ihr._ihs._h-Y._im4._ii0._ihp._ihq._inw._iir._iae._iam._ifT._ifS._jV7._iiN._3-._4M._40._k-A._isy._jUK._30._43")) > 0):#se ci sono i cookie da accettare, accettali
      print("Click cookie law button")
      wd.find_elements_by_css_selector("button.Common-Widgets-Button-StyleJamButton.ui-button.size-m._id7._ihr._ihs._h-Y._im4._ii0._ihp._ihq._inw._iir._iae._iam._ifT._ifS._jV7._iiN._3-._4M._40._k-A._isy._jUK._30._43")[0].click()
  if (len(wd.find_elements_by_id("onetrust-accept-btn-handler")) > 0):#se ci sono i cookie da accettare, accettali
        print("Click cookie law button")
        wd.find_elements_by_id("onetrust-accept-btn-handler")[0].click()
  wd.save_screenshot("check_browser.png") #verifico che i cookies sono accettati


  for i in range(0,2):
    try:
      WebDriverWait(wd, 10).until(EC.element_to_be_clickable((By.CSS_SELECTOR, ('a[class*="moreButton"]')))).click()
    except WebDriverException:
      print("Element is not clickable")
  list_train = wd.find_elements_by_css_selector('div[class*="resultInner"]')
  for train in tqdm(list_train):
    train_details.append(parse_train(train,railway))
    train_details_raw.append(train)
    break
  print("Click next page")
  time.sleep(5)
  
  list_train = wd.find_elements_by_css_selector('div[class*="resultInner"]')
  for train in tqdm(list_train):
    train_details.append(parse_train(train,railway))
    train_details_raw.append(train)
print(len(train_details))
import pandas as pd
ds_train_details1= pd.DataFrame(train_details)
ds_train_details_raw= pd.DataFrame(train_details_raw)
ds_train_details1.head()
ds_train_details1.to_csv("ds_flight_details_sc1.csv", index=False)
#ds_train_details_raw.to_csv("ds_flight_details_raw_sc.csv", index=False)<jupyter_output><empty_output><jupyter_text>ETL<jupyter_code>import pandas as pd
train_details = pd.read_csv('ds_flight_details_sc1.csv')
train_details.tail()
train_details['arrival time'] = train_details['arrival time'].astype('string')
train_details['arrival time'] = train_details['arrival time'].str.rstrip(' +1').astype('string')
train_details['departure time'] = train_details['departure time'].astype('string')
train_details['departure time'] = train_details['departure time'].str.rstrip(' +1').astype('string')
train_details['date'] = train_details['date'].astype('string')
train_details['date'] = train_details['date'].str.rstrip(' +1').astype('string')
train_details.dtypes
train_details.tail()
# train_details['departure time'] = pd.to_datetime(train_details['departure time'], errors = 'coerce',)
# train_details['arrival time'] = pd.to_datetime(train_details['arrival time'].astype('string'), errors = 'coerce')
# train_details.tail()
datetime_departure = train_details["date"] + " " + train_details["departure time"]
datetime_arrival = train_details["date"] + " " + train_details["arrival time"]
train_details['departure time'] = datetime_departure
train_details['arrival time'] = datetime_arrival
train_details['departure time'] = pd.to_datetime(train_details['departure time'], errors="coerce")
train_details['arrival time'] = pd.to_datetime(train_details['arrival time'], errors="coerce")
train_details.head()
train_details.dtypes
train_details['duration'] = train_details['arrival time'] - train_details['departure time']
train_details['departure time'] = pd.to_datetime(train_details['departure time'], errors = 'coerce').dt.time
train_details['arrival time'] = pd.to_datetime(train_details['arrival time'].astype('string'), errors = 'coerce').dt.time

train_details['duration'] = train_details['duration'].astype('string')
train_details['duration'] = train_details['duration'].str.split('s').str[-1].str.strip()
train_details['duration'] = train_details['duration'].str.split('\+').str[-1].str.strip()
train_details.tail()
train_details["price"].replace('', np.nan, inplace=True)
train_details = train_details.dropna(axis=0, how="any", subset=["price"])
train_details.rename(columns={'railway': 'airports', "departure station" : "departure airport", "arrival station" : "arrival airport", "type of train" : "airline"}, inplace=True)
# Get names of indexes for which column change has value cambi
index1 = train_details[(train_details['change'] == "1 cambio")].index
index2 = train_details[(train_details['change'] == "0 cambi")].index
index3 = train_details[(train_details['airline'].str.contains("FlixBus"))].index
# Delete these row indexes from dataFrame
train_details.drop(index=index1 , inplace=True)
train_details.drop(index=index2 , inplace=True)
train_details.drop(index=index3 , inplace=True)
train_details.to_csv("ds_flight_details_sc.csv")
train_details.to_excel("ds_flight_details_sc.xlsx")
x = pd.read_excel("/content/flights details.xlsx")
x.to_csv("flight details.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19033387413542824, 'fraction_numerical': 0.010417556143796431, 'mean_word_length': 5.996415770609319, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 65, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '611500', 'license': 'MIT', 'n_tokens_mistral': 4562, 'n_tokens_neox': 4426, 'n_words': 570, 'path': 'Code/flights_from_kayak_scraping.ipynb', 'repo_name': 'davidesalva/Stop-flight-take-the-train', 'size': '11733'}