<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Intoduction to Pandas and Dataframes
<hr>
Venkat Malladi (Computational Biologist BICF)
Agenda
<hr>
Introduction to Pandas
DataSeries
Exercise 1
Exercise 2
Dataframe 
Exercise 3
Exercise 4
Exercise 5
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
1.1 What is Pandas?
<hr>
Description
Python library providing high-performance, easy-to-use structures and data analysis tools.
Suitable for tabular data with heterogeneously-typed colums, as in an SQL table or Excel spreadsheet
Provides data analysis feautures similar to
Step1:  1.4 Data Structures
Series
Dataframe
Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe 
Exercise 3
Exercise 4
Exercise 5
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
2.1 Series
<hr>
One dimensional array-like object
Contains array of data (of any NumPy data type) with an index that labels each element in the vector. 
Indexes can be
Step2:  What datatype is the counts object?
Step3:  2.3 Series - String indexes
<hr>
We can assign meaningful lables to the indexes when making the Series object by specfing an array of index
| Index  | Value  |
| ------ | ------ |
| CA     | 35     |
| TX     | 50     |
| OK     | 25     |
Make Series of count data with Gene Symbols
Step4:  2.4 Series - Dictionary
<hr>
Can be thought of as a dict
Can be constructed from a dict directly.
Construct second sample RNA-counts dict
Step5:  Make pandas Series from RNA-counts dict
Step6:  2.5.1 Series - Referencing Elements - Integer
<hr>
<div style="background-color
Step7:  Get the 2nd through 4th elements
Step8:  2.5.2 Series - Referencing Elements - String
<hr>
String Index
Get the counts for the Myc Gene
Step9:  Get the counts for FOXA1, GATA2 and BRCA2
Step10:  2.5.3 Series - Referencing Elements - array/index values
<hr>
Can get the array representation and index object of the Series via its values and index atrributes, respectively.
Get the values in the counts matrix
Step11:  Get the index of the rna_counts matrix
Step12:  2.5.4 Series - Referencing Elements - labels
<hr>
Can give both the array of values and the index meaningful labels themselves
Step13:  2.6 Series - array operations
<hr>
NumPy array operations can be applied to Series without losing the data structure
Use boolean array to filter Series
Select Genes that have greater than 20 counts
Step14:  Select genes that have greater than 20 counts
Step15:  2.7 Series - null values
<hr>
Many times Series have missing values that you need to identify and clean
Marvel Cinematic Universe
Make Movie DataFrame with missing values
Step16:  Find movies with no opening revenue
Step17:  Find movies with opening revenue
Step18:  Display only movie with no opening revenue
Step19:  2.8 Series - auto alignment
<hr>
Index labels are used to align (merge) data when used in operations with other Series objects
Step20:  Combine counts for 2 cells (rna_counts and rna_counts_cell2)
Step21:  Adding Series combined values with the same label in the resulting series
Contrast this with arrays, where arrays of the same length will combine values element-wise
Notice that the missing values. If one Series has a missing value you can't add it to the other value and a missing value results. 
<div style="background-color
Step22:  <div style="background-color
Step23:  Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Exercise 5
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
3.1 Dataframe
<hr>
A DataFrame is a tabular data structure, comprised of rows and columns like in a spreadsheet
Each column can be a different value type (numeric, string, boolean etc)
| Title  | Year   | Studio | Rating |
| 
Step24:  What datatype is df_mcu?
Step25:  3.3 Dataframe - specifying indices and columns
<hr>
Order of columns/rows can be specified using
Step26:  3.4 Dataframe - from nested dict of dicts
<hr>
Outer dict keys as columns and inner dict keys as row indices
Make Dataframe of population of states
Step27:  3.5 Dataframe - number of rows and columns
<hr>
Get the number of rows in a Dataframe
Step28:  Get the (rows, cols) of the Dataframe
Step29:  3.6 Dataframe - index, columns and values
<hr>
Get the column headers
Step30:  Get the row index values
Step31:  Get values of the Dataframe only
Step32:  3.7 Dataframe - Selecting Columns and Rows
<hr>
There are three basic ways to access the data in the Dataframe
Step33:  Select values in a list of columns
Step34:  Use slice to get the first n rows (NumPy style indexing)
Step35:  Can combine slice and column selection to select the first n rows
Step36:  Order of column and slice doesn't matter
Step37:  3.7.2 Dataframe - Selecting Columns and Rows - Integer based selection
<hr>
iloc is primarily an integer position based (from 0 to length-1 of the axis), when we include the starting from the end indexing, but may also be used with a boolean array.
Allowed inputs are
Step38:  A list or array of integers
Step39:  A slice object with ints
Step40:  3.7.3 Dataframe - Selecting Columns and Rows - Integer based selection
<hr>
loc is primarily label based, but may also be used with a boolean array.
Allowed inputs are
Step41:  A list or array of labels
Step42:  A slice object with labels
<div style="background-color
Step43:  3.7.4 Dataframe - Selecting Columns and Rows - Data Filtering
<hr>
Data filtering using boolean
Filter and select on single condition
Step44:  Filter and select on multiple conidtions
Step45:  3.8 Dataframe - Adding and Deleting data
<hr>
Add a new column
Step46:  Add a new row
Step47:  Drop an existing column
Step48:  Drop an existing row
Step49:  Columns, Rows or individual elements can be modified similarly using loc or iloc.
<div style="background-color
Step50:  <div style="background-color
Step51:  <div style="background-color
Step52:  Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
4.1 Import and Store Data
<hr>
The first step in any  problem is identifying what format your data is in, and then loading it into whatever framework you're using. Common formats are
Step53:  Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
5.1 Summarizing and Computing Descriptive Statistics
<hr>
Pandas as a lot of  built-in essential functionality common to the pandas data structures to help explore the data.
5.2 Summarizing and Computing Descriptive Statistics - Head and Tail
<hr>
To view a small sample of a Series or DataFrame object, use
Step54:  5.2 Summarizing and Computing Descriptive Statistics - Sorting
<hr>
To sort data for exploring the data use
Step55:  Sort Dataframe by columns in descending order
Step56:  5.3 Summarizing and Computing Descriptive Statistics - Descriptive statistics
<hr>
Built in functions to calculate the values over row or columns
describe()
Step57:  mean()
Step58:  var()
Step59:  5.4 Summarizing and Computing Descriptive Statistics - Missing Data
<hr>
Data comes in many shapes and forms and Pandas is very flexible in handling missing data
Step60:  - Fill Nan with default value
Step61:  - Use inplace to modify the dataframe instead of retunring a new object
Step62:  <div style="background-color
Step63:  <div style="background-color
Step64:  Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
6.1 Grouped and apply
<hr>
The Grouped functionality is referring to a process involving one or more of the following steps
Step65:  6.3 Grouped and apply - Apply
<hr>
Apply the same function to every column or row
Step66:  - Apply a new function that subtract max from 2 times min in every column
Step67:  <div style="background-color
Step68:  <div style="background-color
Step69:  Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
7.1 Data Transformation and Normalization
<hr>
For Machine Learning (ML) and other data analysis it is important to
Step70:  Plot the Number of Medals in barchart by country using
Step71:  Other useful plots
Step72:  <div style="background-color
Step73:  <div style="background-color
<ASSISTANT_TASK:>
Python Code:
# Import Pandas and Numpy
import pandas as pd
import numpy as np
Explanation: Intoduction to Pandas and Dataframes
<hr>
Venkat Malladi (Computational Biologist BICF)
Agenda
<hr>
Introduction to Pandas
DataSeries
Exercise 1
Exercise 2
Dataframe 
Exercise 3
Exercise 4
Exercise 5
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
1.1 What is Pandas?
<hr>
Description
Python library providing high-performance, easy-to-use structures and data analysis tools.
Suitable for tabular data with heterogeneously-typed colums, as in an SQL table or Excel spreadsheet
Provides data analysis feautures similar to: R, MATLAB, SAS
Based on NumPy
1.2 Key Features
<hr>
The library is oriented towards table-like data structures that can be manipulated by a collection of methods:
Easy handling of missing data
Label-based slicing, indexing and subsetting of large data sets
Powerful and flexible group by functionality to perform split-apply-combine operations on data sets
Read/Write data from/to Excel, CSV, SQL databases, JSON
1.3 Import Pandas
<hr>
Before we explore the pandas package, let's import pandas. 
The convention is to use pd to refere to pandas when importing the package.
End of explanation
# Make Series of count data and visaulize series
counts = pd.Series([223, 43, 53, 24, 43])
counts
Explanation: 1.4 Data Structures
Series
Dataframe
Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe 
Exercise 3
Exercise 4
Exercise 5
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
2.1 Series
<hr>
One dimensional array-like object
Contains array of data (of any NumPy data type) with an index that labels each element in the vector. 
Indexes can be:
integers
strings
other data types
<div style="background-color: #9999ff; padding: 10px;">NOTE: Not Numpy arrays but adds functionality to a Numpy array </div>
2.2 Series - Integer indexes
<hr>
If an index is not specified, a default sequence of integers is assigned as index.
| Index  | Value  |
| ------ | ------ |
| 0      | 35     |
| 1      | 50     |
| 2      | 25     |
Make a Series of count data
End of explanation
# What datatype is the counts object?
type(counts)
Explanation: What datatype is the counts object?
End of explanation
# Make Series of count data with Gene Symbols
rna_counts = pd.Series([50, 10, 12, 29, 4], index=['BRCA2', 'GATA2', 'Myc', 'FOXA1', 'ERCC2'])
rna_counts
Explanation: 2.3 Series - String indexes
<hr>
We can assign meaningful lables to the indexes when making the Series object by specfing an array of index
| Index  | Value  |
| ------ | ------ |
| CA     | 35     |
| TX     | 50     |
| OK     | 25     |
Make Series of count data with Gene Symbols
End of explanation
# Construct second sample RNA-counts dict
cell2_counts = {'BRCA2':5, 'GATA2':20, 'Myc':45, 'FOXA1':10, 'ERCC2':0, 'BRCA1': 20}
cell2_counts
Explanation: 2.4 Series - Dictionary
<hr>
Can be thought of as a dict
Can be constructed from a dict directly.
Construct second sample RNA-counts dict
End of explanation
# Make pandas Series from RNA-counts dict
rna_counts_cell2 = pd.Series(cell2_counts)
rna_counts_cell2
Explanation: Make pandas Series from RNA-counts dict
End of explanation
# Access the 1st element of counts data
counts[0]
Explanation: 2.5.1 Series - Referencing Elements - Integer
<hr>
<div style="background-color: #9999ff; padding: 10px;">NOTE: We can access the values like an array text </div>
Integer Index
Access the 1st element of counts data
End of explanation
# Get the 2nd through 4th elements
counts[1:4]
Explanation: Get the 2nd through 4th elements
End of explanation
# Get the counts for Myc Gene
rna_counts['Myc']
Explanation: 2.5.2 Series - Referencing Elements - String
<hr>
String Index
Get the counts for the Myc Gene
End of explanation
# Get the Counts for FOXA1, GATA2 and BRCA2
rna_counts[['FOXA1', 'GATA2', 'BRCA2']]
Explanation: Get the counts for FOXA1, GATA2 and BRCA2
End of explanation
# Get the values in the counts matrix
counts.values
Explanation: 2.5.3 Series - Referencing Elements - array/index values
<hr>
Can get the array representation and index object of the Series via its values and index atrributes, respectively.
Get the values in the counts matrix
End of explanation
# Get the index of the rna_counts matrix
rna_counts.index
Explanation: Get the index of the rna_counts matrix
End of explanation
rna_counts.name = 'RNA Counts'
rna_counts.index.name = 'Symbol'
rna_counts
Explanation: 2.5.4 Series - Referencing Elements - labels
<hr>
Can give both the array of values and the index meaningful labels themselves
End of explanation
# Select Genes that have greater than 20 counts
rna_counts > 20
Explanation: 2.6 Series - array operations
<hr>
NumPy array operations can be applied to Series without losing the data structure
Use boolean array to filter Series
Select Genes that have greater than 20 counts
End of explanation
# Select genes that have greater than 20 counts
rna_counts[rna_counts > 20]
Explanation: Select genes that have greater than 20 counts
End of explanation
# Make Movie Database with missing values
mcu_opening = {'Black Panther': 202003951, 'Thor: Ragnarok': 122744989, 'Spider-Man: Homecoming': 117027503,
              'Guardians of the Galaxy Vol. 2': 146510104, 'Doctor Strange': 85058311, 
               'Captain America: Civil War': 179139142}
