Adding multiple dataframes to compare against genotypes - DATE/LOCATION - geotemporal incidences of SARS2 genotypes

Question

I'm trying to reuse some code I have previously used to add extra parameters. In the initial code, it was used to give a 1/0 counter if a specific mutation was present (first code below)

import re
import os
import pandas as pd
import logging
import sys
import csv

root = "C:"

os.chdir(root)

lineages = os.listdir('Lineages')
mutations = os.listdir('Mutations')

for lineage in lineages:
    
    df = pd.read_csv('Lineages/' + lineage).dropna()
    df.columns = ["lineage"]
    df['lineage'] = [x.split('|')[0].split('_')[-1]
                     for x in df['lineage']]
               

    for mutation in mutations:
        
        mutation_df = pd.read_csv('Mutations/' + mutation).dropna()
        mutation_header = mutation.replace(".txt", "")
        mutation_df.columns = [mutation_header]
        mutation_df[mutation_header] = [x.split('_')[-1].split('|')[0] 
                          for x in mutation_df[mutation_header]]
        
        mutation_set = set(mutation_df[mutation_header])
        
        df[mutation_header] = [1 if x in mutation_set else 0
                               for x in df['lineage']]
        
    df.to_csv('Results9/' + lineage.replace(".txt", "") + '.csv',
                 header = True, 
                 index = False)

However, I now wish to add extra parameters such as date of sample and location of sample. The first code produces this type of counter system:

lineage,A222V,D614G,E484Q,E780Q,G476S,L18F,N439K,S477,S477N,T478I,V483A
417941,0,1,0,0,0,0,0,0,0,0,0
417942,0,1,0,0,0,0,0,0,0,0,0
417944,0,1,0,0,0,0,0,0,0,0,0
417946,0,1,0,0,0,0,0,0,0,0,0
417947,0,1,0,0,0,0,0,0,0,0,0
417948,0,1,0,0,0,0,0,0,0,0,0
417950,0,0,0,0,0,0,0,0,0,0,0
417955,0,1,0,0,0,0,0,0,0,0,0
431011,0,1,0,0,0,0,0,0,0,0,0

This code is fine and working, but if I try and add the second parameter (date/location) with the following code:

import re
import os
import pandas as pd
import logging
import sys
import csv

root = "C:/Users/jones/Downloads/SARSCOV2/"

os.chdir(root)

lineages = os.listdir('Lineages')
mutations = os.listdir('Mutations')
dates = os.listdir('Dates')
locations = os.listdir('Locations')

for date in dates:
            
    date_df = pd.read_csv('Dates/' + date).dropna()
    date_df.columns = ["Date"]
    date_df['Date'] = [x.split('|')[-1].split('/')[-1] 
                       for x in date_df['Date']]
            
    for location in locations:
                
        location_df = pd.read_csv('Locations/' + location).dropna()
        location_df.columns = ["Location"]
        location_df['Location'] = [x.split('/')[1].split('_')[0]
                                   for x in location_df['Location']]
            
        for lineage in lineages:
    
            df = pd.read_csv('Lineages/' + lineage).dropna()
            df.columns = ["lineage"]
            df['lineage'] = [x.split('_')[-1].split('|')[0]
                             for x in df['lineage']]
               

            for mutation in mutations:
        
                mutation_df = pd.read_csv('Mutations/' + mutation).dropna()
                mutation_header = mutation.replace(".txt", "")
                mutation_df.columns = [mutation_header]
                mutation_df[mutation_header] = [x.split('_')[-1].split('|')[0] 
                                                for x in mutation_df[mutation_header]]
        
                mutation_set = set(mutation_df[mutation_header])
        
                df[mutation_header] = [1 if x in mutation_set else 0
                                       for x in df['lineage']]
   
  
            df.to_csv('Results_3/' + lineage.replace(".txt", "") + '.csv',
                 header = True, 
                 index = False)

I retrieve this data, where the counting is maintaibned, but the new parameters (DATE/LOCATION) are not added

lineage,A222V,D614G,E484Q,E780Q,G476S,L18F,N439K,S477,S477N,T478I,V483A
417941,0,1,0,0,0,0,0,0,0,0,0
417942,0,1,0,0,0,0,0,0,0,0,0
417944,0,1,0,0,0,0,0,0,0,0,0
417946,0,1,0,0,0,0,0,0,0,0,0
417947,0,1,0,0,0,0,0,0,0,0,0
417948,0,1,0,0,0,0,0,0,0,0,0
417950,0,0,0,0,0,0,0,0,0,0,0
417955,0,1,0,0,0,0,0,0,0,0,0
431011,0,1,0,0,0,0,0,0,0,0,0

In short, my end goal is to:

• Add Lineage ID|Location of Sample|DD/MM/YYY|Mutations| -- as the headers
• still maintain the mutation counter
• Use the mutation counter as a guide to determine where and when the genotypes (mutation combinations) arose.

The files where I am retrieving the parameters looks like this also:

hCoV-19/Singapore/4/2020|EPI_ISL_410535|2020-02-03

hCoV-19/USA/WA13-UW9/2020|EPI_ISL_413601|2020-03-02

hCoV-19/USA/WA-UW142/2020|EPI_ISL_416680|2020-03-11

hCoV-19/USA/WA-UW143/2020|EPI_ISL_416681|2020-03-11

hCoV-19/USA/WA-UW144/2020|EPI_ISL_416682|2020-03-09

hCoV-19/USA/WA-UW145/2020|EPI_ISL_416683|2020-03-15

hCoV-19/USA/WA-UW146/2020|EPI_ISL_416684|2020-03-14

hCoV-19/USA/WA-UW147/2020|EPI_ISL_416685|2020-03-15

hCoV-19/USA/WA-UW148/2020|EPI_ISL_416686|2020-03-14

A very laborious, and poorly explained question; I apologise in advance!

Answer 1

Just to clarify, when I asked you to share some of your files, I meant to share them in a way that I can reproduce your issue from start to finish. You read from at least four different files in a quadruple for-loop and I'm pretty sure your goal could be achieved much more straightforwardly than your current implementation.

Without a reproducible example, I can offer a best-guess on what's happening: you create date_df and location_df, but after you instantiate them, you never do anything with the objects. Your final write happens from df, so you'll need to add those columns, e.g.:

df["Date"] = date_df["Date"][0]
df["Location"] = location_df["Location"][0]

The above is just an example, you'll need to make sure the correct value gets assigned to your df object instead of simply the first one.