<jupyter_start><jupyter_text>Data Wrangling with Python Getting Comfortable with Different Kinds of Data SourcesIn this section, we will be learning to perform the following using Pandas and NumPy.* Read CSV, Excel, and JSON files into pandas DataFrames* Read PDF documents and HTML tables into pandas DataFrames* Perform basic web scraping using powerful yet easy to use libraries such as Beautiful Soup* Extract structured and textual information from portals Reading Data from Different Text-Based (and Non-Text-Based) SourcesWe will explore how to extract and read data from a diverse array of sources into a structured format. READING DATA FROM A CSV FILE WHERE HEADERS ARE MISSING<jupyter_code>%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from IPython.display import display
from bs4 import BeautifulSoup
# Read CSV file with header using default settings
df1 = pd.read_csv("data/CSV_EX_1.csv")
display(df1)
# Read CSV file without header using default settings
df2 = pd.read_csv("data/CSV_EX_2.csv")
display(df2)

# Read CSV file without header using parameter header=None
df2 = pd.read_csv("data/CSV_EX_2.csv", header=None)
display(df2)

# Read CSV file without header using parameter header=None and names=[Array]
df2 = pd.read_csv("data/CSV_EX_2.csv", header=None, names=[ 'Bedroom	Sq. foot', 'Locality', 'Price ($)' ])
display(df2)<jupyter_output><empty_output><jupyter_text>READING FROM A CSV FILE WHERE DELIMITERS ARE NOT COMMAS<jupyter_code># Read delimited file with delimiter other than a comma using default settings
df3 = pd.read_csv("data/CSV_EX_3.csv")
display(df3)

# Read delimited file with delimiter other than a comma using parameter sep=';'
df3 = pd.read_csv("data/CSV_EX_3.csv", sep=";")
display(df3)<jupyter_output><empty_output><jupyter_text>BYPASSING THE HEADERS OF A CSV FILE<jupyter_code># Read CSV file with header using parameter names=[Array]
df4 = pd.read_csv("data/CSV_EX_1.csv", names=[ 'A', 'B', 'C', 'D' ])
display(df4)

# Read CSV file with header using parameter header=0 and names=[Array]
df4 = pd.read_csv("data/CSV_EX_1.csv", header=0, names=[ 'A', 'B', 'C', 'D' ])
display(df4)<jupyter_output><empty_output><jupyter_text>SKIPPING INITIAL ROWS AND FOOTERS WHEN READING A CSV FILE<jupyter_code># Read CSV file with multiple unecessary lines at the top using default settings
df5 = pd.read_csv("data/CSV_EX_skiprows.csv")
display(df5)

# Read CSV file with multiple unecessary lines at the top using parameter skiprows=2
df5 = pd.read_csv("data/CSV_EX_skiprows.csv", skiprows=2)
display(df5)

# Read CSV file with initial lines and footer using parameters skiprows=2, skipfooter=1, engine="python"
df6 = pd.read_csv("data/CSV_EX_skipfooter.csv", skiprows=2, skipfooter=1, engine="python")
display(df6)<jupyter_output><empty_output><jupyter_text>> Note CSV reader functions pd.read_csv provides 2 engines C or Python for reading the delimited files. And only the Python engine supports the skipfooter option Reading Only the First N Rows (Especially Useful for Large Files)<jupyter_code># Read CSV file using parameter nrows=2
df7 = pd.read_csv("data/CSV_EX_1.csv", nrows=2)
display(df7)<jupyter_output><empty_output><jupyter_text>COMBINING SKIPROWS AND NROWS TO READ DATA IN SMALL CHUNKS<jupyter_code># Read CSV file using parameter skiprows=[start] nrows=[rowcount]
list_of_dataframe = []
rows_in_a_chunk = 10
num_chunks = 5

df_dummy = pd.read_csv("data/boston_house_prices.csv", nrows=2)

colnames = df_dummy.columns
for i in range(0, num_chunks*rows_in_a_chunk,rows_in_a_chunk):
    df = pd.read_csv("data/boston_house_prices.csv", header=0, skiprows=i, nrows=rows_in_a_chunk, names=colnames)
    list_of_dataframe.append(df)

display(list_of_dataframe)
# Read CSV file with empty lines using default settings
df9 = pd.read_csv("data/CSV_EX_blankline.csv")
display(df9)