mcu_movies = ['Ant-Man and the Wasp', 'Avengers: Infinity War', 'Black Panther', 'Thor: Ragnarok', 
              'Spider-Man: Homecoming', 'Guardians of the Galaxy Vol. 2', 'Doctor Strange', 'Captain America: Civil War']
mcu_series = pd.Series(mcu_opening, index=mcu_movies)
mcu_series
Explanation: 2.7 Series - null values
<hr>
Many times Series have missing values that you need to identify and clean
Marvel Cinematic Universe
Make Movie DataFrame with missing values
End of explanation
# Find movies with no opening revenue
pd.isnull(mcu_series)
# Good opportunity to use Boolean filter get index and only movie names
mcu_series[pd.isnull(mcu_series)].index.values
Explanation: Find movies with no opening revenue
End of explanation
# Find movies with opening revenue
pd.notnull(mcu_series)
Explanation: Find movies with opening revenue
End of explanation
# Display only movies with no opening revenue
mcu_series[pd.isnull(mcu_series)].index.values
Explanation: Display only movie with no opening revenue
End of explanation
rna_counts
rna_counts_cell2
Explanation: 2.8 Series - auto alignment
<hr>
Index labels are used to align (merge) data when used in operations with other Series objects
End of explanation
# Combine counts for 2 cells 
rna_counts + rna_counts_cell2
Explanation: Combine counts for 2 cells (rna_counts and rna_counts_cell2)
End of explanation
# Sample Python data and labels:
students = ['Anastasia', 'Dima', 'Katherine', 'James', 'Emily', 'Michael', 'Matthew', 'Laura', 'Kevin', 'Jonas']
test_scores = [12.5, 9, 16.5, np.nan, 9, 20, 14.5, np.nan, 8, 19]
s_scores = pd.Series(test_scores, index=students)
# Which Students have scores greater than 15?
s_scores[s_scores > 15].index.values
# Bonus: How would you use get the Students scores greater than 15 and less than 20?
s_scores[(s_scores > 15) & (s_scores < 20)]
Explanation: Adding Series combined values with the same label in the resulting series
Contrast this with arrays, where arrays of the same length will combine values element-wise
Notice that the missing values. If one Series has a missing value you can't add it to the other value and a missing value results. 
<div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 1</div>
<hr>
Create a Series from a specified list data which has the index labels as student names and test values.
Which Students have scores greater than 15?
Bonus: How would you use get the Students scores greater than 15 and less than 20? 
<div style="background-color: #9999ff; padding: 10px;">Hint: Will use the bitwise opperator & </div>
| Names  | Value  |
| ------ | ------ |
| Anastasia     | 12.5     |
| Dima     | 9     |
| Katherine     | 16.5     |
| James     | NaN     |
| Emily     | 9     |
| Michael     | 20     |
| Matthew     | 14.5     |
| Laura     | NaN     |
| Kevin     | 8     |
| Jonas     | 19     |
End of explanation
# What is the mean, median and max test scores?
s_scores.mean()
s_scores.median()
s_scores.max()
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 2</div>
What is the mean, median and max test scores?
End of explanation
# Make Dataframe of Marvel data
mcu_data = {'Title': ['Ant-Man and the Wasp', 'Avengers: Infinity War', 'Black Panther', 'Thor: Ragnarok', 
              'Spider-Man: Homecoming', 'Guardians of the Galaxy Vol. 2'],
            'Year':[2018, 2018, 2018, 2017, 2017, 2017],
            'Studio':['Beuna Vista', 'Beuna Vista', 'Beuna Vista', 'Beuna Vista', 'Sony', 'Beuna Vista'],
            'Rating': [np.nan, np.nan, 0.96, 0.92, 0.92, 0.83]}
