Python pandas says columns can't be found but they exist within a csv file

Question

So I have this script

mport pandas as pd
import numpy as np
PRIMARY_TUMOR_PATIENT_ID_REGEX = '^.{4}-.{2}-.{4}-01.*'
SHORTEN_PATIENT_REGEX = '^(.{4}-.{2}-.{4}).*'

def mutations_for_gene(df):
  mutated_patients = df['identifier'].unique()
  return pd.DataFrame({'mutated': np.ones(len(mutated_patients))}, index=mutated_patients)
  
def prep_data(mutation_path):
  df = pd.read_csv(mutation_path, low_memory=True, dtype=str, header = 0)#Line 24 reads in a line memory csv file from the given path and parses it based on '\t' delimators, and casts the data to str
  
  df = df[~df['Hugo_Symbol'].str.contains('Hugo_Symbol')] #analyzes the 'Hugo_Symbol' heading within the data and makes a new dataframe where any row that contains 'Hugo_Symbol' is dropped

  df['Hugo_Symbol'] = '\'' + df['Hugo_Symbol'].astype(str) # Appends ''\'' to all the data remaining in that column
  
  df['Tumor_Sample_Barcode'] = df['Tumor_Sample_Barcode'].str.strip() #strips away whitespace from the data within this heading
  non_silent = df.where(df['Variant_Classification'] != 'Silent') #creates a new dataframe where the data within the column 'Variant_Classification' is not equal to 'Silent'

  df = non_silent.dropna(subset=['Variant_Classification']) #Drops all the rows that are missing at least one element
  
  non_01_barcodes = df[~df['Tumor_Sample_Barcode'].str.contains(PRIMARY_TUMOR_PATIENT_ID_REGEX)]
  #TODO: Double check that the extra ['Tumor_Sample_Barcode'] serves no purpose
  df = df.drop(non_01_barcodes.index)
  print(df)
  shortened_patients = df['Tumor_Sample_Barcode'].str.extract(SHORTEN_PATIENT_REGEX, expand=False)
  df['identifier'] = shortened_patients
  
  gene_mutation_df = df.groupby(['Hugo_Symbol']).apply(mutations_for_gene)
  gene_mutation_df.columns = gene_mutation_df.columns.str.strip()
  gene_mutation_df.set_index(['Hugo_Symbol', 'patient'], inplace=True)
  gene_mutation_df = gene_mutation_df.reset_index()
  gene_patient_mutations = gene_mutation_df.pivot(index='Hugo_Symbol', columns='patient', values='mutated')
  
  return gene_patient_mutations.transpose().fillna(0)

This is the csv file that the script reads in:

identifier,Hugo_Symbol,Tumor_Sample_Barcode,Variant_Classification,patient
1,patient,a,Silent,6
22,mutated,d,e,7
1,Hugo_Symbol,f,g,88

The script gives this error:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-60-3f9c00f320bc> in <module>
----> 1 prep_data('test.csv')

<ipython-input-59-2a67d5c44e5a> in prep_data(mutation_path)
     21     display(gene_mutation_df)
     22     gene_mutation_df.columns = gene_mutation_df.columns.str.strip()
---> 23     gene_mutation_df.set_index(['Hugo_Symbol', 'patient'], inplace=True)
     24     gene_mutation_df = gene_mutation_df.reset_index()
     25     gene_patient_mutations = gene_mutation_df.pivot(index='Hugo_Symbol', columns='patient', values='mutated')

e:\Anaconda3\lib\site-packages\pandas\core\frame.py in set_index(self, keys, drop, append, inplace, verify_integrity)
   4546 
   4547         if missing:
-> 4548             raise KeyError(f"None of {missing} are in the columns")
   4549 
   4550         if inplace:

KeyError: "None of ['Hugo_Symbol', 'patient'] are in the columns"

Previously, I had this is as that line

   gene_mutation_df.index.set_names(['Hugo_Symbol', 'patient'], inplace=True)

But that also gave an error that the set_name length expects one argument but got two

Any help would be much appreciated

I would really prefer if the csv data was changed instead of the script and somehow the script could work with set_names instead of set_index

Answer 1

