import pandas as pd
from bs4 import BeautifulSoup
import requests

# Create an empty DataFrame
df = pd.DataFrame(columns=['employer', 'job_title', 'salary', 'location', 'submit_date', 'start_date'])


# Setup of Source Data https://h1bdata.info/index.php?year={year}&job={job}
base_url = "https://h1bdata.info/index.php?"
job_titles = ["data+scientist", "senior+data+scientist", "data+analyst", "Big+Data", "machine+learning+engineer", "business+analyst", "Database+Engineer", "Quality+Assurance", "analyst", "qa+analyst", "quality+engineer", "data+engineer", "qa+engineer", "data+warehouse+specialist", "senior+analyst", "quality+analyst", "associate+data+scientist"]

for job in job_titles:
    for year in range(2018,2023):
        url = f"{base_url}&year={year}&job={job}"
        response = requests.get(url)
        content = response.content
        soup = BeautifulSoup(content, 'html.parser')
        # Find the table with class 'tablesorter tablesorter-blue hasStickyHeaders'
        table = soup.find('table', {'class': 'tablesorter tablesorter-blue hasStickyHeaders'})
        tbody = table.find('tbody')
        if tbody:
            # Find all rows in the table
            rows = tbody.find_all('tr')
            # Loop through each row
            for row in rows:    
                # Find all cells in the row
                cells = row.find_all(['td'])
                if len(cells) >= 6:
                    # Extract the text from each cell
                    employer = cells[0].text
                    job_title = cells[1].text
                    salary = cells[2].text
                    location = cells[3].text
                    submit_date = cells[4].text
                    start_date = cells[5].text
                else:
                    employer = 'NA'
                    job_title = 'NA'
                    salary = 'NA'
                    location = 'NA'
                    submit_date = 'NA'
                    start_date = 'NA'
                # Append the data to the DataFrame
                df_new_row = pd.DataFrame({'employer': [employer], 'job_title': [job_title], 'salary': [salary], 'location': [location], 'submit_date': [submit_date], 'start_date': [start_date]})
                df = pd.concat([df, df_new_row])

df.head(3)

# Save file
from openpyxl import Workbook
df.to_excel("h1b_salaries.xlsx", index=False)