df_mcu = pd.DataFrame(mcu_data)
df_mcu
Explanation: Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Exercise 5
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
3.1 Dataframe
<hr>
A DataFrame is a tabular data structure, comprised of rows and columns like in a spreadsheet
Each column can be a different value type (numeric, string, boolean etc)
| Title  | Year   | Studio | Rating |
| :------: | :------: | :------: | :------: |
| Ant-Man and the Wasp  | 2018| Beuna Vista | NaN|
| Avengers: Infinity War   | 2018    | Beuna Vista | NaN|
| Black Panther     | 2018     | Beuna Vista | 0.96 |
| Thor: Ragnarok | 2017 | Beuna Vista | 0.92|
| Spider-Man: Homecoming| 2017 | Sony | 0.92|
| Guardians of the Galaxy Vol. 2| 2017| Beuna Vista | 0.83 |
3.2 Dataframe - from dict of lists
<hr>
dict keys: columns
dict values (arrays): rows
Make Dataframe of Marvel data
End of explanation
# What datatype is df_mcu?
type(df_mcu)
Explanation: What datatype is df_mcu?
End of explanation
# Assign column order and index based on Marvel Cinemetic Universe Movie Number 
mcu_index = ['mcu_20','mcu_19', 'mcu_18', 'mcu_17', 'mcu_16', 'mcu_15']
mcu_columns = ['Title', 'Year', 'Studio', 'Rating']
df_mcu = pd.DataFrame(mcu_data, columns = mcu_columns, index = mcu_index)
df_mcu
Explanation: 3.3 Dataframe - specifying indices and columns
<hr>
Order of columns/rows can be specified using:
columns array
index array
End of explanation
# Make Dataframe of population
pop = {'Nevada': {2001: 2.9, 2002: 2.9}, 'Ohio': {2002: 3.6, 2001: 1.7, 2000: 1.5}}
df_pop = pd.DataFrame(pop)
df_pop
Explanation: 3.4 Dataframe - from nested dict of dicts
<hr>
Outer dict keys as columns and inner dict keys as row indices
Make Dataframe of population of states
End of explanation
# Get the number of rows in a Dataframe
len(df_mcu)
Explanation: 3.5 Dataframe - number of rows and columns
<hr>
Get the number of rows in a Dataframe
End of explanation
# Get the (rows, cols) of the Dataframe
df_mcu.shape
Explanation: Get the (rows, cols) of the Dataframe
End of explanation
# Get the column headers
df_mcu.columns 
Explanation: 3.6 Dataframe - index, columns and values
<hr>
Get the column headers
End of explanation
# Get the row index values
df_mcu.index  
Explanation: Get the row index values
End of explanation
# Get values of the Dataframe only
df_mcu.values  
Explanation: Get values of the Dataframe only
End of explanation
# Select values in a single column
df_mcu['Title']
Explanation: 3.7 Dataframe - Selecting Columns and Rows
<hr>
There are three basic ways to access the data in the Dataframe:
Quick Access: DataFrame[] 
Integer position based selection method: DataFrame.iloc[row, col] 
Label based selection method: DataFrame.loc[row, col] 
3.7.1 Dataframe - Selecting Columns and Rows - Quick Access
<hr>
Select values in a single column
End of explanation
# Select values in a list of columns
df_mcu[['Title', 'Rating']]
Explanation: Select values in a list of columns
End of explanation
# Use slice to get the first n rows (NumPy style indexing)
df_mcu[:2]
Explanation: Use slice to get the first n rows (NumPy style indexing)
End of explanation
# Can combine slice and column selection to select the first n rows
df_mcu['Title'][:2]
Explanation: Can combine slice and column selection to select the first n rows
End of explanation
df_mcu[:4]['Year']
Explanation: Order of column and slice doesn't matter
End of explanation
# Return values in the first row
df_mcu.iloc[0]
# Return values in the first row and second column
df_mcu.iloc[0,1]
Explanation: 3.7.2 Dataframe - Selecting Columns and Rows - Integer based selection
<hr>
iloc is primarily an integer position based (from 0 to length-1 of the axis), when we include the starting from the end indexing, but may also be used with a boolean array.
Allowed inputs are:
Integer
End of explanation
# Return values in the 3,5 and 6th rows 
df_mcu.iloc[[2,4,5]]
Explanation: A list or array of integers
End of explanation
# Return values in the first row and columns 2 and 3
df_mcu.iloc[:2, 1:3]
Explanation: A slice object with ints
End of explanation
# Select all values of the 20th Movie
df_mcu.loc['mcu_20']
Explanation: 3.7.3 Dataframe - Selecting Columns and Rows - Integer based selection
<hr>
loc is primarily label based, but may also be used with a boolean array.
Allowed inputs are:
A single label
End of explanation
# Select all values of the 20th, 17th and 15th movie, which uses row index values, 
# Not to be confused with df_mcu[['Title', 'Rating']] which uses column headers
df_mcu.loc[['mcu_20', 'mcu_17', 'mcu_15']]
Explanation: A list or array of labels
End of explanation
# Select the Year and Rating
df_mcu.loc[:, ['Year', 'Rating']]
Explanation: A slice object with labels
<div style="background-color: #9999ff; padding: 10px;"> NOTE: Unlike numeric index python slices, both the start and the stop are included!</div>
End of explanation
# Filter for Rating < .95
df_mcu.loc[df_mcu['Rating'] < .95, :]
Explanation: 3.7.4 Dataframe - Selecting Columns and Rows - Data Filtering
<hr>
Data filtering using boolean
Filter and select on single condition
End of explanation
# Filter for Rating < .95 and Sudio is Sony
# Reuse the bitwise comparator seen earlier but with OR instead of AND.
df_mcu.loc[(df_mcu['Rating'] < .95) | (df_mcu['Studio'] == 'Sony'), :]
Explanation: Filter and select on multiple conidtions
End of explanation
# Add new predicted rating to Dataframe
df_mcu['Predicted Rating'] = np.random.random(len(df_mcu))
df_mcu
Explanation: 3.8 Dataframe - Adding and Deleting data
<hr>
Add a new column
End of explanation
# Add a new row for a new movie 
new_row = pd.Series(['Captain Marvel', 2019, 'BeunaVista',  np.nan, np.random.random(1)[0]], index=df_mcu.columns, name= 'mcu_21' )
df_mcu.append(new_row)
Explanation: Add a new row
End of explanation
# Drop the Rating Column
df_mcu.drop('Rating', axis=1)
Explanation: Drop an existing column
End of explanation
# Drop the 17 and 19th movies
df_mcu.drop(['mcu_15', 'mcu_17'])
Explanation: Drop an existing row
End of explanation
# Sample Python data:
exam_data = {'Names': ['Anastasia', 'Dima', 'Katherine', 'James', 'Emily', 'Michael', 'Matthew', 'Laura', 'Kevin', 'Jonas'], 
             'Scores': [12.5, 9, 16.5, np.nan, 9, 20, 14.5, np.nan, 8, 19], 
             'Attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1], 
             'Qualify': ['yes', 'no', 'yes', 'no', 'no', 'yes', 'yes', 'no', 'no', 'yes']} 
