Intoduction to Pandas and Dataframes

Venkat Malladi (Computational Biologist BICF)

Agenda

1. Introduction to Pandas
2. DataSeries
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
   • Exercise 5
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

1.1 What is Pandas?

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

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

Before we explore the pandas package, let's import pandas.

The convention is to use pd to refere to pandas when importing the package.

1.4 Data Structures

• Series
• Dataframe

Agenda

1. Introduction to Pandas
2. Series
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
   • Exercise 5
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

2.1 Series

• 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

2.2 Series - Integer indexes

• If an index is not specified, a default sequence of integers is assigned as index.

• Make a Series of count data

• What datatype is the counts object?

2.3 Series - String indexes

• We can assign meaningful lables to the indexes when making the Series object by specfing an array of index

• Make Series of count data with Gene Symbols

2.4 Series - Dictionary

• Can be thought of as a dict
• Can be constructed from a dict directly.

• Construct second sample RNA-counts dict

• Make pandas Series from RNA-counts dict

2.5.1 Series - Referencing Elements - Integer

• Integer Index

• Access the 1st element of counts data

• Get the 2nd through 4th elements

2.5.2 Series - Referencing Elements - String

• String Index

• Get the counts for the Myc Gene

• Get the counts for FOXA1, GATA2 and BRCA2

2.5.3 Series - Referencing Elements - array/index values

• 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

• Get the index of the rna_counts matrix

2.5.4 Series - Referencing Elements - labels

• Can give both the array of values and the index meaningful labels themselves

2.6 Series - array operations

• 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

• Select genes that have greater than 20 counts

2.7 Series - null values

• Many times Series have missing values that you need to identify and clean

Marvel Cinematic Universe

• Make Movie DataFrame with missing values

• Find movies with no opening revenue

• Find movies with opening revenue

• Display only movie with no opening revenue

2.8 Series - auto alignment

• Index labels are used to align (merge) data when used in operations with other Series objects

• Combine counts for 2 cells (rna_counts and rna_counts_cell2)

• 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.

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?

What is the mean, median and max test scores?

Agenda

1. Introduction to Pandas
2. Series
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
   • Exercise 5
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

3.1 Dataframe

• 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)

3.2 Dataframe - from dict of lists

• dict keys: columns
• dict values (arrays): rows

• Make Dataframe of Marvel data

• What datatype is df_mcu?

3.3 Dataframe - specifying indices and columns

• Order of columns/rows can be specified using:
  • columns array
  • index array

3.4 Dataframe - from nested dict of dicts

• Outer dict keys as columns and inner dict keys as row indices

• Make Dataframe of population of states

3.5 Dataframe - number of rows and columns

• Get the number of rows in a Dataframe

• Get the (rows, cols) of the Dataframe

3.6 Dataframe - index, columns and values

• Get the column headers

• Get the row index values

• Get values of the Dataframe only

3.7 Dataframe - Selecting Columns and Rows

• There are three basic ways to access the data in the Dataframe:
  1. Quick Access: DataFrame[]
  2. Integer position based selection method: DataFrame.iloc[row, col]
  3. Label based selection method: DataFrame.loc[row, col]

3.7.1 Dataframe - Selecting Columns and Rows - Quick Access

• Select values in a single column

• Select values in a list of columns

• Use slice to get the first n rows (NumPy style indexing)

• Can combine slice and column selection to select the first n rows

• Order of column and slice doesn't matter

3.7.2 Dataframe - Selecting Columns and Rows - Integer based selection

• 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

• A list or array of integers

• A slice object with ints

3.7.3 Dataframe - Selecting Columns and Rows - Integer based selection

• loc is primarily label based, but may also be used with a boolean array.

• Allowed inputs are:

• A single label

• A list or array of labels

• A slice object with labels

3.7.4 Dataframe - Selecting Columns and Rows - Data Filtering

• Data filtering using boolean

• Filter and select on single condition

• Filter and select on multiple conidtions

3.8 Dataframe - Adding and Deleting data

• Add a new column

• Add a new row

• Drop an existing column

• Drop an existing row

• Columns, Rows or individual elements can be modified similarly using loc or iloc.

Create a Dataframe from a specified dictionary data of

Which Students have scorescore is between 15 and 20?

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)

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

Agenda

1. Introduction to Pandas
2. Series
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

4.1 Import and Store Data

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

4.2 Import and Store Data - Text Files

• 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

Agenda

1. Introduction to Pandas
2. Series
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

5.1 Summarizing and Computing Descriptive Statistics

• 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

• To view a small sample of a Series or DataFrame object, use:
  • head()
  • tail()

• Get the First 3 lines of Dataframe

5.2 Summarizing and Computing Descriptive Statistics - Sorting

• To sort data for exploring the data use:
  • sort_index(): object by labels (along an axis)
  • sort_values(): by the values along either axis

• Sort Dataframe by rows in ascending order

• Sort Dataframe by columns in descending order

5.3 Summarizing and Computing Descriptive Statistics - Descriptive statistics

• 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

• mean()

• var()

5.4 Summarizing and Computing Descriptive Statistics - Missing Data

• 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

  - Fill Nan with default value

- Use inplace to modify the dataframe instead of retunring a new object

What is the median score of the students on the exam?

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?

Agenda

1. Introduction to Pandas
2. Series
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

6.1 Grouped and apply

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

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()

6.3 Grouped and apply - Apply

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

        - Apply a new function that subtract max from 2 times min in every column

Group students by attempts and find the average score?

Group students by their pass result and report the variance in scores?

1. Introduction to Pandas
2. Series
   • Exercise 1
   • Exercise 2
3. Dataframe
   • Exercise 3
   • Exercise 4
4. Import and Store Data
5. Summarizing and Computing Descriptive Statistics
   • Exercise 6
   • Exercise 7
6. Grouped and apply
   • Exercise 8
   • Exercise 9
7. Data Transformation and Normalization
   • Exercise 10
   • Exercise 11
   • Exercise 12

7.1 Data Transformation and Normalization

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

Pandas Dataframes have a built in plot functionality:

• Dataframe.plot()

• Plot the Number of Medals in barchart by country using: plot.bar()

• Other useful plots:
  • hist: plot.hist()
  • scatter: plot.scatter()
  • boxplot: plot.box()

7.2 Data Transformation and Normalization - Normalization

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

Resources:

• Pandas 10 min
• Pandas Tutorals
• Pandas Cookbook

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?

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?

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?