<jupyter_start><jupyter_text>Import excel file from links provided on project instructions<jupyter_code># import the contents of CSPP_PEPP_corporate_bond_holdings_20210430 into a dataframe
corporate_bond_holdings_df = pd.read_excel (r'/Users/gabbyvinco/Desktop/CSPP_PEPP_corporate_bond_holdings_20210430.xlsx')
corporate_bond_holdings_df.head(10)
# import cbonds list
cbonds_path = r'/Users/gabbyvinco/Desktop/ALL_HISTORICAL.csv'
cbonds = pd.read_csv(cbonds_path, index_col=None, header=0)
cbonds = cbonds.dropna()
cbonds["ISIN_Code"] = cbonds["ISIN_Code"].astype(str)
cbonds
na_check2 = cbonds.isna().any()
na_check2
# make list of ISIN numbers
isin_list = cbonds["ISIN_Code"].tolist()

# isin_list = corporate_bond_holdings_df['ISIN'].tolist()
# isin_list
cbonds_quarter = cbonds["quarter"].tolist()
cbonds_year = cbonds["year"].tolist()
# isin_list
test_list = isin_list[1000:1010]
# test_list
# import missing isin list
path = r'/Users/gabbyvinco/Desktop/missing_isin_codes.csv'
missing_isin = pd.read_csv(path, index_col=None, header=0)
take_2 = missing_isin["ISIN_Code"].tolist()
take_2<jupyter_output><empty_output><jupyter_text>Use cbonds.com to get info about each of the bonds by ISIN code<jupyter_code># Install and initiate driver
driver = webdriver.Chrome(ChromeDriverManager().install())
# URL to test the individual sneaker page functions

cbonds_url = 'https://cbonds.com'
my_url = cbonds_url

# open and maximize the browser window
driver.get(my_url)
# driver.maximize_window()
# initiate the action object
action = webdriver.ActionChains(driver)
# create a wait with a timeout of 30 seconds
wait = WebDriverWait(driver, 30)
# create lists to append items from the function into 
bond_titles = []
bond_types = []
bond_amounts = []
issue_info_list = []
def scrape_correct_names(isin_list):
    for isin in isin_list:
        
        search_bar = wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="mainSearch"]')))
        
        # click search bar
        search_bar.click()
        
        # random sleep time between 1 and 3 seconds
        sleep(randint(1,3))
        
        # select all text in the search bar and delete
        search_bar.send_keys(Keys.COMMAND, 'a')
        search_bar.send_keys(Keys.BACKSPACE)
        
        # random sleep time between 1 and 3 seconds
        sleep(randint(1,3))
        
        # type in isin number
        search_bar.send_keys(isin)
        
        try:
            
            # click the selection
            pop_down = wait.until(EC.element_to_be_clickable((By.XPATH,'/html/body/div[2]/div[1]/div[1]/div[4]/div/ul[2]/li/a')))
            pop_down.click()                                                 
        
            # grab info from isin entry pop down
            title = wait.until(EC.presence_of_element_located((By.XPATH,'/html/body/div[2]/div[4]/div[2]/div[1]/h1')))
            bond_info = driver.find_element_by_xpath('/html/body/div[2]/div[4]/div[2]/div[1]/p')
            amount = driver.find_element_by_class_name('other_param')
            bond_title = title.text  
            bond_type = bond_info.text
            bond_amount = amount.text
            bond_titles.append(bond_title)
            bond_types.append(bond_type)
            bond_amounts.append(bond_amount)
    #         print(bond_title)
    #         print(bond_type)
    #         print(bond_amount)
          
            # random sleep time between 2 and 4 seconds
            sleep(randint(2,4))
            # issuer info
            issue_info = driver.find_element_by_id('bondInfo')
            # move to section
            driver.execute_script("arguments[0].scrollIntoView();", issue_info)
            bottom_container = issue_info.text
            issue_info_list.append(bottom_container)
    #         print(bottom_container)
            print("all info grabbed for this bond")
            # sleep 3 seconds
            time.sleep(3)
            # move back up to search bar
            back_to_search_bar = driver.find_element_by_id('mainSearch')
            back_to_search_bar.location_once_scrolled_into_view
        
        except TimeoutException:
            not_found = "isin_code_not_found"
            bond_titles.append(not_found)
            bond_types.append(not_found)
            bond_amounts.append(not_found)
            issue_info_list.append(not_found)
            continue
        sleep(randint(2,4))

    print('done')<jupyter_output><empty_output><jupyter_text>Re-running for the missing items<jupyter_code>scrape_correct_names(take_2)
