Pandas

Manipulating dtables

Dr. Yu Cheng Hsu

Learning Objectives

  • Perform table manipulation with pandas
  • Evaluate the code robustness and its implication
  • Appreciate and apply pipeline mindset

Library

In python a lot of developer will write their own code and share to other to reuse. This package of code are called library

To install a specific library you can do

! pip install PACKAGE_NAME

You do not need to do this on Google colab as Google colab pre-installed most of the commonly used package for you already

Import library

You can import the installed library throgh keyword import

Pandas

  • Pandas is a specialized library for manipulating tabular data
  • To use the exisiting library we can do
# 'pd' is the universal standard nickname for pandas. 
# Every coder in the world uses this exact line.
import pandas as pd

Dataset Overview

You will analyze brain tissue RNA-Seq profiles from mice infected with influenza over an 8-day timecourse:

  • Source: link
  • Key columns: gene, sample, expression, sex, infection, time, tissue, gene_biotype

The case is adopted from Gatto, Missault, and Martin (2021) with data from Blackmore et al. (2017)

Loading the data

  • Data table is called a DataFrame (usually abbreviated as df) in Pandas
  • To read the data we use pd.read_csv("FILE_LOCATION")
# Load the CSV file into memory
# (Assume we uploaded 'rnaseq_results.csv' to Colab)
df = pd.read_csv("https://raw.githubusercontent.com/UCLouvain-CBIO/WSBIM1207/refs/heads/main/docs/data/rnaseq.csv")

# Printing the first 3 rows fo the dataframe 
print(df.head(5))
      gene      sample  expression      organism  age     sex   infection  \
0      Asl  GSM2545336        1170  Mus musculus    8  Female  InfluenzaA   
1     Apod  GSM2545336       36194  Mus musculus    8  Female  InfluenzaA   
2  Cyp2d22  GSM2545336        4060  Mus musculus    8  Female  InfluenzaA   
3     Klk6  GSM2545336         287  Mus musculus    8  Female  InfluenzaA   
4    Fcrls  GSM2545336          85  Mus musculus    8  Female  InfluenzaA   

    strain  time      tissue  mouse  ENTREZID  \
0  C57BL/6     8  Cerebellum     14    109900   
1  C57BL/6     8  Cerebellum     14     11815   
2  C57BL/6     8  Cerebellum     14     56448   
3  C57BL/6     8  Cerebellum     14     19144   
4  C57BL/6     8  Cerebellum     14     80891   

                                             product     ensembl_gene_id  \
0     argininosuccinate lyase, transcript variant X1  ENSMUSG00000025533   
1             apolipoprotein D, transcript variant 3  ENSMUSG00000022548   
2  cytochrome P450, family 2, subfamily d, polype...  ENSMUSG00000061740   
3  kallikrein related-peptidase 6, transcript var...  ENSMUSG00000050063   
4  Fc receptor-like S, scavenger receptor, transc...  ENSMUSG00000015852   

  external_synonym chromosome_name    gene_biotype  \
0    2510006M18Rik               5  protein_coding   
1              NaN              16  protein_coding   
2             2D22              15  protein_coding   
3             Bssp               7  protein_coding   
4    2810439C17Rik               3  protein_coding   

                             phenotype_description  \
0            abnormal circulating amino acid level   
1                       abnormal lipid homeostasis   
2                         abnormal skin morphology   
3                          abnormal cytokine level   
4  decreased CD8-positive alpha-beta T cell number   

  hsapiens_homolog_associated_gene_name  
0                                   ASL  
1                                  APOD  
2                                CYP2D6  
3                                  KLK6  
4                                 FCRL2  

Inspecting the DataFrame

Some few information you can retrieved of your data

  • .shape : The dimensions: (number_of_rows, number_of_columns)
  • .columns : List of all the column headers
# 1. df.shape tells you the dimensions: (number_of_rows, number_of_columns)
print("Data dimensions:", df.shape)

# 2. df.columns gives you a List of all the column headers
print("Column names:", df.columns)
Data dimensions: (32428, 19)
Column names: Index(['gene', 'sample', 'expression', 'organism', 'age', 'sex', 'infection',
       'strain', 'time', 'tissue', 'mouse', 'ENTREZID', 'product',
       'ensembl_gene_id', 'external_synonym', 'chromosome_name',
       'gene_biotype', 'phenotype_description',
       'hsapiens_homolog_associated_gene_name'],
      dtype='object')