• The issue is:
  • gene_mutation_df = df.groupby(['Hugo_Symbol']).apply(mutations_for_gene)
    • 'Hugo_Symbol is used for a groupby, so now it's in the index, not a column
  • In the case of the sample data, an empty dataframe, with no columns, has been created.

    gene_mutation_df = df.groupby(['Hugo_Symbol']).apply(mutations_for_gene)
    print(gene_mutation_df)  # print the dataframe to see what it looks like
    print(gene_mutation_df.info())  # print the information for the dataframe
    gene_mutation_df.columns = gene_mutation_df.columns.str.strip()
    gene_mutation_df.set_index(['Hugo_Symbol', 'patient'], inplace=True)

# output

Empty DataFrame
Columns: [identifier, Hugo_Symbol, Tumor_Sample_Barcode, Variant_Classification, patient]
Index: []
Empty DataFrame
Columns: []
Index: []


<class 'pandas.core.frame.DataFrame'>
Index: 0 entries
Empty DataFrameNone

reset the index

• Resetting the index, will make Hugo_Symbol a column again
• As long as the dataframe is not empty, the KeyError should be resolved.

    gene_mutation_df = gene_mutation_df.reset_index()  # try adding this line
    gene_mutation_df.set_index(['Hugo_Symbol', 'patient'], inplace=True)

Addition Notes

• There are a number of lines of code, that may be resulting in an empty dataframe
  • non_01_barcodes = df[~df['Tumor_Sample_Barcode'].str.contains(PRIMARY_TUMOR_PATIENT_ID_REGEX)]
  • shortened_patients = df['Tumor_Sample_Barcode'].str.extract(SHORTEN_PATIENT_REGEX, expand=False)
  • gene_mutation_df = df.groupby(['Hugo_Symbol']).apply(mutations_for_gene)

Test if the dataframe is empty

• Use .empty to determine if a dataframe is empty

def prep_data(mutation_path):
    df = pd.read_csv(mutation_path, low_memory=True, dtype=str, header = 0)#Line 24 reads in a line memory csv file from the given path and parses it based on '\t' delimators, and casts the data to str
    
    df.columns = df.columns.str.strip()  # clean the column names here if there is leading or trailing whitespace.
    
    df = df[~df['Hugo_Symbol'].str.contains('Hugo_Symbol')] #analyzes the 'Hugo_Symbol' heading within the data and makes a new dataframe where any row that contains 'Hugo_Symbol' is dropped

    df['Hugo_Symbol'] = '\'' + df['Hugo_Symbol'].astype(str) # Appends ''\'' to all the data remaining in that column

    df['Tumor_Sample_Barcode'] = df['Tumor_Sample_Barcode'].str.strip() #strips away whitespace from the data within this heading
    non_silent = df.where(df['Variant_Classification'] != 'Silent') #creates a new dataframe where the data within the column 'Variant_Classification' is not equal to 'Silent'

    df = non_silent.dropna(subset=['Variant_Classification']) #Drops all the rows that are missing at least one element

    non_01_barcodes = df[~df['Tumor_Sample_Barcode'].str.contains(PRIMARY_TUMOR_PATIENT_ID_REGEX)]
    #TODO: Double check that the extra ['Tumor_Sample_Barcode'] serves no purpose
    df = df.drop(non_01_barcodes.index)
    print(df)
    shortened_patients = df['Tumor_Sample_Barcode'].str.extract(SHORTEN_PATIENT_REGEX, expand=False)
    df['identifier'] = shortened_patients

    gene_mutation_df = df.groupby(['Hugo_Symbol']).apply(mutations_for_gene)
    
    gene_mutation_df = gene_mutation_df.reset_index()  # reset the index here
    
    print(gene_mutation_df)
    
    if gene_mutation_df.empty:  # check if the dataframe is empty
        print('The dataframe is empty')
        
    else:
    
#         gene_mutation_df.set_index(['Hugo_Symbol', 'patient'], inplace=True)  # this is not needed, pivot won't work if you do this
#         gene_mutation_df = gene_mutation_df.reset_index()  # this is not needed, the dataframe was reset already
        gene_patient_mutations = gene_mutation_df.pivot(index='Hugo_Symbol', columns='patient', values='mutated')  # values needs to be a column in the dataframe

        return gene_patient_mutations.transpose().fillna(0)