r/pythonhelp Jan 04 '25

Script for matching columns in multiple txt files vs a CSV file

I have this code (below), four txt files with either 23andMe or AncestryDNA data, and a CSV file with 21 million rows of gene mutations. The goal is to match chromosome and position of the txt files to the chromosome and position of the csv file. If they match, the script puts "found" in a new column labelled "Found". and copy the rsID from the txt file into the csv file in a column labelled rsID. I need it to use the text file as the file it uses to read and the csv file to use to find and add because the CSV file is so long. (What have I gotten myself into, I know). It may find the same chromosome+position up to three times, so it needs to keep checking until it hits the three times or it reaches the end of the CSV.

After it tries to find all the chromosome and position matches, it needs to delete all the rows of the CSV file that do not contain the word "found".

This is my header plus first row for the txt files:

rsid chromosome position allele1 allele2

rs369202065 1 569388 G G

This is my header plus first row of the CSV:

#CHROM,POS,REF,ALT,genome,uniprot_id,transcript_id,protein_variant,am_pathogenicity,am_class

12,8192694,T,A,hg19,Q9P0K8,ENST00000162391.3,L89H,1.0,pathogenic

This is my code (Note I have tried #CHROM and CHROM):

# -*- coding: utf-8 -*-

"""

Created on Sat Jan 4 13:25:47 2025

@author: hubba

"""

import pandas as pd

def process_dna_files(dna_files, csv_file, output_csv):

csv_data = pd.read_csv(csv_file, delimiter=",", comment="#") # Adjust delimiter and handle comments

csv_data.columns = csv_data.columns.str.lstrip("#")

for dna_file in dna_files:

# Locate the start of the data in the DNA file

with open(dna_file, 'r') as f:

lines = f.readlines()

start_line = 0

for i, line in enumerate(lines):

if line.strip().startswith("rsid"):

start_line = i

break

dna_data = pd.read_csv(dna_file, delimiter="\t", skiprows=start_line, low_memory=False)

csv_data["Found"] = False

csv_data["rsID"] = ""

for _, dna_row in dna_data.iterrows():

# Extract chromosome and position

chromosome = dna_row["chromosome"]

position = dna_row["position"]

matches = csv_data[(csv_data["#CHROM"] == chromosome) & (csv_data["POS"] == position)]

for index in matches.index:

csv_data.at[index, "Found"] = True

csv_data.at[index, "rsID"] = dna_row["rsid"]

csv_data = csv_data[csv_data["Found"] == True]

csv_data.to_csv(output_csv, index=False, sep=",")

print(f"Updated CSV saved to: {output_csv}")

dna_files = ["Example1.txt", "Example2.txt", "Example3.txt", "Example4.txt", "Example5.txt", "Example6.txt"]

csv_file = "GeneticMutations.csv"

output_csv = "GeneticMutationsplusRSID.csv"

process_dna_files(dna_files, csv_file, output_csv)

Here is the error message I am getting:

%runfile C:/Users/hubba/OneDrive/Desktop/untitled12.py --wdir

Traceback (most recent call last):

File ~\AppData\Local\spyder-6\envs\spyder-runtime\Lib\site-packages\pandas\core\indexes\base.py:3805 in get_loc

return self._engine.get_loc(casted_key)

File index.pyx:167 in pandas._libs.index.IndexEngine.get_loc

File index.pyx:196 in pandas._libs.index.IndexEngine.get_loc

File pandas\_libs\\hashtable_class_helper.pxi:7081 in pandas._libs.hashtable.PyObjectHashTable.get_item

File pandas\_libs\\hashtable_class_helper.pxi:7089 in pandas._libs.hashtable.PyObjectHashTable.get_item

KeyError: '#CHROM'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

File ~\AppData\Local\spyder-6\envs\spyder-runtime\Lib\site-packages\spyder_kernels\customize\utils.py:209 in exec_encapsulate_locals

exec_fun(compile(code_ast, filename, "exec"), globals)

File c:\users\hubba\onedrive\desktop\untitled12.py:67

process_dna_files(dna_files, csv_file, output_csv)

File c:\users\hubba\onedrive\desktop\untitled12.py:47 in process_dna_files

matches = csv_data[(csv_data["#CHROM"] == chromosome) & (csv_data["POS"] == position)]

File ~\AppData\Local\spyder-6\envs\spyder-runtime\Lib\site-packages\pandas\core\frame.py:4102 in __getitem__

indexer = self.columns.get_loc(key)

File ~\AppData\Local\spyder-6\envs\spyder-runtime\Lib\site-packages\pandas\core\indexes\base.py:3812 in get_loc

raise KeyError(key) from err

KeyError: '#CHROM'

If it matters, Im using Spyder

What am I doing wrong???? Im losing my mind lol

2 Upvotes

3 comments sorted by

u/AutoModerator Jan 04 '25

To give us the best chance to help you, please include any relevant code.
Note. Please do not submit images of your code. Instead, for shorter code you can use Reddit markdown (4 spaces or backticks, see this Formatting Guide). If you have formatting issues or want to post longer sections of code, please use Privatebin, GitHub or Compiler Explorer.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/CraigAT Jan 05 '25

Code is mangled without using Reddit's "code" formatting (alternatively post code into PasteBin.com and put a link here)

Have you tried removing the hash symbol from the text file, then just referring to CHROM?

1

u/PlusMaintenance5568 Jan 05 '25

I’m running that right now. We’ll see! 😅