missing_list = [1004, 1095, 1122, 1124, 1301, 1604, 1809, 1910, 1911, 1940, 1941]
# creating variables for the missing items 
isin_code = cbonds.iloc[missing_list, 2].tolist()
quarter = cbonds.iloc[missing_list, 0].tolist()
year = cbonds.iloc[missing_list, 1].tolist()
print(isin_code)
print(quarter)
# putting the missing items into its own df
missing_from_main = pd.DataFrame(bond_titles)
missing_from_main['bond_types'] = bond_types
missing_from_main['bond_amounts'] = bond_amounts
missing_from_main['issue_info'] = issue_info_list
missing_from_main['ISIN_Code'] = isin_code
missing_from_main['quarter'] = quarter
missing_from_main['year'] = year
# missing_from_main
# keep only missing items that we were able to get information for
missing_from_main = missing_from_main[missing_from_main.bond_types != "isin_code_not_found"]
missing_from_main
# save the unique_improved to csv file
# missing_from_main.to_csv (r'/Users/gabbyvinco/Desktop/missingfrommain.csv', index = False, header=True)
# update lists so they can be added to the dataframe later
isin_code = missing_from_main["ISIN_Code"].tolist()
quarter = missing_from_main["quarter"].to_list()
year = missing_from_main["year"].tolist()
# exit from the driver
driver.quit()
# same things but for missing df

b_types_2 = missing_from_main["bond_types"]
b_types_2 = b_types_2.tolist()
print(len(b_types_2))

# change bond_amounts column to list
extra_info_2 = missing_from_main["bond_amounts"]
extra_info_2 = extra_info_2.tolist()

# change the first column to list
basic_info_2 = missing_from_main[0]
basic_info_2 = basic_info_2.tolist()

# change issue_info to list
issue_info_2 = missing_from_main['issue_info']
issue_info_2 = issue_info_2.tolist()

# create new lists to split items into from extra_info
amount_list_2 = []
status_list_2 = []
country_of_risk_list_2 = []
# split up the items of the bond_amounts column
for item in extra_info_2:
    item = item.split("\n")
#     print(len(item))
    amount = item[3]
    status = item[1]
    country_of_risk = item[9]
    amount_list_2.append(amount)
    status_list_2.append(status)
    country_of_risk_list_2.append(country_of_risk)
# create lists to split items into from basic_info
bond_location_list_2 = []
name_list_2 = []
percentage_list_2 = []
date_list_2 = []
currency_list_2 = []
isin_code_list_2 = []
_2# split up the data from the first column into its individual variables
for item in basic_info_2:
    item = item.split(",")
    bondNname = item[0]
    percNdate = item[1]
    if len(item) == 3:
        ctryNcode = item[2]
        ctryNcode = ctryNcode.split("\n")
        currency = ctryNcode[0]
    #     print(currency)
        isin_code = ctryNcode[1]
    else:
        isin_code = "none"
        currency = "none"
    currency_list_2.append(currency)
    isin_code_list_2.append(isin_code)
        
    bondNname = bondNname.split(": ")
    bond_location = bondNname[0]
    name = bondNname[1]
    bond_location_list_2.append(bond_location)
    name_list_2.append(name)
    
    percNdate = percNdate.split(" ")
    if len(percNdate) == 2:
        sub_item = percNdate[1]
        sub_item = sub_item.split("%")
        date = sub_item[1]
        percentage = sub_item[0]+"%"
        percentage_list_2.append(percentage)
        date_list_2.append(date)
    else:
        percentage = percNdate[1]
        date = percNdate[2]
        percentage_list_2.append(percentage)
        date_list_2.append(date)
    
    
    
