<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Python for Data Analysis
This is a hands on workshop aimed at getting you comfortable with the the syntax of core data analysis concepts in Python. Some background in base Python is useful, but not required to learn from this workshop.
* Keystrokes for the IPython notebook
* Reading and Summarizing Data
* Filtering and Sorting Data
* Modifying Columns
* Handling Missing Values
* EXERCISE
Step1:  Summarize the data that was just read in
Step2:  Filtering and Sorting Data
Step3:  Sorting
Step4:  Modifying Columns
Step5:  Handling Missing Values
Step6:  Exercise
Step7:  Solutions
Step8:  Indexing and Slicing Data
Create a new index
Step9:  loc
Step10:  iloc
Step11:  Add another level to the index
Step12:  Analyzing Across Time
Step13:  Split-Apply-Combine
Drawing by Hadley Wickham
Step14:  Merging Data
Step15:  Writing Data
Step16:  Other Useful Features
Detect duplicate rows
Step17:  Map existing values to other values
Step18:  Pivot rows to columns
Step19:  Randomly sample a DataFrame
Step20:  Replace all instances of a value
Step21:  One more thing...
<ASSISTANT_TASK:>
Python Code:
# Import the pandas and numpy libraries
import pandas as pd
import numpy as np
# Read a file with an absolute path
ufo = pd.read_csv('/Users/josiahdavis/Documents/GitHub/python_data_analysis/ufo_sightings.csv')
# Alterntively, read the the file using a relative path
ufo = pd.read_csv('ufo_sightings.csv')
# Alterntively read in the file from the internet
ufo = pd.read_csv('https://raw.githubusercontent.com/josiahdavis/python_data_analysis/master/ufo_sightings.csv')
# Get help on a function
help(pd.read_csv)
Explanation: Python for Data Analysis
This is a hands on workshop aimed at getting you comfortable with the the syntax of core data analysis concepts in Python. Some background in base Python is useful, but not required to learn from this workshop.
* Keystrokes for the IPython notebook
* Reading and Summarizing Data
* Filtering and Sorting Data
* Modifying Columns
* Handling Missing Values
* EXERCISE: Working with drinks data
* Indexing and Slicing Data
* Analyzing across time
* Split-Apply-Combine
* Merging Data
* Writing Data
* Other Useful Features
Keystrokes for the IPython Notebook
There are two modes: Command (enabled by esc) and Edit (enabled by enter). The table below has a quick reference of the main keystrokes that I will be using in the workshop. To get the full list go to Help -> Keyboard Shortcuts.
|          | Mac    | PC    |
|:----------|:--------|:-------|
| Command Mode  | esc    |       |
| Delete   | d, d     |       |
| Markdown   | m     |       |
| Run Cell   | control, return    |       |
| Run Cell and Insert Below   | option, return    |       |
| Insert Above   | a    |       |
| Insert Below   | b    |       |
| Edit Mode     | return | Enter |
| Run Cell | control, return     |       |
| Run Cell and Insert Below   | option, return    |       |
Reading and Summarizing Data
Reading Data
End of explanation
ufo.head(10)          # Look at the top 10 observations
ufo.tail()            # Bottom x observations (defaults to 5)
ufo.describe()        # get summary statistics for columns
ufo.index             # "the index" (aka "the labels")
ufo.columns           # column names (which is "an index")
ufo.dtypes            # data types of each column
ufo.values            # underlying numpy array
ufo.info()            # concise summary
Explanation: Summarize the data that was just read in
End of explanation
# Select a single column
ufo['State']
ufo.State                       # This is equivalent
# Select multiple columns
ufo[['State', 'City','Shape Reported']]
my_cols = ['State', 'City', 'Shape Reported']
ufo[my_cols]                    # This is equivalent
# Logical filtering
ufo[ufo.State == 'TX']          # Select only rows where State == 'TX'
ufo[~(ufo.State == 'TX')]       # Select everything where the test fails
ufo[ufo.State != 'TX']          # Same thing as before
ufo.City[ufo.State == 'TX']     # Select only city columm where State == 'TX'
ufo[ufo.State == 'TX'].City     # Same thing as before
ufo[(ufo.State == 'CA') | (ufo.State =='TX')] # Select only records where State is 'CA' or State is 'TX'
ufo_dallas = ufo[(ufo.City == 'Dallas') & (ufo.State =='TX')]  # Select only Dallas, TX records
ufo[ufo.City.isin(['Austin','Dallas', 'Houston'])] # Select only Austin, Dallas, or Houston records
Explanation: Filtering and Sorting Data
End of explanation
ufo.State.order()                               # only works for a Series
ufo.sort_index(inplace=True)                    # sort rows by label
ufo.sort_index(ascending=False, inplace=False)
ufo.sort_index(by='State')                      # sort rows by specific column
ufo.sort_index(by=['State', 'Shape Reported'])  # sort by multiple columns
ufo.sort_index(by=['State', 'Shape Reported'], ascending=[False, True], inplace=True)  # specify sort order
Explanation: Sorting
End of explanation
# Add a new column as a function of existing columns
ufo['Location'] = ufo['City'] + ', ' + ufo['State']
ufo.head()
# Rename columns
ufo.rename(columns={'Colors Reported':'Colors', 'Shape Reported':'Shape'}, inplace=True)
ufo.head()
# Hide a column (temporarily)
ufo.drop(['Location'], axis=1)
# Delete a column (permanently)
del ufo['Location']
Explanation: Modifying Columns
End of explanation
# Missing values are often just excluded
ufo.describe()                          # Excludes missing values
ufo.Shape.value_counts()                # Excludes missing values
ufo.Shape.value_counts(dropna=False)    # Includes missing values
# Find missing values in a Series
ufo.Shape.isnull()       # True if NaN, False otherwise
ufo.Shape.notnull()      # False if NaN, True otherwise
ufo.Shape.isnull().sum() # Count the missing values
# Find missing values in a DataFrame
ufo.isnull()
# Count the missing values in a DataFrame
ufo.isnull().sum()
# Exclude rows with missing values in a dataframe
ufo[(ufo.Shape.notnull()) & (ufo.Colors.notnull())]
# Drop missing values
ufo.dropna()             # Drop a row if ANY values are missing
ufo.dropna(how='all')    # Drop a row only if ALL values are missing
# Fill in missing values for a series
ufo.Colors.fillna(value='Unknown', inplace=True)
# Fill in missing values for the DataFrame
ufo.fillna(value='Unknown', inplace=True)
Explanation: Handling Missing Values
End of explanation
# Read drinks.csv (in the 'drinks_data' folder) into a DataFrame called 'drinks'
# Print the first 10 rows
# Examine the data types of all columns
# Print the 'beer_servings' Series
# Calculate the average 'beer_servings' for the entire dataset
# Print all columns, but only show rows where the country is in Europe
# Calculate the average 'beer_servings' for all of Europe
# Only show European countries with 'wine_servings' greater than 300
# Determine which 10 countries have the highest 'total_litres_of_pure_alcohol'
# Determine which country has the highest value for 'beer_servings'
# Count the number of occurrences of each 'continent' value and see if it looks correct
# Determine which countries do not have continent designations
# Determine the number of countries per continent. Does it look right?
Explanation: Exercise: Working with the Drinks Data
(Be on the lookout for a curveball question)
End of explanation
# Read drinks.csv (in the drinks_data folder) into a DataFrame called 'drinks'
drinks = pd.read_csv('drinks_data/drinks.csv')
# Print the first 10 rows
drinks.head(10)
# Examine the data types of all columns
drinks.dtypes
drinks.info()
# Print the 'beer_servings' Series
drinks.beer_servings
drinks['beer_servings']
# Calculate the average 'beer_servings' for the entire dataset
drinks.describe()                   # Mean is provided in the summary from describe()
drinks.beer_servings.mean()         # Alternatively, calculate the mean directly
# Print all columns, but only show rows where the country is in Europe
drinks[drinks.continent=='EU']
# Calculate the average 'beer_servings' for all of Europe (hint: use the .mean() function)
drinks[drinks.continent=='EU'].beer_servings.mean()
# Only show European countries with 'wine_servings' greater than 300
drinks[(drinks.continent=='EU') & (drinks.wine_servings > 300)]
# Determine which 10 countries have the highest 'total_litres_of_pure_alcohol'
drinks.sort_index(by='total_litres_of_pure_alcohol').tail(10)
# Determine which country has the highest value for 'beer_servings' (hint: use the .max() function)
drinks[drinks.beer_servings==drinks.beer_servings.max()].country
drinks[['country', 'beer_servings']].sort_index(by='beer_servings', ascending=False).head(1) # This is equivalent
# Count the number of occurrences of each 'continent' value and see if it looks correct
drinks.continent.value_counts()
# Determine which countries do not have continent designations
drinks[drinks.continent.isnull()].country
# Due to "na_filter = True" default within pd.read_csv()
help(pd.read_csv)
Explanation: Solutions
End of explanation
ufo.set_index('State', inplace=True)
ufo.index
ufo.index.is_unique
ufo.sort_index(inplace=True)
ufo.head(25)
Explanation: Indexing and Slicing Data
Create a new index
End of explanation
ufo.loc['FL',:]                                     # row with label FL`
ufo.loc[:'FL',:]                                    # rows with labels  through'FL'
ufo.loc['FL':'HI', 'City':'Shape']         # rows FL, columns 'City' through 'Shape Reported'
ufo.loc[:, 'City':'Shape']                 # all rows, columns 'City' through 'Shape Reported'
ufo.loc[['FL', 'TX'], ['City','Shape']]    # rows FL and TX, columns 'City' and 'Shape Reported'
Explanation: loc: filter rows by LABEL, and select columns by LABEL
End of explanation
ufo.iloc[0,:]                       # row with 0th position (first row)
ufo.iloc[0:3,:]                     # rows with positions 0 through 2 (not 3)
ufo.iloc[0:3, 0:3]                  # rows and columns with positions 0 through 2
ufo.iloc[:, 0:3]                    # all rows, columns with positions 0 through 2
ufo.iloc[[0,2], [0,1]]              # 1st and 3rd row, 1st and 2nd column
Explanation: iloc: filter rows by POSITION, and select columns by POSITION
End of explanation
ufo.set_index('City', inplace=True, append=True) # Adds to existing index
ufo.sort_index(inplace=True)
ufo.head(25)
ufo.loc[['ND', 'WY'],:] # Select all records from ND AND WY
ufo.loc['ND':'WY',:]    # Select all records from ND THROUGH WY
ufo.loc[('ND', 'Bismarck'),:]  # Select all records from Bismark, ND
ufo.loc[('ND', 'Bismarck'):('ND','Casselton'),:] # Select all records from Bismark, ND through Casselton, ND
ufo.reset_index(level='City', inplace=True) # Remove the City from the index
ufo.head()
ufo.reset_index(inplace=True)               # Remove all columns from the index
ufo.head()
Explanation: Add another level to the index
End of explanation
# Reset the index
ufo.dtypes
# Convert Time column to date-time format (defined in Pandas)
# Reference: https://docs.python.org/2/library/time.html#time.strftime
ufo['Time'] = pd.to_datetime(ufo['Time'], format="%m/%d/%Y %H:%M")
ufo.dtypes
# Compute date range
ufo.Time.min()
ufo.Time.max()
# Slice using time
ufo[ufo.Time > pd.datetime(1995, 1, 1)]  # Slice using the time
ufo[(ufo.Time > pd.datetime(1995, 1, 1)) & (ufo.State =='TX')]  # Works with other logical conditions, as expected
# Set the index to time
ufo.set_index('Time', inplace=True)
ufo.sort_index(inplace=True)
ufo.head()
# Access particular times/ranges
ufo.loc['1995',:]
ufo.loc['1995-01',:]
ufo.loc['1995-01-01',:]
# Access range of times/ranges
ufo.loc['1995':,:]
ufo.loc['1995':'1996',:]
ufo.loc['1995-12-01':'1996-01',:]
# Access elements of the timestamp
# Reference: http://pandas.pydata.org/pandas-docs/stable/timeseries.html#time-date-components
ufo.index.year
ufo.index.month
ufo.index.weekday
ufo.index.day
ufo.index.time
ufo.index.hour
# Create a new variable with time element
ufo['Year'] = ufo.index.year
ufo['Month'] = ufo.index.month
ufo['Day'] = ufo.index.day
ufo['Weekday'] = ufo.index.weekday
ufo['Hour'] = ufo.index.hour
Explanation: Analyzing Across Time
End of explanation
# For each year, calculate the count of sightings
ufo.groupby('Year').City.count()
# For each Shape, calculate the first sighting, last sighting, and range of sightings. 
ufo.groupby('Shape').Year.min()
ufo.groupby('Shape').Year.max()
# Specify the variable outside of the apply statement
ufo.groupby('Shape').Year.apply(lambda x: x.max())
# Specifiy the variable within the apply statement
ufo.groupby('Shape').apply(lambda x: x.Year.max() - x.Year.min())
# Specify a custom function to use in the apply statement
def get_max_year(df):
    try:
        return df.Year.max()
    except:
        return ''
