In [ ]:
import pandas as pd
def add_full_name(path_to_csv, path_to_new_csv):
#Assume you will be reading in a csv file with the same columns that the
#Lahman baseball data set has -- most importantly, there are columns
#called 'nameFirst' and 'nameLast'.
#1) Write a function that reads a csv
#located at "path_to_csv" into a pandas dataframe and adds a new column
#called 'nameFull' with a player's full name.
#
#For example:
# for Hank Aaron, nameFull would be 'Hank Aaron',
#
#2) Write the data in the pandas dataFrame to a new csv file located at
#path_to_new_csv
#WRITE YOUR CODE HERE
# Create a dataframe which hosted the data from csv file.
baseball_data = pd.read_csv(path_to_csv)
# Create new header called nameFull which contain full name of player.
baseball_data['nameFull'] = baseball_data['nameFirst'] + ' ' + baseball_data['nameLast']
# Write the new thing to new csv file
baseball_data.to_csv(path_to_new_csv)
if __name__ == "__main__":
# For local use only
# If you are running this on your own machine add the path to the
# Lahman baseball csv and a path for the new csv.
# The dataset can be downloaded from this website: http://www.seanlahman.com/baseball-archive/statistics
# We are using the file Master.csv
path_to_csv = ""
path_to_new_csv = ""
add_full_name(path_to_csv, path_to_new_csv)
In [ ]:
# Relational Database
import pandas
import pandasql
def select_first_50(filename):
# Read in our aadhaar_data csv to a pandas dataframe. Afterwards, we rename the columns
# by replacing spaces with underscores and setting all characters to lowercase, so the
# column names more closely resemble columns names one might find in a table.
aadhaar_data = pandas.read_csv(filename)
aadhaar_data.rename(columns = lambda x: x.replace(' ', '_').lower(), inplace=True)
# Select out the first 50 values for "registrar" and "enrolment_agency"
# in the aadhaar_data table using SQL syntax.
#
# Note that "enrolment_agency" is spelled with one l. Also, the order
# of the select does matter. Make sure you select registrar then enrolment agency
# in your query.
#
# You can download a copy of the aadhaar data that we are passing
# into this exercise below:
# https://s3.amazonaws.com/content.udacity-data.com/courses/ud359/aadhaar_data.csv
# SELECT Headers (Attributes) FROM DataFrame with Limitation number of items which will be selected.
q = """
-- YOUR QUERY HERE
SELECT registrar, enrolment_agency FROM aadhaar_data LIMIT 50;
"""
#Execute your SQL command against the pandas frame
aadhaar_solution = pandasql.sqldf(q.lower(), locals())
return aadhaar_solution
More about PandasSQL package:
The pandasql package allows us to perform queries on dataframes using the SQLite syntax.
In [ ]:
import pandas
import pandasql
def aggregate_query(filename):
# Read in our aadhaar_data csv to a pandas dataframe. Afterwards, we rename the columns
# by replacing spaces with underscores and setting all characters to lowercase, so the
# column names more closely resemble columns names one might find in a table.
aadhaar_data = pandas.read_csv(filename)
aadhaar_data.rename(columns = lambda x: x.replace(' ', '_').lower(), inplace=True)
# Write a query that will select from the aadhaar_data table how many men and how
# many women over the age of 50 have had aadhaar generated for them in each district.
# aadhaar_generated is a column in the Aadhaar Data that denotes the number who have had
# aadhaar generated in each row of the table.
#
# Note that in this quiz, the SQL query keywords are case sensitive.
# For example, if you want to do a sum make sure you type 'sum' rather than 'SUM'.
#
# The possible columns to select from aadhaar data are:
# 1) registrar
# 2) enrolment_agency
# 3) state
# 4) district
# 5) sub_district
# 6) pin_code
# 7) gender
# 8) age
# 9) aadhaar_generated
# 10) enrolment_rejected
# 11) residents_providing_email,
# 12) residents_providing_mobile_number
#
# You can download a copy of the aadhaar data that we are passing
# into this exercise below:
# https://s3.amazonaws.com/content.udacity-data.com/courses/ud359/aadhaar_data.csv
q = """SELECT gender, district, SUM(aadhaar_generated) FROM aadhaar_data WHERE age>50 GROUP BY gender, district;"""
# Execute your SQL command against the pandas frame
aadhaar_solution = pandasql.sqldf(q.lower(), locals())
return aadhaar_solution
We can access data on files or database but what about the data that sits in websites.
We need to crawl the websites and parsing through all html is complicated. Another way is using this website's api (application programming interface) which can provide us machine readable format data.
There are several different kinds of APIs but one of the most common types is representational state transfer (REST)
In [ ]:
import json
import requests
import pprint
def api_get_request(url):
# In this exercise, you want to call the last.fm API to get a list of the
# top artists in Spain. The grader will supply the URL as an argument to
# the function; you do not need to construct the address or call this
# function in your grader submission.
#
# Once you've done this, return the name of the number 1 top artist in
# Spain.
# More about pprint here: https://docs.python.org/2/library/pprint.html
# Make API call using the request library and load the results into a dictionary.
data = requests.get(url).text
data = json.loads(data)
pp = pprint.PrettyPrinter(indent = 4)
# Print out the name of the #1 artist, we look at the topartists
# key then artist key then the first entry and there is the name.
pp.pprint(data['topartists']['artist'][0]['name'])
return data['topartists']['artist'][0]['name'] # return the top artist in Spain
In [ ]:
import pandas
import numpy
def imputation(filename):
# Pandas dataframes have a method called 'fillna(value)', such that you can
# pass in a `single value` to replace any NAs in a dataframe or series. You
# can call it like this:
# dataframe['column'] = dataframe['column'].fillna(value)
#
# Using the numpy.mean function, which calculates the mean of a numpy
# array, impute any missing values in our Lahman baseball
# data sets 'weight' column by setting them equal to the average weight.
#
# You can access the 'weight' colum in the baseball data frame by
# calling baseball['weight']
baseball = pandas.read_csv(filename)
#YOUR CODE GOES HERE
mean_weight = numpy.mean(baseball['weight'])
# print mean_weight
baseball['weight'] = baseball['weight'].fillna(mean_weight)
return baseball