Selecting Columns

Subset the DataFrame by passing a List of the column names you want to keep inside square brackets.

# We use double brackets: the outer brackets are for Pandas, 
# and the inner brackets represent our Python List of names!
df_subset = df[["gene", "expression", "tissue"]]

df_subset.head()
gene expression tissue
0 Asl 1170 Cerebellum
1 Apod 36194 Cerebellum
2 Cyp2d22 4060 Cerebellum
3 Klk6 287 Cerebellum
4 Fcrls 85 Cerebellum

Precise Slicing .loc and .iloc

.loc Label location

# 1. Turn the "gene" column into the Index
df_indexed = df.set_index("gene")

# 2. Let's find the data for the Plp1 gene:
plp1_data = df_indexed.loc["Plp1", :]

print("Data for Plp1:")
print(plp1_data.head(2))

# 3. We can be highly specific: 
# "Give me the Plp1 row, but only the expression column"
plp1_exp = df_indexed.loc["Plp1", "expression"]
print("Plp1 Expression:", plp1_exp.head(2))
Data for Plp1:
          sample  expression      organism  age     sex    infection   strain  \
gene                                                                            
Plp1  GSM2545336       43217  Mus musculus    8  Female   InfluenzaA  C57BL/6   
Plp1  GSM2545337      101241  Mus musculus    8  Female  NonInfected  C57BL/6   

      time      tissue  mouse  ENTREZID  \
gene                                      
Plp1     8  Cerebellum     14     18823   
Plp1     0  Cerebellum      9     18823   

                                                product     ensembl_gene_id  \
gene                                                                          
Plp1  proteolipid protein (myelin) 1, transcript var...  ENSMUSG00000031425   
Plp1  proteolipid protein (myelin) 1, transcript var...  ENSMUSG00000031425   

     external_synonym chromosome_name    gene_biotype  \
gene                                                    
Plp1             DM20               X  protein_coding   
Plp1             DM20               X  protein_coding   

                   phenotype_description hsapiens_homolog_associated_gene_name  
gene                                                                            
Plp1  abnormal CNS glial cell morphology                                  PLP1  
Plp1  abnormal CNS glial cell morphology                                  PLP1  
Plp1 Expression: gene
Plp1     43217
Plp1    101241
Name: expression, dtype: int64

.iloc index location

# Grab the first row (index 0) and all columns (the colon :)
first_row = df.iloc[0, :]

# Grab the first 3 rows, and only the first 2 columns
# (Remember: 0:3 means it grabs 0, 1, and 2. It stops BEFORE 3!)
top_left_block = df.iloc[0:3, 0:2]

print(top_left_block)
      gene      sample
0      Asl  GSM2545336
1     Apod  GSM2545336
2  Cyp2d22  GSM2545336

Comparison

Feature .iloc (Integer Location) .loc (Label Location)
Lookup Mechanism Position-based: Uses exact numerical coordinates (0, 1, 2). Name-based: Uses the actual row/column names.
Example Syntax df.iloc[0:5, 2] df.loc["BRCA1":"EGFR", "expression"]
Slicing Rule Exclusive of the stop number (0:3 grabs 0, 1, 2). Inclusive of the stop name ("A":"C" grabs A, B, and C).
Best Used For Grabbing the “Top 10” rows, or dealing with un-named data. Extracting specific genes, writing reproducible pipelines.
Robustness Highly Fragile. Breaks silently if data changes. Highly Robust. Immune to structural column/row shifts.

Robustness matters

Goal: Extracting the expression column

.loc Label location - Use actual name

# Extract all rows for the `expression` column
df.loc[:, "expression"].head(3)
0     1170
1    36194
2     4060
Name: expression, dtype: int64

.iloc index location

# Extract all rows for the 3rd column
df.iloc[:, 2].head(3)
0     1170
1    36194
2     4060
Name: expression, dtype: int64

Scenario:

Sequencing facility updates their software and adds a new quality_score column at the very beginning of the CSV. What will happen ?

  • label location: Not affected
  • index location: Not referring to the intended column (and without warning message)

Creating new columns

Creating new column:

  • Assign on a new column.
  • Do not need to loop all cells, Most of the python math function will apply the math mfunctin to every cells in the table