student_data = pd.DataFrame(exam_data)
student_data
# Select the students the qualify based on scores using the Qualify column 'yes'
student_data[student_data['Qualify'] == 'yes']
Explanation: Columns, Rows or individual elements can be modified similarly using loc or iloc.
<div style="background-color: #9999ff; padding: 10px;"> NOTE: Return new object Dataframe, withouth changing the orignal dataframe (append, drop).</div>
<div style="background-color:yellow; padding: 10px"><h3><span></span>Exericse 3</div>
<hr>
Create a Dataframe from a specified dictionary data of 
Which Students have scorescore is between 15 and 20?
| Names  | Scores  | Attempts | Qualify | 
| ------ | ------ | ------ | ------ |
| Anastasia     | 12.5     | 1 | 'yes' |
| Dima     | 9     | 3 | 'no' |
| Katherine     | 16.5     | 2| 'yes' |
| James     | NaN     | 3| 'no' |
| Emily     | 9     | 2| 'no' |
| Michael     | 20     | 3| 'yes' |
| Matthew     | 14.5     | 1| 'yes' |
| Laura     | NaN     | 1| 'no' |
| Kevin     | 8     | 2| 'no' |
| Jonas     | 19     | 1| 'yes' |
End of explanation
# Add a new column of, Grade Level, to indicate which Grade in High school the students are in?
grade_level = ['9th', '9th', '10th', '11th', '12th', '9th', '10th', '11th', '12th', '11th']
student_data['Grade'] = grade_level
student_data
student_data.columns
# Add a new student named, Jack? (Hint: Need to use ignore_index=True)
jack = pd.Series([2, 'Jack', 'yes' , 17, '9th'],index=student_data.columns )
student_data.append(jack, ignore_index=True)
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 4</div>
<hr>
Add a new column of, Grade Level, to indicate which Grade in High school the students are in?
Add a new student named, Jack? (Hint: Need to use ignore_index=True)
End of explanation
# Add a new column of Pass that is either 0 or 1 based on the column Qualify. (Hint: use numpy.where)
student_data['Pass'] = np.where(student_data['Qualify'] == 'yes', 1, 0)
student_data
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 5</div>
<hr>
Add a new column of Pass that is either 0 or 1 based on the column Qualify. (Hint: use numpy.where)
- 'yes': 1
- 'no': 0
End of explanation
# Read in Winter Olympic Medal Winners 
winter_olympics = pd.read_csv('data/winter_olympics.csv')
winter_olympics.head()
Explanation: Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
4.1 Import and Store Data
<hr>
The first step in any  problem is identifying what format your data is in, and then loading it into whatever framework you're using. Common formats are:
- Text Files: Text files with a common delimeter to seperate values (e.g. CSV uses ,)
- JSON(JavaScript Object Notation): standard formats for sending data by HTTP requests
- Web Page: XML and HTML
- Binary: "pickle” format and HDF5
- Database: MySQL, PostgreSQL 
<div style="background-color: #9999ff; padding: 10px;"> NOTE: Most common is CSV or Text files with different delimeters. </div>
4.2 Import and Store Data - Text Files
<hr>
Reading
read_csv: Use comma seperated (,) deliminator to read file
read_table: Use tab (\t)  deliminator to read file
Writting
write_csv: Use comma seperated (,) deliminator to write file
write_table: Use tab (\t)  deliminator to write file
Read in Winter Olympic Medal Winners form Kaggle
End of explanation
# Get the First 3 lines of a Dataframe
winter_olympics.head(3)
Explanation: Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
5.1 Summarizing and Computing Descriptive Statistics
<hr>
Pandas as a lot of  built-in essential functionality common to the pandas data structures to help explore the data.
5.2 Summarizing and Computing Descriptive Statistics - Head and Tail
<hr>
To view a small sample of a Series or DataFrame object, use:
head()
tail()
<div style="background-color: #9999ff; padding: 10px;"> NOTE: The default number of elements to display is five, but you may pass a custom number. </div>
Get the First 3 lines of Dataframe
End of explanation
# Sort Dataframe by rows in ascending order
df_mcu.sort_index(axis=0, ascending=True)
Explanation: 5.2 Summarizing and Computing Descriptive Statistics - Sorting
<hr>
To sort data for exploring the data use:
sort_index(): object by labels (along an axis)
sort_values(): by the values along either axis
<div style="background-color: #9999ff; padding: 10px;"> NOTE: axis: 0 or ‘index’, 1 or ‘columns’ and default is 0 </div>
Sort Dataframe by rows in ascending order
End of explanation
# Sort Dataframe by column in descending order
df_mcu.sort_values(by=['Rating', 'Predicted Rating'], ascending=False)
Explanation: Sort Dataframe by columns in descending order
End of explanation
# Summary Statistics for the Dataframe
df_mcu.describe()
Explanation: 5.3 Summarizing and Computing Descriptive Statistics - Descriptive statistics
<hr>
Built in functions to calculate the values over row or columns
describe(): return summary statistics of each column
        - for numeric data: mean, std, max, min, 25%, 50%, 75%, etc.
        - For non-numeric data: count, uniq, most-frequent item, etc
