<jupyter_start><jupyter_text>dataScience30 [Day 4: Data Wrangling](https://youtu.be/Xn2pzNoP3i0) By [<NAME>](https://www.youtube.com/channel/UCErSNiDZV4rJCNB8NrDGREA)---  Table of Contents:- [Objectives](Objectives:)- [Code Along](Code-Along:)- [Assignments](Assignments:)- [BONUS](BONUS:)- [Vocabulary](Vocabulary:)- [References](References:)- [Have Feedback?](Have-Feedback?)  Objectives:- Participants will gain experience loading data from files, web scraping, and APIs- Participants will gain experience writing data to a file- Participants will gain experience creating a data dictionary  Code Along:  Imports<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>Loading data from filesWe can load a raw CSV file as a DataFrame using `pd.read_csv("FILE_PATH")`Check out the [Pandas DataFrame Documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html) for more on what you can do with a DataFrame.<jupyter_code># load the "ads.csv" file as the variable ads_data
ads_data = pd.read_csv("ads.csv")<jupyter_output><empty_output><jupyter_text>An Aside: Relative file pathsThe file system for this module will look something like this..._NOTE: folders have rounded edges, and files have sharp edges._Notice this notebook and the "data" folder are both within the "day_4_data_wrangling" folder. They're both on the same level. In order to properly access the "ads.csv" file, we need to go into the "data folder". Since __code in this notebook executes relative to this file__, we can look at pathing from this file's perspective - that is to say, if we pretended to walk from this file to the file we wanted, we'd have to go into the "data" folder and then declare the file name.__Therefore, our file path is "data/ads.csv"__, and not just "ads.csv".What might you expect the path to the "wine.csv" file to be?<jupyter_code>ads_data = 
ads_data.head()<jupyter_output><empty_output><jupyter_text>That first column ("Unnamed: 0") is odd...it looks more like an index column.After typing in `pd.read_csv()`, try putting your cursor in between the parentheses and press Shift+Tab. This should pull up the help documentation. If you keep pressing Shift+Tab, the window expands.There are plenty of parameters to specify, but we're interested in `index_col`. Let's set the index_col to the 0th column (in python we start counting from 0!).<jupyter_code>ads_data = 
# try ads_data.head()<jupyter_output><empty_output><jupyter_text>Try out some basic Pandas Dataframe methods:- `ads_data.tail()`- `ads_data.describe()`- `ads_data.dtypes`- `ads_data.info()` Let's load in the "superstore.xls" file using the `pd.read_excel()` method and store the data in the variable `store_data`._Note: be mindful of the relative path to the file and the index column!_<jupyter_code>store_data = 
# check out "store_data.head()"<jupyter_output><empty_output><jupyter_text>Try using `store_data.describe()`. What do you notice is missing? Categorical, or string data (AKA the "object" datatype), information is not includeded in `.describe()` by default.Try `store_data.describe(include="all")` `store_data.describe(include="all")` included ALL of the columns.  Take a moment to make sense of how numeric columns and categorical columns are treated differently. Type `pd.read_` and press Tab to see the possibilities via auto-complete.As you can see, there are plenty of ways to read in data using Pandas!  Loading data from web scrapingWeb scraping takes advantage of the consistent structure websites have. When a website displays data, there is typically a table or table-like structure to it that we can take advantage of.Web scraping is essentially going to a website and pulling data from it, and parsing out the parts we want into a form we want to work with. For example, I'm interested in a sports team, but don't want to keep track of every little change to the roster over time - there are plenty of websites that already keep track of that kind of thing! I can just write a script that pulls the data from the website and gives me all of the players, their names, performance data, etc.To accomplish this we can use the Beautiful Soup package (there are plenty of web scraping libraries out there, so don't feel married to this one!). Feel free to check out the [Beautiful Soup Documentation](https://www.crummy.com/software/BeautifulSoup/bs4/doc/).<jupyter_code>import pandas as pd
import requests # this will handle the raw data, which will then be passed to BeautifulSoup
from bs4 import BeautifulSoup
# Website URL where data is
URL = "https://www.espn.com/mlb/team/roster/_/name/nym"

# "visit" the website and capture the response
response = requests.get(URL)

# pull just the HTML text as a large string - we'll feed this into BeautifulSoup
html = response.text
# See the first 1000 characters<jupyter_output><empty_output><jupyter_text>Instantiate a BeautifulSoup object named `soup` with the `html` string variable<jupyter_code>soup =<jupyter_output><empty_output><jupyter_text>Understanding HTML goes beyond the scope of this course, however, [here is a simple explaination for how HTML structures information](https://www.youtube.com/watch?v=bWPMSSsVdPk).To put it succinctly, HTML content is placed between tags. Tags can be placed inside other tags, and this feature of nesting information is how data is structured in HTML.For example: if we want to display a table with rows and columns we might contain the entire table in its own tag (`...`). Then we can put the header row (`...`) inside of it. Other rows (`...`) will come after the header_row. Inside each row will be the associated columns (`...`), which contains the raw text data inside of it.```html            Name        Age        Score                    Bob        19        B-                    Sally        34        A+    ```Becomes...            Name        Age        Score                Bob        19        B-                Sally        34        A+     The `soup` object has a bunch of methods for querying the information inside of the HTML, which can actually be chained.  Use the `.find()` method to find the first "table", and store it in the variable `table`.<jupyter_code>table =<jupyter_output><empty_output><jupyter_text>The `table` object is much like the `soup` object, except it ONLY contains the information inside of the table. This tends to be easier to work with than the entire HTML document._NOTE: In the earlier example, I simplified the tag names to keep things clear. The actual tag names are "tr" for `` and "td" for ``._<jupyter_code># Use .find_all() to get all of the "tr" elements within the table object and store the results in the "rows" variable

# Initialize the variable "contents" as an empty list
contents = []

# Loop through each row in rows and append the result of calling `.get_text(";")` on each row
for row in rows:
    contents.append(row.get_text(";")) #gathers text for each row with semi-colon, ";", as delimiter
# Look at the contents list
contents<jupyter_output><empty_output><jupyter_text>_NOTE: There really is no single correct approach to web scrapping. The steps before and after this point came from much trial and error as well as familiarity with the website. If you plan to scrap data, be prepared to do the same!_<jupyter_code># The headers were off by 1 column because of how the website puts the name and player number together
headers = contents.pop(0).split(";")
headers.insert(1,"Num")
headers
# Putting everything into a DataFrame
data_splits = [s.split(";") for s in contents]
players_data = pd.DataFrame(data_splits, columns = headers)
players_data.head()<jupyter_output><empty_output><jupyter_text>An Aside: robots.txtWeb scrapping can be easy once you have a script up and running. HOWEVER, automated web queries can cause havoc to even the most robust servers, which is why __most web servers include a "robots.txt" file, which details the expected behavior developers should employ when automactically querying their servers.__  It's important to __be respectful__ and not cause an undue burden on someone else. Doing so can __quickly get you banned or blacklisted__. So, exercise discretion when scrapping website by checking the "robots.txt" file BEFORE even thinking about scrapping from a site.You can typically go to the main website you WOULD want to scrap from and in the URL add "/robots.txt" to see which users agents are allowed to do what. For example: https://twitter.com => https://twitter.com/robots.txtAs you can see, Twitter has a LOT of consideration for who is querying their servers. Since you likely won't have a bot or user-agent already defined in this file, you will fall under the blanket user-agent `User-agent: *` (near bottom). Here, you can see which routes are restricted and allowed.For example: https://www.reddit.com => https://www.reddit.com/robots.txtReddit has a similar set of concerns, but very different permissions for `User-agent: *`.  An Aside: delaying requestsIn addition to respecting how server maintainers wish their servers to be treated, you can purposefully delay repeated requests using the `time` library<jupyter_code>import time

for i in range(5):
    print(i)
    time.sleep(1)<jupyter_output><empty_output><jupyter_text>Loading data from an APIThe last major way data is made accessible is through an API interface.  What is an API?API stands for Application Program Interface, which is just a fancy way of saying computers talking to other computers.When we humans access a website, we enjoy a visually appealing website with nice interactions. Computers don't require such nicities - instead, raw data is often transferred in some standard format (usually JSON).JSON stands for JavaScript Object Notation. No, we won't be learning JavaScript! This if JSON as just a basic format for how raw data is transferred between computers and look something like this...```javascript{    "some_key" : "some value",    "an_array_or_list" : [1,2,3],    "an_object_or_dictionary" : {        "another_key":"another value"    }}``` We'll use the `requests` library again.<jupyter_code>import pandas as pd
import requests<jupyter_output><empty_output><jupyter_text>Let's request a random user from the [Random User API](https://randomuser.me/)<jupyter_code>URL = "https://randomuser.me/api/"

response = requests.get(URL)
# What does this request look like?
response.text<jupyter_output><empty_output><jupyter_text>Notice the difference in response between this API URL and the website URL we ran before. This is not HTML, it's JSON!As a convenience, the response object has a `.json()` method, which turns the JSON text into a python dictionary!<jupyter_code>random_user = response.json()
random_user<jupyter_output><empty_output><jupyter_text>Now, we can access the data directly and do whatever we typically would in Python.<jupyter_code># Get the gender of the random_user
random_user["results"][0]["gender"]
# Get the username of the random_user

# Get the large picture of the random_user<jupyter_output><empty_output><jupyter_text>Handling URL parametersTypically APIs allow you to query their database through URL parameters. URL parameters are added at the end of a URL and gives more context to the request.For example, the [Random User API](https://randomuser.me/) will allow you to request multiple random users, but you'll have to tell it how many you want within the URL string.<jupyter_code>BASE_URL = "https://randomuser.me/api/"
number_of_users = 10
URL = "{}?results={}".format(BASE_URL, number_of_users)
response = requests.get(URL)<jupyter_output><empty_output><jupyter_text>Check out what the final URL looks like<jupyter_code>URL<jupyter_output><empty_output><jupyter_text>Notice the separation of the base url and the parameters is done with a question mark, ?.Also, the parameters come in the form a key:value pairs, which are separated by an equal sign, =.<jupyter_code># How many random users are found in the results?
len(response.json()["results"])<jupyter_output><empty_output><jupyter_text>Adding additional parameters is a simple as including more key:value pairs, except the pairs themselves are separated by an ampersand, &.<jupyter_code>BASE_URL = "https://randomuser.me/api/"
number_of_users = 10
gender = "female"
URL = "{}?results={}&gender={}".format(BASE_URL, number_of_users, gender)
response = requests.get(URL)
URL<jupyter_output><empty_output><jupyter_text>Handling an API requiring an API KeyThe above previous APIs are great tools for learning about APIs.  However, more often than not, an API will require you register or have an account with the service. You will be given an API KEY, which is used by the server maintainers to "keep the peace" and ensure users are not abusing the service (i.e. "spamming" the server causing a "denial of service"). Often, an API will include some quota restricting how often you are allowed to ping the API. Again, this is used to ensure the server runs smoothly for all users and you'll need to check the API documentation to learn exactly how you're expected to work with the service. __Go to http://www.omdbapi.com/ and register for an API Key__ (for "Account Type" select "Free! (1000 daily limit)"). Your API key will be emailed to you and your account will require activation via that same email. This process is fairly representative of other API services._NOTE: Your API Key is akin to your username and password. NEVER disclose your API Key!__NOTE: This course is not endorsed by or affiliated with OMDb. As of this writing, the OMDb API is simply easy to use as an example API requiring an API Key._<jupyter_code>API_KEY = # paste your API Key here as a string
movie = "The Matrix"
BASE_URL = "http://www.omdbapi.com/"
URL = "{}?apikey={}&t={}".format(BASE_URL, API_KEY, movie)
URL
response = requests.get(URL)
response.text<jupyter_output><empty_output><jupyter_text>Delaying API requests in loopsLet's query the OMBd API with a bunch of movies!It would be easy to create a loop and run all of the requests. However, our python code would execute everything within milliseconds of each other. This might cause the OMBd servers to assume we're a malicious attacker trying to cause a denial of service, so let's be respectful and include a short delay between our queries.<jupyter_code>import time
movies = ["The Matrix", "War Games", "Sneakers","Tron","The Net"]

API_KEY = # paste your API Key here as a string

BASE_URL = "http://www.omdbapi.com/"

results = [] # storing results

for movie in movies:
    # forming URL for each movie
    URL = "{}?apikey={}&t={}".format(BASE_URL, API_KEY, movie)
    
    # making request and storing response
    response = requests.get(URL)
    
    # converting response to Python Dictionary and adding to stored results
    results.append(response.json())
    
    # adding delay
    time.sleep(1)
results<jupyter_output><empty_output><jupyter_text>Convert API data to DataFrameLets take our results and add them to a dataframe.Thankfully, `pd.DataFrame()` is set up to handle dictionary-style datasets by assuming the keys serve as the column names and the values are the values for each row.<jupyter_code>movie_df = pd.DataFrame(results)
movie_df
# Observe the first value in the "Ratings" column
movie_df["Ratings"][0]<jupyter_output><empty_output><jupyter_text>The convenience is great, but not perfect.  We see nested values, such as the "Ratings" column are difficult to parse.  However, we are set up to clean these data in typical Pandas fashion!  Writing data (pd.DataFrame) to a fileOnce you've obtained your data in memory, you'll want to store the information as a file on your hard drive. This will ensure you're not making redundant or excessive server requests.Simply take the DataFrame you've made and call `.to_csv("FILE_PATH")` on it. Of course, there are other formats you can write to, but CSV is the most common.<jupyter_code>movie_df.to_csv("movies.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07794807632155146, 'fraction_numerical': 0.0028151391929934315, 'mean_word_length': 4.7983315197678635, 'pattern_counts': {'":': 1, '<': 94, '<?xml version=': 0, '>': 96, 'https://': 15, 'lorem ipsum': 0, 'www.': 9, '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': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '107524', 'license': 'MIT', 'n_tokens_mistral': 4512, 'n_tokens_neox': 4324, 'n_words': 2078, 'path': 'day_4_data_wrangling/data_wrangling.ipynb', 'repo_name': 'Shaddyjr/dataScience30', 'size': '15995'}