# Read CSV file with empty lines using parameter skip_blank_lines=False
df9 = pd.read_csv("data/CSV_EX_blankline.csv", skip_blank_lines=False)
display(df9)<jupyter_output><empty_output><jupyter_text>Read CSV From a zip file<jupyter_code>df10 = pd.read_csv("data/CSV_EX_1.zip")
display(df10)<jupyter_output><empty_output><jupyter_text>READING FROM AN EXCEL FILE USING SHEET_NAME AND HANDLING A DISTINCT SHEET_NAME<jupyter_code># read from excel sheet Data_Tab_1
df11_1 = pd.read_excel(
    "data/Housing_data.xlsx", sheet_name='Data_Tab_1'
)
display(df11_1)

# read from excel sheet Data_Tab_2
df11_2 = pd.read_excel(
    "data/Housing_data.xlsx", sheet_name='Data_Tab_2'
)
display(df11_2)

# read from excel sheet Data_Tab_3
df11_3 = pd.read_excel(
    "data/Housing_data.xlsx", sheet_name='Data_Tab_3'
)
display(df11_3)

# read entire excel sheet as dictionary
dict_df = pd.read_excel(
    "data/Housing_data.xlsx", sheet_name=None
)
display(dict_df.keys())<jupyter_output><empty_output><jupyter_text>READING A GENERAL DELIMITED TEXT FILEThis uses read_table that is deprecated and recommended to use read_csv with the separator parameter<jupyter_code># reading a comma-separated file, save with .txt with no separator.
df13 = pd.read_table("data/Table_EX_1.txt")
display(df13)

# reading a comma-separated file, save with .txt with separator
df13 = pd.read_table("data/Table_EX_1.txt", sep=",")
display(df13)<jupyter_output>/Volumes/apfs_256gb/Personal/Technology/conda/data-engineering/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning: read_table is deprecated, use read_csv instead, passing sep='\t'.<jupyter_text>READING HTML TABLES DIRECTLY FROM A URL<jupyter_code>url = 'http://www.fdic.gov/bank/individual/failed/banklist.html'

list_of_df = pd.read_html(url)

df14 = list_of_df[0]

df14.head()<jupyter_output><empty_output><jupyter_text>FURTHER WRANGLING TO GET THE DESIRED DATA<jupyter_code># reading a list of dataframe from an html page
list_of_df = pd.read_html("https://en.wikipedia.org/wiki/2016_Summer_Olympics_medal_table", header=0)
len(list_of_df)

for t in list_of_df:
    print(t.shape)
    
df15 = list_of_df[1]
df15.head()<jupyter_output>(1, 1)
(87, 6)
(10, 9)
(0, 2)
(1, 2)
(4, 2)<jupyter_text>READING FROM A JSON FILE<jupyter_code>df16 = pd.read_json("data/movies.json")
df16.head()

cast_of_avengers = df16[(df16["title"] == "The Avengers") & (df16['year'] == 2012)]['cast']
print(list(cast_of_avengers))<jupyter_output>[['<NAME>, Jr.', '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>']]<jupyter_text>Reading a Stata File<jupyter_code>df17 = pd.read_stata("data/wu-data.dta")
display(df17.head())<jupyter_output><empty_output><jupyter_text>READING TABULAR DATA FROM A PDF FILE<jupyter_code>from tabula import read_pdf

df18_1 = read_pdf("data/Housing_data.pdf", pages=[1], pandas_options={'header':None})
display(df18_1)

df18_2 = read_pdf("data/Housing_data.pdf", pages=[2], pandas_options={'header':None})
display(df18_2)

df_18 = pd.concat([ df18_1, df18_2 ])
display(df_18)

# Adding headers to the dataframe
names = [
    'CRIM', 'ZN', 'INDUS', 'CHAS', 'NOX', 'RM', 'AGE',
    'DIS', 'RAD', 'TAX', 'PTRATIO', 'B', 'LSTAT', 'PRICE'
]

df18_1 = read_pdf(
    'data/Housing_data.pdf', 
    pages=[1], 
    pandas_options={
        "header": None,
        "names": names[:10]
    }
)

df18_2 = read_pdf(
    'data/Housing_data.pdf', 
    pages=[2], 
    pandas_options={
        "header": None,
        "names": names[:10]
    }
)

df18 = pd.concat([ df18_1, df18_2 ], axis=1)
display(df18)<jupyter_output><empty_output><jupyter_text>Introduction to Beautiful Soup 4 and Web Page Parsing READING AN HTML FILE AND EXTRACTING ITS CONTENTS USING BEAUTIFULSOUP<jupyter_code>with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    print(type(soup))
    print(soup.prettify())
# access single html tag
with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    print(soup.p)
# access all html tags of same kind
with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    all_p = soup.find_all('p')
    print(f"Total number of <p> --- {len(all_p)}")
# access a particular html 
with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    table = soup.table
    print(table.contents)
