<jupyter_start><jupyter_text>![Cloud-First](../image/CloudFirst.png)  SIT742: Big Data Analytics**(Module: Big Data)**---- Materials in this module include resources collected from various open-source online repositories.- You are free to use, change and distribute this package.- If you found any issue/bug for this document, please submit an issue at [tulip-lab/sit742](https://github.com/tulip-lab/sit742/issues)Prepared by **SIT742 Teaching Team**--- Session 3E: Data Acquisition (2)In this session, we will learn how to use Python Packages to ETL the data and files. Content1. `Pandas` Basics2. Loading `CSV` Data3. Data Extraction through Web `API` 4. Web Crawling using `BeautifulSoup`**Note**: The data available on those service might be changing, so you need to adjust the code to accommodate those changes.---  Part 1. `Pandas` Basics`Pandas` is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.To get started, we can import `Pandas` with:<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>`Series` and `DataFrame`Two core components of pandas are the `Series` and `DataFrame`. A `Series` is essentially a column, and a `DataFrame` is a multi-dimensional table made up of a collection of `series`. For a two dimensional DataFrame, the row labels are referred to as `index`, and the column labels are referred to as `columns`.There are many ways to create a dataframe, and one common option is to use a simple dictionary with each entry acting as a column in the dataframe, as shown in below:<jupyter_code># Defining DataFrame by specifing a list of observations
df_1= pd.DataFrame([['a', 'b', 'c'],
                 ['d', 'e', 'f'],
                 ['g', 'h', 'i']],
                index = [1,2,3], columns = ['col1', 'col2', 'col3'])
                
# Defining DataFrame by specifing a dictionary of columns 
df_2=pd.DataFrame({'col1': ['a', 'd', 'g'], 
                 'col2': ['b', 'e', 'h'],
                 'col3': ['c', 'f', 'i']}, 
                 index = [1,2,3])

df_1<jupyter_output><empty_output><jupyter_text>Both `df_1` and `df_2` defined above are identical.The index labels, columns labels, and data values stored in the dataframe `df_1` can be retrieved using `df_1.index`, `df_1.columns` and `df_1.values` respectively.<jupyter_code>df_1.index
df_1.columns
df_1.values<jupyter_output><empty_output><jupyter_text>`Pandas` also provides operations to inspect a dataframe to gain better understanding of its contents.- `.head(n)` and `.tail(n)` returns the top and bottom `n` rows of the dataframe, respectively.- `.describe()` returns summary of all numerical variables (columns) in the dataset.<jupyter_code>df_1.head(2)
df_1.tail(2)
df_1.describe()<jupyter_output><empty_output><jupyter_text>Part 2. Loading `CSV` DataHere you will learn  how to use Pandas [read_csv()](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) function to load a CSV file. Before we start importing our CSV file, it might be good for you to read [Pandas tutorial on reading CSV files](http://pandas.pydata.org/pandas-docs/stable/io.htmlio-read-csv-table).If `wget` was not installed in your `Python` platform, install it first:<jupyter_code>!pip install wget<jupyter_output><empty_output><jupyter_text>Suppose the `csv` data file is avilable at a URL, we use `wget` to download it to the local file system.<jupyter_code>import wget

link_to_data = 'https://github.com/tulip-lab/sit742/raw/master/Jupyter/data/Melbourne_bike_share.csv'

DataSet = wget.download(link_to_data)<jupyter_output><empty_output><jupyter_text>Importing `CSV` dataImporting `CSV` files with `Pandas` function [`read_csv()`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html)  and converting the data into a form Python can understand is simple. It only takes a couple of lines of code. The imported data will be stored in Pandas `DataFrame`.<jupyter_code>import pandas as pd
csvdf = pd.read_csv("Melbourne_bike_share.csv")
type(csvdf)<jupyter_output><empty_output><jupyter_text>Inspecting the DataNow, the data should be loaded into `Python`.  Let's have a look at the first 5 records in the dataset. There are a coupe of ways to retrieve these records.For example, you can use * `csvdf.head(n = 5)`: It will return first `n` rows in a DataFrame, `n = 5` by default.* `csvdf[:5]`: It uses the slicing method to retrieve the first `5` rows.Refer to "[Indexing and Selecting Data](http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html)"for how to slice, dice, and generally get and set subsets of pandas objects.Here, we use the `head` function.<jupyter_code># have an oveall inspection
csvdf.describe()

#We can use the head instead of the For Statement shown in the above example.
csvdf.head()
csvdf[:5]
csvdf.loc[:4] 

#tail is used to show last several records.
csvdf.tail()<jupyter_output><empty_output><jupyter_text>Currently, the row indices are integers automatically generated by `Pandas`.Suppose you want to set IDs as row indices and delete the ID column.Resetting the row indices can be easily done with the following DataFrame function：>DataFrame.set_index(keys, drop=True, append=False, inplace=False, verify_integrity=False) See its [API webpage](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html) for the detailed usage.The keys are going to be the IDs in the first column. By setting `inplace = True`, the corresponding change is done inplace and won't return a new DataFrame object.<jupyter_code>#To show how many records in the file
len(csvdf.ID.unique())

csvdf.set_index(csvdf.ID, inplace = True)
csvdf.head()<jupyter_output><empty_output><jupyter_text>To remove the ID column that is now redundant, you use DataFrame `drop` function and set `inplace = True`>DataFrame.drop(labels, axis=0, level=None, inplace=False, errors='raise')<jupyter_code>csvdf.drop('ID', 1, inplace = True)
csvdf.head()<jupyter_output><empty_output><jupyter_text>Part 3. Data Extraction through Web `API` Many of you will probably be interested in scraping data from the web for your projects. For example, what if we were interested in working with some historical Canadian weather data? Well, we can get that from: http://climate.weather.gc.ca using their API. Requests are going to be formatted like this:<jupyter_code>import pandas as pd

url_template = "http://climate.weather.gc.ca/climate_data/bulk_data_e.html?format=csv&stationID=5415&Year={year}&Month={month}&timeframe=1&submit=Download+Data"<jupyter_output><empty_output><jupyter_text>Note that we've requested the data be returned as a CSV, and that we're going to supply the month and year as inputs when we fire off the query. To get the data for March 2012, we need to format it with month=3, year=2012:<jupyter_code>url = url_template.format(month=3, year=2012)
url<jupyter_output><empty_output><jupyter_text>This is great! We can just use the same `read_csv` function as before, and just give it a URL as a filename. Awesome.Upon inspection, we find out that there are 0 rows (as in 03/2020) of metadata at the top of this CSV, but pandas knows CSVs are weird, so there's a `skiprows` options. We parse the dates again, and set 'Date/Time' to be the index column. Here's the resulting dataframe.<jupyter_code>weather_mar2012 = pd.read_csv(url, skiprows=0, index_col='Date/Time (LST)', parse_dates=True, encoding='latin1')
weather_mar2012.head()<jupyter_output><empty_output><jupyter_text>As before, we can get rid of any columns that don't contain real data using ${\tt .dropna()}$<jupyter_code>weather_mar2012 = weather_mar2012.dropna(axis=1, how='any')
weather_mar2012.head()<jupyter_output><empty_output><jupyter_text>Getting better! The Year/Month/Day/Time columns are redundant, though, and the Data Quality column doesn't look too useful. Let's get rid of those.<jupyter_code>weather_mar2012 = weather_mar2012.drop(['Year', 'Month', 'Day', 'Time (LST)'], axis=1)
weather_mar2012[:5]<jupyter_output><empty_output><jupyter_text>Great! Now let's figure out how to download the whole year? It would be nice if we could just send that as a single request, but like many APIs this one is limited to prevent people from hogging bandwidth. No problem: we can write a function!<jupyter_code>def download_weather_month(year, month):
    url = url_template.format(year=year, month=month)
    weather_data = pd.read_csv(url, skiprows=0, index_col='Date/Time (LST)', parse_dates=True)
    weather_data = weather_data.dropna(axis=1)
    weather_data.columns = [col.replace('\xb0', '') for col in weather_data.columns]
    weather_data = weather_data.drop(['Year', 'Day', 'Month', 'Time (LST)'], axis=1)
    return weather_data<jupyter_output><empty_output><jupyter_text>Now to test that this function does the right thing:<jupyter_code>download_weather_month(2020, 1).head()<jupyter_output><empty_output><jupyter_text>Woohoo! Now we can iteratively request all the months using a single line. This will take a little while to run.<jupyter_code>data_by_month = [download_weather_month(2012, i) for i in range(1, 12)]<jupyter_output><empty_output><jupyter_text>Once that's done, it's easy to concatenate all the dataframes together into one big dataframe using ${\tt pandas.concat()}$. And now we have the whole year's data!<jupyter_code>weather_2012 = pd.concat(data_by_month)<jupyter_output><empty_output><jupyter_text>This thing is long, so instead of printing out the whole thing, I'm just going to print a quick summary of the ${\tt DataFrame}$ by calling ${\tt .info()}$:<jupyter_code>weather_2012.info()<jupyter_output><empty_output><jupyter_text>And a quick reminder, if we wanted to save that data to a file:<jupyter_code>weather_2012.to_csv('weather_2012.csv')
!ls<jupyter_output><empty_output><jupyter_text>And finally, something you should do early on in the wrangling process, plot data:<jupyter_code># plot that data
import matplotlib.pyplot as plt 
# so now 'plt' means matplotlib.pyplot
dateRange = weather_2012.index
temperature = weather_2012['Temp (C)']

df1 = pd.DataFrame({'Temperature' : temperature}, index=dateRange)
      
plt.plot(df1.index.to_pydatetime(), df1.Temperature)
plt.title("The 2012 annual temperature in Canada")
plt.xlabel("Month")
plt.ylabel("Temperature")
# nothing to see... in iPython you need to specify where the chart will display, usually it's in a new window
# to see them 'inline' use:
%matplotlib inline
#If you add the %matplotlib inline, then you can skip the plt.show() function.
#How to close python warnings
import warnings
warnings.filterwarnings('ignore')
# that's better, try other plots, scatter is popular, also boxplott
df1 = pd.read_csv('weather_2012.csv', low_memory=False)
df1.plot(kind='scatter',x='Dew Point Temp (C)',y='Rel Hum (%)',color='red')
df1.plot(kind='scatter',x='Temp (C)',y='Wind Spd (km/h)',color='yellow')
# show first several 'weather' columns value
weather_2012['Weather'].head()

#Boxplot sample
climategroup1 = df1[df1['Weather']=='Fog']['Temp (C)']
climategroup2 = df1[df1['Weather']=='Rain']['Temp (C)']
climategroup3 = df1[df1['Weather']=='Clear']['Temp (C)']
climategroup4 = df1[df1['Weather']=='Cloudy']['Temp (C)']

data =[climategroup1,climategroup2,climategroup3,climategroup4]


fig1, ax1 = plt.subplots()
ax1.set_title('Temperature Boxplot based on the Climate group')
ax1.set_ylabel('Temperature')
ax1.set_xlabel('Climate Group')

boxplot=ax1.boxplot(data, 
                    notch=True,
                    patch_artist=True,
                    labels=['Fog','Rain','Clear','Cloudy'],
                    boxprops=dict(linestyle='--', linewidth=2, color='black'))

colors = ['cyan', 'pink', 'lightgreen', 'tan', 'pink']
for patch, color in zip(boxplot['boxes'], colors):
    patch.set_facecolor(color)

plt.show()<jupyter_output><empty_output><jupyter_text>Part 4. States and Territories of Australia We are interested in getting  State and Territory information from Wikipedia, however we do not want to copy and paste the table : )Here is the URLhttps://en.wikipedia.org/wiki/States_and_territories_of_Australia   We need two libraries to do the task:Check documentations here:* [urllib](https://docs.python.org/2/library/urllib.html)* [BeautifulSoup](https://www.crummy.com/software/BeautifulSoup/bs4/doc/)<jupyter_code>import sys
if sys.version_info[0] == 3:
    from urllib.request import urlopen
else:
    from urllib import urlopen
from bs4 import BeautifulSoup<jupyter_output><empty_output><jupyter_text>We first save the link in wiki<jupyter_code>wiki = "https://en.wikipedia.org/wiki/States_and_territories_of_Australia"<jupyter_output><empty_output><jupyter_text>Then use ulropen to open the page. If you get "SSL: CERTIFICATE_VERIFY_FAILED", what you need to do is find where "Install Certificates.command" file is, and click it to upgrade the certificate. Then, you should be able to solve the problem.<jupyter_code>page = urlopen(wiki)
if sys.version_info[0] == 3:
    page = page.read()<jupyter_output><empty_output><jupyter_text>You will meet BeautifulSoup later in this subject, so don't worry if you feel uncomfortable with it now. You can always revisit. We begin by reading in the source code and creating a Beautiful Soup object with the BeautifulSoup function.<jupyter_code>soup = BeautifulSoup(page, "lxml")<jupyter_output><empty_output><jupyter_text>Then we print and see.<jupyter_code>print(soup.prettify())<jupyter_output><empty_output><jupyter_text>For who do not know much about HTML, this might be a bit overwhelming, but essentially it contains lots of tags in the angled brackets providing structural and formatting information that we don't care so much here. What we need is the table. Let's first check the title.<jupyter_code>soup.title.string<jupyter_output><empty_output><jupyter_text>It looks fine, then we would like to find the table. Let's have a try to extract all contents within the 'table' tag.<jupyter_code>all_tables = soup.findAll('table')
print(all_tables)<jupyter_output><empty_output><jupyter_text>This returns a collection of tag objects. It seems that most of the information are useless and it's getting hard to hunt for the table. So searched online and found an instruction here: https://adesquared.wordpress.com/2013/06/16/using-python-beautifulsoup-to-scrape-a-wikipedia-table/The class is "wikitable sortable"!! Have a try then.<jupyter_code>right_table = soup.find('table', class_='wikitable sortable')
print(right_table)<jupyter_output><empty_output><jupyter_text>Next we need to extract table header row by find the first 'tr'<jupyter_code>head_row = right_table.find('tr')
print(head_row)<jupyter_output><empty_output><jupyter_text>Then we extract header row name via iterate through each row and extract text. The `.findAll` function in Python returns a list containing all the elements, which you can iterate through.<jupyter_code>header_list = []
headers = head_row.findAll('th')
for header in headers:
    #print header.find(text = True)
    header_list.append(header.find(text = True).strip())
header_list<jupyter_output><empty_output><jupyter_text>We can probably iterate through this list and then extract contents. But let's take a simple approach of extracting each column separately.<jupyter_code>flag = []
state = []
abbrev = []
ISO = []
Capital = []
Population = []
Area = []
Seats = []
Gov = []
Premier = []

for row in right_table.findAll("tr"):
    cells = row.findAll('td')
    if len(cells) > 0 : # and len(cells) < 10:
        flag.append(cells[0].find(text=True))
        state.append(cells[1].find(text=True).strip())
        abbrev.append(cells[2].find(text=True).strip())
        ISO.append(cells[3].find(text=True).strip())
        Capital.append(cells[4].find(text=True).strip())
        Population.append(cells[5].find(text=True).strip())
        Area.append(cells[6].find(text=True).strip())
        Seats.append(cells[7].find(text=True).strip())
        Gov.append(cells[8].find(text=True).strip())
        Premier.append(cells[10].find(text=True).strip())<jupyter_output><empty_output><jupyter_text>Next we can append all list to the dataframe.<jupyter_code>df_au = pd.DataFrame()
df_au[header_list[0]] = flag
df_au[header_list[1]] = state
df_au[header_list[2]] = abbrev
df_au[header_list[3]] = ISO
df_au[header_list[4]] = Capital
df_au[header_list[5]] = Population
df_au[header_list[6]] = Area
df_au[header_list[7]] = Seats
df_au[header_list[8]] = Gov
df_au[header_list[9]] = Premier<jupyter_output><empty_output><jupyter_text>Done !<jupyter_code>df_au<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11423592332191575, 'fraction_numerical': 0.014782765458806562, 'mean_word_length': 5.585674713098536, 'pattern_counts': {'":': 0, '<': 142, '<?xml version=': 0, '>': 144, 'https://': 7, 'lorem ipsum': 0, 'www.': 1, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '35', 'dir': 'jupyter-structured-clean-dedup', 'id': '510703', 'license': 'MIT', 'n_tokens_mistral': 5563, 'n_tokens_neox': 5266, 'n_words': 1680, 'path': 'Jupyter/M03-BigData/M03E-DataAcquisition-II.ipynb', 'repo_name': 'tulip-lab/sit742', 'size': '16641'}