# create lists to split items into from issue_info
comp_text_2 = []
sector_2 = []
industry_2 = []
nominal_2 = []
stocks_2 = []
relationship_status_2 = []
_2for item in issue_info_2:
    sector_split = item.split("Sector\n")
    sector_info = sector_split[1]
    sector_info = sector_info.split("\n")
    sector_info = sector_info[0]
    
    industry_split = item.split("Industry\n")
    industry_info = industry_split[1]
    industry_info = industry_info.split("\n")
    industry_info = industry_info[0]
    
    nominal_split = item.split("Nominal\n")
    nominal_info = nominal_split[2]
    nominal_info = nominal_info.split("\n")
    nominal_info = nominal_info[0]
#     print(nominal_info)
    
    stocks_split = item.split("\n*** EUR")
    stock_info = stocks_split[1]
    stock_info = stock_info.replace("\n", "")
    
    relationship_split = item.split("Show more")
    relationship = relationship_split[0]
    relationship = relationship.split("\n")
    relationship = relationship[1]
    
    
    normal_split = item.split("\n")
    text_blurb = normal_split[2]
    
    comp_text_2.append(text_blurb)
    sector_2.append(sector_info)
    industry_2.append(industry_info)
    nominal_2.append(nominal_info)
    stocks_2.append(stock_info)
    relationship_status_2.append(relationship)
missing_bond_info = pd.DataFrame(name_list_2, columns=["company_name"])
missing_bond_info["company_description"] = comp_text_2
missing_bond_info["relationship_to_bond"] = relationship_status_2
missing_bond_info["sector"] = sector_2
missing_bond_info["industry"] = industry_2
missing_bond_info["ISIN"] = isin_code
missing_bond_info["bond_destination"] = bond_location_2
missing_bond_info["coupon_rate"] = percentage_list_2
missing_bond_info["total_bond_amount"] = amount_list_2
missing_bond_info["nominal_amount"] = nominal_2
missing_bond_info["quarter_created"] = quarter
missing_bond_info["year_created"] = year
missing_bond_info["maturity_date"] = date_list_2
missing_bond_info["bond_currency"] = currency_list_2
missing_bond_info["country"] = country_of_risk_list_2
missing_bond_info["status"] = status_list_2
missing_bond_info["stocks_info"] = stocks_2
missing_bond_info["bond_types"] = b_types_2
# takes the different types of bond_types and provides a yes/no in seperate columns
missing_bond_type_df = missing_bond_info['bond_types'].str.get_dummies(', ').replace({0:'no', 1:'yes'})
missing_bonds_df = pd.concat([missing_bond_info, missing_bond_type_df], axis=1)
missing_bonds_df<jupyter_output><empty_output><jupyter_text>First run for the main dataset<jupyter_code>len(bond_titles)
# set to string so we dont get the .0 after year
cbonds["year"] = cbonds["year"].astype(str)
# add everything to a dataframe so I dont accidentally lose it
cbonds_info_df = pd.DataFrame(bond_titles)
cbonds_info_df['bond_types'] = bond_types
cbonds_info_df['bond_amounts'] = bond_amounts
cbonds_info_df['issue_info'] = issue_info_list
cbonds_info_df['ISIN_Code'] = cbonds["ISIN_Code"]
cbonds_info_df['quarter'] = cbonds["quarter"]
cbonds_info_df['year'] = cbonds["year"]
cbonds_info_df
# save the unique_improved to csv file
# cbonds_info_df.to_csv (r'/Users/gabbyvinco/Desktop/may15cbonds.csv', index = False, header=True)
# import cbonds_info_df list
cbds_info_path = r'/Users/gabbyvinco/Desktop/may15cbonds.csv'
cbonds_info_df = pd.read_csv(cbds_info_path, index_col=None, header=0)
# cbonds_info_df
cbonds_info_df["Quarter"] = cbonds_quarter
cbonds_info_df["Year"] = cbonds_year
# create a df of items that isin_code was not found on cbonds
isin_codes_not_found = cbonds_info_df.loc[cbonds_info_df['bond_types'] == "isin_code_not_found"]
isin_codes_not_found
# save the unique_improved to csv file
# isin_codes_not_found.to_csv (r'/Users/gabbyvinco/Desktop/missing_isin_codes.csv', index = False, header=True)
cbonds_info_df = cbonds_info_df[cbonds_info_df.bond_types != "isin_code_not_found"]
cbonds_info_df
cbonds_info_df = cbonds_info_df.drop(columns=['ISIN_Code', 'quarter', 'year'])
na_check1 = cbonds_info_df.isna().any()
na_check1
# cbonds_info_df.loc["0"]
cbonds_info_df = cbonds_info_df.reset_index(drop=True)
# cbonds_info_df
# exit from the driver
driver.quit()
# just to see the different combinations of bond types
cbonds_info_df["bond_types"].value_counts()
# change bond_types column to list
b_types = cbonds_info_df["bond_types"]
b_types = b_types.tolist()