# access the children of the html tag
with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    table = soup.table
    children = table.children
    des = table.descendants
    print(len(list(children)), len(list(des)))<jupyter_output>9 61<jupyter_text>Dataframes and BeautifulsoupRandomly choose a controlled fraction of the DataFrame<jupyter_code>with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    data = soup.findAll('tr')
    print(f"Data is a {type(data)} and {len(data)} items")
    data_without_header = data[1:]
    headers = data[0]
    print(headers)
    
    col_headers = [
        th.getText() for th in headers.findAll('th')
    ]
    print(col_headers)
    
    df_data = [
        [td.getText() for td in tr.findAll('td')] for tr in data_without_header
    ]
    print(df_data)
    
    df = pd.DataFrame(df_data, columns=col_headers)
    display(df.head())<jupyter_output>Data is a <class 'bs4.element.ResultSet'> and 4 items
<tr>
<th>Entry Header 1</th>
<th>Entry Header 2</th>
<th>Entry Header 3</th>
<th>Entry Header 4</th>
</tr>
['Entry Header 1', 'Entry Header 2', 'Entry Header 3', 'Entry Header 4']
[['Entry First Line 1', 'Entry First Line 2', 'Entry First Line 3', 'Entry First Line 4'], ['Entry Line 1', 'Entry Line 2', 'Entry Line 3', 'Entry Line 4'], ['Entry Last Line 1', 'Entry Last Line 2', 'Entry Last Line 3', 'Entry Last Line 4']]<jupyter_text>Exporting a Dataframe as an Excel file<jupyter_code>with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    data = soup.findAll('tr')
    data_without_header = data[1:]
    headers = data[0]
    col_headers = [
        th.getText() for th in headers.findAll('th')
    ]
    df_data = [
        [td.getText() for td in tr.findAll('td')] for tr in data_without_header
    ]
    df = pd.DataFrame(df_data, columns=col_headers)
    writer = pd.ExcelWriter('test_output.xlsx')
    df.to_excel(writer, "Sheet1")
    writer.save()
    print(writer)<jupyter_output><pandas.io.excel._OpenpyxlWriter object at 0x1256c7f60><jupyter_text>STACKING URLS FROM A DOCUMENT USING BS4<jupyter_code>stack = []
with open("data/test.html", "r") as fd:
    soup = BeautifulSoup(fd)
    lis = soup.find('ul').findAll('li')
    for li in lis:
        a = li.find('a', href=True)
        stack.append(a['href'])
    
print(stack)<jupyter_output>['https://en.wikipedia.org/wiki/Entropy_(information_theory)', 'http://www.gutenberg.org/browse/scores/top', 'https://www.imdb.com/chart/top']<jupyter_text>Reading Tabular Data from a Web Page and Creating DataFrames<jupyter_code>file = "data/List of countries by GDP (nominal) - Wikipedia.htm"
fd = open(file)
soup = BeautifulSoup(fd)
all_tables = soup.findAll('table')
print(f"{file} has {len(all_tables)} tables")

data_table = soup.find("table", { "class": '"wikitable"|}' })
print(type(data_table))

# get headers
sources = data_table.tbody.findAll('tr', recursive=False)[0]
source_list = [ td for td in sources.findAll('td') ]
print(len(source_list))

# get data
data = data_table.tbody.findAll('tr', recursive=False)[1].findAll('td', recursive=False)
data_tables = []
for td in data:
    data_tables.append(td.findAll('table'))
    
len(data_tables)

source_names = [ source.findAll('a')[0].getText() for source in source_list ]
print(source_names)

 

def find_right_text(i, td):
    if i == 0 or i == 1:
        return td.get_text().strip()
    else:
        index = td.text.find("♠")
        return td.text[index + 1:].strip()
    
df_headers = []
df_rows = []

dfs = []

for table in data_tables:
    table_headers = [
        th.getText() for th in table[0].findAll('thead')[0].findAll('th')
    ]
    rows = table[0].findAll('tbody')[0].findAll('tr')[1:]
    table_data = [
        [ find_right_text(i, td) for i, td in enumerate(row.findAll('td'))] for row in rows 
    ]
    df = pd.DataFrame(table_data, columns=table_headers)
    dfs.append(df)
    display(df.head(10))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1325766921621844, 'fraction_numerical': 0.020314170573237932, 'mean_word_length': 5.08304152076038, 'pattern_counts': {'":': 5, '<': 94, '<?xml version=': 0, '>': 95, 'https://': 3, 'lorem ipsum': 0, 'www.': 3, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '364213', 'license': 'MIT', 'n_tokens_mistral': 4578, 'n_tokens_neox': 4375, 'n_words': 1097, 'path': 'Data Wrangling with Python - Chapter 05.ipynb', 'repo_name': 'seetd/data-wrangling-with-python', 'size': '12248'}