End of explanation
# Mean of the Rating and Predicted Rating Columns
df_mcu.loc[:,['Rating', 'Predicted Rating']].mean()
Explanation: mean()
End of explanation
# Get the variance of the Rating column
df_mcu.loc[:,['Rating']].var()
Explanation: var()
End of explanation
# Drop rows with NaN values
df_mcu.dropna()
Explanation: 5.4 Summarizing and Computing Descriptive Statistics - Missing Data
<hr>
Data comes in many shapes and forms and Pandas is very flexible in handling missing data:
While Nan is the default missing value marker
However, Python None will arise and we wish to also consider that “missing” or “not available” or “NA”
Drop rows with Nan values
End of explanation
# File Nan in Dataframe with default value
df_mcu.fillna(0)
Explanation: - Fill Nan with default value
End of explanation
# File Nan in Dataframe with default value in place
df_mcu.fillna(0, inplace=True)
df_mcu
Explanation: - Use inplace to modify the dataframe instead of retunring a new object
End of explanation
# What is the median score of the students on the exam?
student_data['Scores'].median()
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 6</div>
<hr>
What is the median score of the students on the exam?
End of explanation
# Deduct 4 points from everyone that attempted the exam 2 or more times. Replace all Nan scores with 0. (Passing is 12 points)
student_data.loc[student_data['Attempts'] >= 2, 'Scores'] -= 4
student_data
# Compute the mean. Would the class as a whole pass the test?
student_data['Scores'].mean()
# Are there any students that will fail now?
student_data[(student_data['Qualify'] == 'yes')]
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 7</div>
<hr>
Deduct 4 points from everyone that attempted the exam 2 or more times. Replace all Nan scores with 0.
(Passing is 12 points)
Compute the mean. Would the class as a whole pass the test?
Are there any students that will fail now?
End of explanation
# Groupby year of release and get mean Rating
df_mcu.groupby('Year').mean()
Explanation: Agenda
<hr>
Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
6.1 Grouped and apply
<hr>
The Grouped functionality is referring to a process involving one or more of the following steps:
- Splitting the data into groups based on some criteria
- Applying a function to each group independently
6.2 Grouped and apply - Splitting
<hr>
Pandas objects can be split on any of their axes. 
- 'grouping' is to provided as a mapping of labels to group names in a Grouped Objec
- groupby()
End of explanation
# Apply square to every value in a dataframe
test_data = np.arange(9).reshape(3,-1)
df_test = pd.DataFrame(test_data, index=['r1', 'r2', 'r3'], columns=['c1', 'c2', 'c3'])
df_test
df_test.applymap(np.square)
Explanation: 6.3 Grouped and apply - Apply
<hr>
Apply the same function to every column or row:
- applymap: Apply same function across every cell
- apply: Apply same function to every column (default) or row
    - Apply square to every value in a dataframe