ufo.groupby('Shape').apply(lambda x: get_max_year(x))
# Split/combine can occur on multiple columns at the same time
ufo.groupby(['Weekday','Hour']).City.count()
Explanation: Split-Apply-Combine
Drawing by Hadley Wickham
End of explanation
# Read in population data
pop = pd.read_csv('population.csv')
pop.head()
ufo.head()
# Merge the data together
ufo = pd.merge(ufo, pop, on='State', how = 'left')
# Specify keys if columns have different names
ufo = pd.merge(ufo, pop, left_on='State', right_on='State', how = 'left')
# Observe the new Population column
ufo.head()
# Check for values that didn't make it (length)
ufo.Population.isnull().sum()
# Check for values that didn't make it (values)
ufo[ufo.Population.isnull()]
# Change the records that didn't match up using np.where command
ufo['State'] = np.where(ufo['State'] == 'Fl', 'FL', ufo['State'])
# Alternatively, change the state using native python string functionality
ufo['State'] = ufo['State'].str.upper()
# Merge again, this time get all of the records
ufo = pd.merge(ufo, pop, on='State', how = 'left')
Explanation: Merging Data
End of explanation
ufo.to_csv('ufo_new.csv')               
ufo.to_csv('ufo_new.csv', index=False)  # Index is not included in the csv
Explanation: Writing Data
End of explanation
ufo.duplicated()                                # Series of logicals
ufo.duplicated().sum()                          # count of duplicates
ufo[ufo.duplicated(['State','Time'])]           # only show duplicates
ufo[ufo.duplicated()==False]                    # only show unique rows
ufo_unique = ufo[~ufo.duplicated()]             # only show unique rows
ufo.duplicated(['State','Time']).sum()          # columns for identifying duplicates
Explanation: Other Useful Features
Detect duplicate rows
End of explanation
ufo['Weekday'] = ufo.Weekday.map({  0:'Mon', 1:'Tue', 2:'Wed', 
                                    3:'Thu', 4:'Fri', 5:'Sat', 
                                    6:'Sun'})
