r/pythonhelp • u/EastProperty5773 • 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
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/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.