# Import pandas as pdimportpandasaspd# Read the CSV and assign it to the variable datadata=pd.read_csv("vt_tax_data_2016.csv")
# View the first few lines of dataprint(data.head())
Get data from other flat files
# Import pandas with the alias pdimportpandasaspd# Load TSV using the sep keyword argument to set delimiterdata=pd.read_csv("vt_tax_data_2016.tsv", sep="\t")
# Plot the total number of tax returns by income groupcounts=data.groupby("agi_stub").N1.sum()
counts.plot.bar()
plt.show()
Import a subset of columns
# Create list of columns to usecols= ["zipcode", "agi_stub", "mars1", "MARS2", "NUMDEP"]
# Create data frame from csv using only selected columnsdata=pd.read_csv("vt_tax_data_2016.csv", usecols=cols)
# View counts of dependents and tax returns by income levelprint(data.groupby("agi_stub").sum())
Import a file in chunks
# Create data frame of next 500 rows with labeled columnsvt_data_next500=pd.read_csv("vt_tax_data_2016.csv",
nrows=500,
skiprows=500,
header=None,
names=list(vt_data_first500))
# View the Vermont data frames to confirm they're differentprint(vt_data_first500.head())
print(vt_data_next500.head())
Specify data types
# Create dict specifying data types for agi_stub and zipcodedata_types= {'agi_stub': 'category',
'zipcode': str}
# Load csv using dtype to set correct data typesdata=pd.read_csv("vt_tax_data_2016.csv", dtype=data_types)
# Print data types of resulting frameprint(data.dtypes.head())
Set custom NA values
# Create dict specifying that 0s in zipcode are NA valuesnull_values= {"zipcode": 0}
# Load csv using na_values keyword argumentdata=pd.read_csv("vt_tax_data_2016.csv",
na_values=null_values)
# View rows with NA ZIP codesprint(data[data.zipcode.isna()])
Skip bad data
try:
# Set warn_bad_lines to issue warnings about bad recordsdata=pd.read_csv("vt_tax_data_2016_corrupt.csv",
error_bad_lines=False,
warn_bad_lines=True)
# View first 5 recordsprint(data.head())
exceptpd.io.common.CParserError:
print("Your data contained rows that could not be parsed.")
2. Importing Data From Excel Files
Get data from a spreadsheet
# Load pandas as pdimportpandasaspd# Read spreadsheet and assign it to survey_responsessurvey_responses=pd.read_excel("fcc_survey.xlsx")
# View the head of the data frameprint(survey_responses.head())
Load a portion of a spreadsheet
# Create string of lettered columns to loadcol_string="AD,AW:BA"# Load data with skiprows and usecols setsurvey_responses=pd.read_excel("fcc_survey_headers.xlsx",
skiprows=2,
usecols=col_string)
# View the names of the columns selectedprint(survey_responses.columns)
Select a single sheet
### Create df from second worksheet by referencing its positionresponses_2017=pd.read_excel("fcc_survey.xlsx",
sheet_name=1)
# Graph where people would like to get a developer jobjob_prefs=responses_2017.groupby("JobPref").JobPref.count()
job_prefs.plot.barh()
plt.show()
### Create df from second worksheet by referencing its nameresponses_2017=pd.read_excel("fcc_survey.xlsx",
sheet_name='2017')
# Graph where people would like to get a developer jobjob_prefs=responses_2017.groupby("JobPref").JobPref.count()
job_prefs.plot.barh()
plt.show()
Select multiple sheets
### Load both the 2016 and 2017 sheets by nameall_survey_data=pd.read_excel("fcc_survey.xlsx",
sheet_name=['2016', '2017'])
# View the data type of all_survey_dataprint(type(all_survey_data))
### Load all sheets in the Excel fileall_survey_data=pd.read_excel("fcc_survey.xlsx",
sheet_name=[0, '2017'])
# View the sheet names in all_survey_dataprint(all_survey_data.keys())
### Load all sheets in the Excel fileall_survey_data=pd.read_excel("fcc_survey.xlsx",
sheet_name=None)
# View the sheet names in all_survey_dataprint(all_survey_data.keys())
Work with multiple spreadsheets
# Create an empty data frameall_responses=pd.DataFrame()
# Set up for loop to iterate through values in responsesfordfinresponses.values():
# Print the number of rows being addedprint("Adding {} rows".format(df.shape[0]))
# Append df to all_responses, assign resultall_responses=all_responses.append(df)
# Graph employment statuses in samplecounts=all_responses.groupby("EmploymentStatus").EmploymentStatus.count()
counts.plot.barh()
plt.show()
Set Boolean columns
### Load the datasurvey_data=pd.read_excel("fcc_survey_subset.xlsx")
# Count NA values in each columnprint(survey_data.isna().sum())
### Set dtype to load appropriate column(s) as Boolean datasurvey_data=pd.read_excel("fcc_survey_subset.xlsx",
dtype={"HasDebt": bool})
# View financial burdens by Boolean groupprint(survey_data.groupby('HasDebt').sum())
Set custom true/false values
# Load file with Yes as a True value and No as a False valuesurvey_subset=pd.read_excel("fcc_survey_yn_data.xlsx",
dtype={"HasDebt": bool,
"AttendedBootCampYesNo": bool},
true_values=["Yes"],
false_values=["No"])
# View the dataprint(survey_subset.head())
Parse simple dates
# Load file, with Part1StartTime parsed as datetime datasurvey_data=pd.read_excel("fcc_survey.xlsx",
parse_dates=["Part1StartTime"])
# Print first few values of Part1StartTimeprint(survey_data.Part1StartTime.head())
Get datetimes from multiple columns
# Create dict of columns to combine into new datetime columndatetime_cols= {"Part2Start": ["Part2StartDate", "Part2StartTime"]}
# Load file, supplying the dict to parse_datessurvey_data=pd.read_excel("fcc_survey_dts.xlsx",
parse_dates=datetime_cols)
# View summary statistics about Part2Startprint(survey_data.Part2Start.describe())
Parse non-standard date formats
# Parse datetimes and assign result back to Part2EndTimesurvey_data["Part2EndTime"] =pd.to_datetime(survey_data["Part2EndTime"],
format="%m%d%Y %H:%M:%S")
# Print first few values of Part2EndTimeprint(survey_data["Part2EndTime"].head())
3. Importing Data from Databases
Connect to a database
# Import sqlalchemy's create_engine() functionfromsqlalchemyimportcreate_engine# Create the database engineengine=create_engine("sqlite:///data.db")
# View the tables in the databaseprint(engine.table_names())
Load entire tables
### Load librariesimportpandasaspdfromsqlalchemyimportcreate_engine# Create the database engineengine=create_engine('sqlite:///data.db')
# Load hpd311calls without any SQLhpd_calls=pd.read_sql('hpd311calls', engine)
# View the first few rows of dataprint(hpd_calls.head())
### Create the database engineengine=create_engine("sqlite:///data.db")
# Create a SQL query to load the entire weather tablequery="""SELECT * FROM weather;"""# Load weather with the SQL queryweather=pd.read_sql(query, engine)
# View the first few rows of dataprint(weather.head())
Refining imports with SQL queries
# Create database engine for data.dbengine=create_engine('sqlite:///data.db')
# Write query to get date, tmax, and tmin from weatherquery="""SELECT date, tmax, tmin FROM weather;"""# Make a data frame by passing query and engine to read_sql()temperatures=pd.read_sql(query, engine)
# View the resulting data frameprint(temperatures)
Selecting rows
# Create query to get hpd311calls records about safetyquery="""SELECT *FROM hpd311callsWHERE complaint_type = 'SAFETY';"""# Query the database and assign result to safety_callssafety_calls=pd.read_sql(query, engine)
# Graph the number of safety calls by boroughcall_counts=safety_calls.groupby('borough').unique_key.count()
call_counts.plot.barh()
plt.show()
Filtering on multiple conditions
# Create query for records with max temps <= 32 or snow >= 1query="""SELECT * FROM weather WHERE tmax <= 32 OR snow >= 1;"""# Query database and assign result to wintry_dayswintry_days=pd.read_sql(query, engine)
# View summary stats about the temperaturesprint(wintry_days.describe())
Getting distinct values
# Create query for unique combinations of borough and complaint_typequery="""SELECT DISTINCT borough, complaint_type FROM hpd311calls;"""# Load results of query to a data frameissues_and_boros=pd.read_sql(query, engine)
# Check assumption about issues and boroughsprint(issues_and_boros)
Counting in groups
# Create query to get call counts by complaint_typequery="""SELECT DISTINCT complaint_type, count(*) FROM hpd311calls GROUP BY complaint_type;"""# Create data frame of call counts by issuecalls_by_issue=pd.read_sql(query, engine)
# Graph the number of calls for each housing issuecalls_by_issue.plot.barh(x="complaint_type")
plt.show()
Working with aggregate functions
# Create query to get temperature and precipitation by monthquery="""SELECT month, MAX(tmax), MIN(tmin), SUM(prcp) FROM weather GROUP BY month;"""# Get data frame of monthly weather statsweather_by_month=pd.read_sql(query, engine)
# View weather stats by monthprint(weather_by_month)
Joining tables
# Query to join weather to call records by date columnsquery="""SELECT * FROM hpd311calls JOIN weather ON hpd311calls.created_date = weather.date;"""# Create data frame of joined tablescalls_with_weather=pd.read_sql(query, engine)
# View the data frame to make sure all columns were joinedprint(calls_with_weather.head())
Joining and filtering
### Query to get hpd311calls and precipitation valuesquery="""SELECT hpd311calls.*, weather.prcp FROM hpd311calls JOIN weather ON hpd311calls.created_date = weather.date;"""# Load query results into the leak_calls data frameleak_calls=pd.read_sql(query, engine)
# View the data frameprint(leak_calls.head())
### Query to get water leak calls and daily precipitationquery="""SELECT hpd311calls.*, weather.prcp FROM hpd311calls JOIN weather ON hpd311calls.created_date = weather.date WHERE hpd311calls.complaint_type = 'WATER LEAK';"""# Load query results into the leak_calls data frameleak_calls=pd.read_sql(query, engine)
# View the data frameprint(leak_calls.head())
Joining, filtering, and aggregating
# Modify query to join tmax and tmin from weather by datequery="""SELECT hpd311calls.created_date, COUNT(*), weather.tmax, weather.tmin FROM hpd311calls JOIN weather ON hpd311calls.created_date = weather.date WHERE hpd311calls.complaint_type = 'HEAT/HOT WATER' GROUP BY hpd311calls.created_date; """# Query database and save results as dfdf=pd.read_sql(query, engine)
# View first 5 recordsprint(df.head())
4. Importing JSON Data and Working with APIs
Load JSON data
# Load pandas as pdimportpandasaspd# Load the daily report to a data framepop_in_shelters=pd.read_json('dhs_daily_report.json')
# View summary stats about pop_in_sheltersprint(pop_in_shelters.describe())
Work with JSON orientations
try:
# Load the JSON with orient specifieddf=pd.read_json("dhs_report_reformatted.json",
orient="split")
# Plot total population in shelters over timedf["date_of_census"] =pd.to_datetime(df["date_of_census"])
df.plot(x="date_of_census",
y="total_individuals_in_shelter")
plt.show()
exceptValueError:
print("pandas could not parse the JSON.")
Get data from an API
api_url="https://api.yelp.com/v3/businesses/search"# Get data about NYC cafes from the Yelp APIresponse=requests.get(api_url,
headers=headers,
params=params)
# Extract JSON data from the responsedata=response.json()
# Load data to a data framecafes=pd.DataFrame(data["businesses"])
# View the data's dtypesprint(cafes.dtypes)
Set API parameters
# Create dictionary to query API for cafes in NYCparameters= {"term": "cafe",
"location": "NYC"}
# Query the Yelp API with headers and params setresponse=requests.get(api_url,
headers=headers,
params=parameters)
# Extract JSON data from responsedata=response.json()
# Load "businesses" values to a data frame and print headcafes=pd.DataFrame(data["businesses"])
print(cafes.head())
Set request headers
# Create dictionary that passes Authorization and key stringheaders= {"Authorization": "Bearer {}".format(api_key)}
# Query the Yelp API with headers and params setresponse=requests.get(
api_url,
headers=headers,
params=params)
# Extract JSON data from responsedata=response.json()
# Load "businesses" values to a data frame and print namescafes=pd.DataFrame(data["businesses"])
print(cafes.name)
Flatten nested JSONs
# Load json_normalize()frompandas.io.jsonimportjson_normalize# Isolate the JSON data from the API responsedata=response.json()
# Flatten business data into a data frame, replace separatorcafes=json_normalize(data["businesses"],
sep="_")
# View dataprint(cafes.head())
Handle deeply nested data
# Load other business attributes and set meta prefixflat_cafes=json_normalize(data["businesses"],
sep="_",
record_path="categories",
meta=["name",
"alias",
"rating",
["coordinates", "latitude"],
["coordinates", "longitude"]],
meta_prefix="biz_")
# View the dataprint(flat_cafes.head())
Append data frames
# Add an offset parameter to get cafes 51-100params= {"term": "cafe",
"location": "NYC",
"sort_by": "rating",
"limit": 50,
"offset": 50}
result=requests.get(api_url, headers=headers, params=params)
next_50_cafes=json_normalize(result.json()["businesses"])
# Append the results, setting ignore_index to renumber rowscafes=top_50_cafes.append(next_50_cafes, ignore_index=True)
# Print shape of cafesprint(cafes.shape)
Merge data frames
# Merge crosswalk into cafes on their zip code fieldscafes_with_pumas=cafes.merge(crosswalk,
left_on="location_zip_code",
right_on="zipcode")
# Merge pop_data into cafes_with_pumas on puma fieldcafes_with_pop=cafes_with_pumas.merge(pop_data, on="puma")
# View the dataprint(cafes_with_pop.head())