<jupyter_start><jupyter_text><jupyter_code># Import dependencies
import requests
from bs4 import BeautifulSoup
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import re
# Store dataframes for further processing
from sqlalchemy import create_engine

# Upload the database from GitHub
url_db = 'https://github.com/lustraka/data-analyst-portfolio-project-2022/blob/main/data/dapp2022.db?raw=true'
r = requests.get(url_db)
with open('dapp2022.db', 'wb') as file:
  file.write(r.content)

# Create SQLAlchemy engine and connect to the database
engine = create_engine('sqlite:///dapp2022.db')

# Read dataframes from SQlite database
wp_root = pd.read_sql('SELECT * FROM wp_root', engine)
wp_root.info()
wp_root.status.value_counts()<jupyter_output><empty_output><jupyter_text>**HTTP Status Codes**Number | Meaning-|-200 | OK403 | Forbidden404 | Not Found406 | Not Acceptable418 | I'm a teapot (RFC 2324, RFC 7168)429 | Too Many Requests (RFC 6585)500 | Internal Server Error503 | Service Unavailable<jupyter_code>wp_root.query('status == 200 and text_len == 0')['wp_url'].to_numpy()
wp_root.query('text_len > 0').shape[0]<jupyter_output><empty_output><jupyter_text>**Conclusion** (2021-11-12) : Out of 120 web pages, 103 could be successfully loaded by the `requests.get()` function. Two URLs pointed to PDF files and couldn't be parsed with a html parser. So we ended up with 101 web pages for automated text processing. All pages are accessible via a browser, though.**Caveat**: The results can differ when run again another time!<jupyter_code># Check selected records
wp_root.query('status == 404')
def get_site(url):
  """Return site from URL address."""
  return re.search('://(.*?)/', url).group(1)
# Report results in markdown
# for _, row in wp_root.iterrows():
#   print(f'- [{row[1]}] [{row[2]}]({row[3]}) ({row[4]} | {row[7]})')
# Prepare a markdown table for manual data collection
# print('| id | title | site | wp_cat | wp_published | wp_updated | authors | notes |')
# print('| - | - | - | - | - | - | - | - |')
# for _, row in wp_root.iterrows():
#   print(f'| {row[1]} | {row[2][:20]}... | {get_site(row[3])} | - | - | - | - | - |')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1478809738503156, 'fraction_numerical': 0.0405770964833183, 'mean_word_length': 5.029891304347826, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 14, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '522732', 'license': 'CC0-1.0', 'n_tokens_mistral': 819, 'n_tokens_neox': 751, 'n_words': 218, 'path': 'Wrangle_Data/Scrape_Google_Search/20211112_Analyze_Initial_WebPages.ipynb', 'repo_name': 'lustraka/Data_Analysis_Workouts', 'size': '2218'}