#### Time start (This is for the next slides)
start_time_loop = time.time()
## Exact code starts

# Create a new column called 'log_expression'
df["log_expression"] = np.log2(df["expression"])

#### Exact code ends, Below are for the next slides and display purpose 
end_time_loop = time.time()
loop_time = end_time_loop - start_time_loop
print(f"For loop finished in: {loop_time:.5f} seconds")
df.head(2)
For loop finished in: 0.00184 seconds
gene sample expression organism age sex infection strain time tissue mouse ENTREZID product ensembl_gene_id external_synonym chromosome_name gene_biotype phenotype_description hsapiens_homolog_associated_gene_name log_expression
0 Asl GSM2545336 1170 Mus musculus 8 Female InfluenzaA C57BL/6 8 Cerebellum 14 109900 argininosuccinate lyase, transcript variant X1 ENSMUSG00000025533 2510006M18Rik 5 protein_coding abnormal circulating amino acid level ASL 10.192293
1 Apod GSM2545336 36194 Mus musculus 8 Female InfluenzaA C57BL/6 8 Cerebellum 14 11815 apolipoprotein D, transcript variant 3 ENSMUSG00000022548 NaN 16 protein_coding abnormal lipid homeostasis APOD 15.143463

What if using for Loops

We can use for loop to achieve exactly the same goal

# Time start
start_time_loop = time.time()

#### Exact code starts here 
# 1. Create an empty list to store the answers
new_values = []
# 2. Loop through every single row in the dataframe
for i in range(len(df)):
    # Calculate the new value for this specific row and appe
    math_result = np.log2(df.loc[i, "expression"])
    new_values.append(math_result)
# 3. Assign the completed list as a new column
df["log_expression"] = new_values

#### Exact code ends, Time end
end_time_loop = time.time()
loop_time = end_time_loop - start_time_loop
print(f"For loop finished in: {loop_time:.2f} seconds")
For loop finished in: 0.21 seconds

Advantage of using Pandas

Vectorization (Pandas)

  • Code Clarity: Poor. Using 5 lines to write
  • Time efficiency: Low (0.16s)

For loop (Pandas)

  • Code Clarity: Great. 1 line solution, easy to understand
  • Time efficiency: Fast (0.001s)
  • The time difference will be more prominent when the rows increase

Save your results

.to_csv/ .to_excel methods can let you save your results

# Save our filtered 'highly_expressed_genes' table to a new CSV file.
# index=False prevents Pandas from exporting the 0,1,2,3 row numbers.
df.to_csv("high_expression_results.csv", index=False)

Data wrangling

Motivations

If we manipulate the data with what we have just cover

# Create a copy of df to prevent overwriting  
my_data = df.copy()

# 2. Filter for high expression
my_data = my_data[my_data["expression"] > 50000]

# 3. Talking the log of expression
my_data["expression"] = np.log2(my_data["expression"])

Problems

Colab lets you run the same cell multiple times (by accidentally pressing Shift + Enter twice), this step-by-step approach is highly dangerous.

  • Run 1: Your expression of 60,000 is taking log to log(60000)
  • Run 2: You accidentally run the cell again. Python take log again become log(log(60000))

Other problem

The below code will give you a warning

# 1. Filter the data
high_exp = df[df["expression"] > 50000]

# 2. Try to do math on the filtered data
high_exp["expression_k"] = high_exp["expression"] / 1000

Pandas gives you warning because it isn’t sure if high_exp is a brand-new table or just a “window” looking at the original df

Implication

If in the step 1 and 2 you do some change on df["expression"] then high_exp won’t get the updated value

Bibliography Notes

Blackmore, Stephen, Jessica Hernandez, Michal Juda, Emily Ryder, Gregory G Freund, Rodney W Johnson, and Andrew J Steelman. 2017. “Influenza Infection Triggers Disease in a Genetic Model of Experimental Autoimmune Encephalomyelitis.” Proceedings of the National Academy of Sciences 114 (30): E6107–16.
Gatto, Laurent, Kevin Missault, and Manon Martin. 2021. “UCLouvain-CBIO/WSBIM1207: V2.0.” Zenodo. https://doi.org/10.5281/zenodo.5532658.
Seurat, Georges. 1883. A Summer Landscape. Oil on wood. National Gallery of Art.