# change bond_amounts column to list
extra_info = cbonds_info_df["bond_amounts"]
extra_info = extra_info.tolist()

# change the first column to list
basic_info = cbonds_info_df["0"]
basic_info = basic_info.tolist()

# change issue_info to list
issue_info = cbonds_info_df['issue_info']
issue_info = issue_info.tolist()

# b_types
# basic_info
# extra_info
# create new lists to split items into from extra_info
amount_list = []
status_list = []
country_of_risk_list = []
# split up the items of the bond_amounts column
for item in extra_info:
    item = item.split("\n")
#     print(len(item))
    amount = item[3]
    status = item[1]
    country_of_risk = item[9]
    amount_list.append(amount)
    status_list.append(status)
    country_of_risk_list.append(country_of_risk)
# create lists to split items into from basic_info
bond_location_list = []
name_list = []
percentage_list = []
date_list = []
currency_list = []
isin_code_list = []
# split up the data from the first column into its individual variables
for item in basic_info:
    item = item.split(",")
    bondNname = item[0]
    percNdate = item[1]
    if len(item) == 3:
        ctryNcode = item[2]
        ctryNcode = ctryNcode.split("\n")
        currency = ctryNcode[0]
    #     print(currency)
        isin_code = ctryNcode[1]
    else:
        isin_code = "none"
        currency = "none"
    currency_list.append(currency)
    isin_code_list.append(isin_code)
        
    bondNname = bondNname.split(": ")
    bond_location = bondNname[0]
    name = bondNname[1]
    bond_location_list.append(bond_location)
    name_list.append(name)
    
    percNdate = percNdate.split(" ")
    if len(percNdate) == 2:
        sub_item = percNdate[1]
        sub_item = sub_item.split("%")
        date = sub_item[1]
        percentage = sub_item[0]+"%"
        percentage_list.append(percentage)
        date_list.append(date)
    else:
        percentage = percNdate[1]
        date = percNdate[2]
        percentage_list.append(percentage)
        date_list.append(date)
    
    
    
# create lists to split items into from issue_info
comp_text = []
sector = []
industry = []
nominal = []
stocks = []
relationship_status = []
for item in issue_info:
    sector_split = item.split("Sector\n")
    sector_info = sector_split[1]
    sector_info = sector_info.split("\n")
    sector_info = sector_info[0]
    
    industry_split = item.split("Industry\n")
    industry_info = industry_split[1]
    industry_info = industry_info.split("\n")
    industry_info = industry_info[0]
    
    nominal_split = item.split("Nominal\n")
    nominal_info = nominal_split[2]
    nominal_info = nominal_info.split("\n")
    nominal_info = nominal_info[0]