End of explanation
# Define max minus min function
def max_minus_min(x): 
    return max(x)-(2*min(x))
# Apply a new function that subtract max from min in every column
df_test.apply(max_minus_min)
Explanation: - Apply a new function that subtract max from 2 times min in every column
End of explanation
# Group students by attempts and find the average score?
student_data.groupby('Attempts')['Scores'].mean()
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 8</div>
<hr>
Group students by attempts and find the average score?
End of explanation
# Group students by their pass result and report the variance in scores?
student_data.groupby('Pass')['Scores'].var()
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 9</div>
<hr>
Group students by their pass result and report the variance in scores?
End of explanation
# Import  maplotlib and setup to display plots notebook
import matplotlib.pyplot as plt
%matplotlib inline  
Explanation: Introduction to Pandas
Series
Exercise 1
Exercise 2
Dataframe
Exercise 3
Exercise 4
Import and Store Data
Summarizing and Computing Descriptive Statistics
Exercise 6
Exercise 7
Grouped and apply
Exercise 8
Exercise 9
Data Transformation and Normalization
Exercise 10
Exercise 11
Exercise 12
7.1 Data Transformation and Normalization
<hr>
For Machine Learning (ML) and other data analysis it is important to:
- Explore your data
- Standardize data (Transform/Normalize) to obtain so different columns became comparable / compatible
7.1 Data Transformation and Normalization - Data Exploration
<hr>
Pandas Dataframes have a built in plot functionality:
- Dataframe.plot()
<div style="background-color: #9999ff; padding: 10px;"> NOTE: We will go into more plotting libraries later in the course. </div>
End of explanation
# Plot the Number of Medals in barchart by country using: plot.bar()
winter_olympics.groupby(['Country'])['Medal'].count().plot.bar()
Explanation: Plot the Number of Medals in barchart by country using: plot.bar()
End of explanation
# In the Winter olympics which country has the most Biathlon medals?
winter_sport_medal = winter_olympics.groupby(['Country','Sport'])['Medal'].count()
winter_sport_medal.head(10)
winter_sport_medal.loc[:,'Biathlon',:].sort_values(ascending=False).head()
# In the Winter olympics which country has the most Skiing medals? 
winter_sport_medal.loc[:,'Skiing',:].sort_values(ascending=False).head()
# And in which event do they have the most Gold medals?
winter_sport_disc_medal = winter_olympics.groupby(['Country','Sport', 'Discipline', 'Medal'])['Medal'].count()
winter_sport_disc_medal.head()
winter_sport_disc_medal['NOR','Skiing'].sort_values(ascending=False)
Explanation: Other useful plots:
hist: plot.hist()
scatter: plot.scatter()
boxplot: plot.box()
7.2 Data Transformation and Normalization - Normalization
<hr>
Why Normalize (re-scale)?
- Transform data to obtain a certain distribution e.g. from lognormal to normal
- Normalize data so different columns became comparable / compatible
Typical normalization approach:
- Z-score transformation
- Scale to between 0 and 1
- Trimmed mean normalization
- Vector length transformation
- Quantilenorm
Further Resources:
- scikit-learn
Further Resources
<hr>
Resources:
- Pandas 10 min
- Pandas Tutorals
- Pandas Cookbook
<div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 10</div>
<hr>
In the Winter olympics which country has the most Biathlon medals?
In the Winter olympics which country has the most Skiing medals? And in which event do they have the most Gold medals?
End of explanation
# Import the Summer Olympic dataset located in ('data/summer_olypmics.csv')
summer_olympics = pd.read_csv('data/summer_olympics.csv')
#Which Olympian has the most medals?
summer_olympics.groupby(['Athlete'])['Medal'].count().sort_values(ascending=False).head()
# Which Olympian has the most Gold medals and for which Country?
summer_olympics.groupby(['Athlete','Medal','Country'])['Medal'].count().sort_values(ascending=False).head()
# Which Olympian has the most Gold medals and for which Sport?
summer_olympics.groupby(['Athlete','Medal','Discipline'])['Medal'].count().sort_values(ascending=False).head()
# Which rows have no values and why?
summer_olympics[pd.isnull(summer_olympics).any(axis=1)]
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 11</div>
<hr>
Import the Summer Olympic dataset located in ('data/summer_olypmics.csv')
Which Olympian has the most medals?
Which Olympian has the most Gold medals and for which Country and Sport?
Which rows have no values and why?
End of explanation
# Import the example RNA-seq Count Data in ('data/RNAseq_count_table.txt')
rna_counts = pd.read_csv('data/RNAseq_count_table.txt', sep = '\t', index_col = 0)
rna_counts.head()
# Calculate CPM for each Sample.
rna_cpm = rna_counts.divide(rna_counts.sum(axis=0)).multiply(1000000)
rna_cpm.head()
# Which Gene has the highest average CPM?
rna_cpm.mean(axis=1).sort_values(ascending=False).head()
# What is the Correlation between SRR1550986 SRR1550987?
rna_cpm.corr()
Explanation: <div style="background-color:yellow; padding: 10px"><h3><span></span>Exercise 12</div>
<hr>
Import the example RNA-seq Count Data in ('../data/RNAseq_count_table.txt')
Calculate CPM for each Sample. (CPM, Counts Per Million)
Formula for CPM = readsMappedToGene x 1/totalNumReads x 10^6
totalNumReads - total number of mapped reads of a sample
readsMappedToGene - number of reads mapped to a selected gene
Which Gene has the highest average CPM?
What is the Correlation between SRR1550986 SRR1550987?
End of explanation
<END_TASK>