Explanation: Map existing values to other values
End of explanation
ufo.groupby(['Weekday','Hour']).City.count()
ufo.groupby(['Weekday','Hour']).City.count().unstack(0) # Make first row level a column
ufo.groupby(['Weekday','Hour']).City.count().unstack(1) # Make second row level a column
# Note: .stack() transforms columns to rows
Explanation: Pivot rows to columns
End of explanation
idxs = np.random.rand(len(ufo)) < 0.66   # create a Series of booleans
train = ufo[idxs]                        # will contain about 66% of the rows
test = ufo[~idxs]                        # will contain the remaining rows
Explanation: Randomly sample a DataFrame
End of explanation
ufo.Shape.replace('DELTA', 'TRIANGLE')   # replace values in a Series
ufo.replace('PYRAMID', 'TRIANGLE')       # replace values throughout a DataFrame
Explanation: Replace all instances of a value
End of explanation
%matplotlib inline
# Plot the number of sightings over time
ufo.groupby('Year').City.count().plot(  kind='line', 
                                        color='r', 
                                        linewidth=2, 
                                        title='UFO Sightings by year')
# Plot the number of sightings over the day of week and time of day
ufo.groupby(['Weekday','Hour']).City.count().unstack(0).plot(   kind='line', 
                                                                linewidth=2,
                                                                title='UFO Sightings by Time of Day')
# Plot multiple plots on the same plot (plots neeed to be in column format)              
ufo_fourth = ufo[(ufo.Year.isin([2011, 2012, 2013, 2014])) & (ufo.Month == 7)]
ufo_fourth.groupby(['Year', 'Day']).City.count().unstack(0).plot(   kind = 'bar',
                                                                    subplots=True,
                                                                    figsize=(7,9))
Explanation: One more thing...
End of explanation
<END_TASK>