#     print(nominal_info)
    
    stocks_split = item.split("\n*** EUR")
    stock_info = stocks_split[1]
    stock_info = stock_info.replace("\n", "")
    
    relationship_split = item.split("Show more")
    relationship = relationship_split[0]
    relationship = relationship.split("\n")
    relationship = relationship[1]
    
    
    normal_split = item.split("\n")
    text_blurb = normal_split[2]
    
    comp_text.append(text_blurb)
    sector.append(sector_info)
    industry.append(industry_info)
    nominal.append(nominal_info)
    stocks.append(stock_info)
    relationship_status.append(relationship)
# create a new dataframe with the new variables that we have created
all_bond_info = pd.DataFrame(name_list, columns=["company_name"])
# add columns to the dataframe with the rest of the variables we created
all_bond_info["company_description"] = comp_text
all_bond_info["relationship_to_bond"] = relationship_status
all_bond_info["sector"] = sector
all_bond_info["industry"] = industry
all_bond_info["ISIN"] = isin_code_list
all_bond_info["bond_destination"] = bond_location
all_bond_info["coupon_rate"] = percentage_list
all_bond_info["total_bond_amount"] = amount_list
all_bond_info["nominal_amount"] = nominal
all_bond_info["quarter_created"] = cbonds_info_df["Quarter"]
all_bond_info["year_created"] = cbonds_info_df["Year"]
all_bond_info["maturity_date"] = date_list
all_bond_info["bond_currency"] = currency_list
all_bond_info["country"] = country_of_risk_list
all_bond_info["status"] = status_list
all_bond_info["stocks_info"] = stocks
all_bond_info["bond_types"] = b_types
# takes the different types of bond_types and provides a yes/no in seperate columns
bond_type_df = all_bond_info['bond_types'].str.get_dummies(', ').replace({0:'no', 1:'yes'})
all_bond_df = pd.concat([all_bond_info, bond_type_df], axis=1)
all_bond_df
na_check = all_bond_df.isna().any()
# na_check<jupyter_output><empty_output><jupyter_text>Combine main df with missing df<jupyter_code>all_bond_df = all_bond_df.append(missing_bonds_df, sort=False)
all_bond_df["Floating rate"] = all_bond_df["Floating rate"].replace(np.nan, 'no', regex=True)
all_bond_df["Foreign bonds"] = all_bond_df["Foreign bonds"].replace(np.nan, 'no', regex=True)
all_bond_df["Green bonds"] = all_bond_df["Green bonds"].replace(np.nan, 'no', regex=True)
all_bond_df["Securitization"] = all_bond_df["Securitization"].replace(np.nan, 'no', regex=True)
all_bond_df["Senior Secured"] = all_bond_df["Senior Secured"].replace(np.nan, 'no', regex=True)
all_bond_df["Senior Unsecured"] = all_bond_df["Senior Unsecured"].replace(np.nan, 'no', regex=True)
all_bond_df["Zero-coupon bonds"] = all_bond_df["Zero-coupon bonds"].replace(np.nan, 'no', regex=True)
all_bond_df = all_bond_df.sort_values(["year_created", "quarter_created"], ascending = (True, True))
pd.set_option('display.max_columns', None)
all_bond_df
# save the unique_improved to csv file
# all_bond_df.to_csv (r'/Users/gabbyvinco/Desktop/HISTORICAL_BONDS.csv', index = False, header=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11027624309392266, 'fraction_numerical': 0.013867403314917127, 'mean_word_length': 4.328525169267, 'pattern_counts': {'":': 2, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '585568', 'license': 'MIT', 'n_tokens_mistral': 6498, 'n_tokens_neox': 6240, 'n_words': 1172, 'path': 'Notebooks/cbonds_scraping.ipynb', 'repo_name': 'gabriellavinco/ECB-Bond-Purchases', 'size': '18100'}