我写的程序取2个csv，每个来自我们的基因结果报告器，并将它们合并到“样品Id”列。
| 试验ID_x|基因符号|NCBI SNP参考|样品编号|调用_x|试验ID_y|基因符号|调用_y|唯一ID|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| C_###|细胞色素P2 D 6|编号|编号12878|C/A|C_##|细胞色素P2 D 6|C/A|“一致”|
| C_###|细胞色素P2 D 6|编号|编号12878_1|C/C|C_##|细胞色素P2 D 6|C/C|“一致”|
比较报告者的呼叫以获得类似的结果。本质上，如果呼叫x！=呼叫y，则标记为不一致。
问题是：有时2行是同一个样品，用下划线隔开名称（例如NA1234和NA1234_1）。
考虑到代码只检查一行上的调用，我无法找到比较同一样本的其他行的方法。
请注意，对于第一行，NA12878具有C/A和C/A的匹配调用。下一个样本NA12878_1（同一个样本）具有C/C和C/C的匹配调用。
这些调用实际上不一致。C/A和C/C不匹配，必须标记以供审查。
我试过几种不同的策略，但都没有奏效。

import pandas as pd
import tkinter as tk
from tkinter import filedialog
import tkinter.messagebox
import jinja2
import openpyxl

# The purpose of this program is to compare calls from duplicates, and the calls are aligned from 2 reporters
# There will be 2 Genotyper output passes
# We will flag the calls/comparisons if !=

# Defining the upload prompt
# File Upload window.
def ask_path():
    root = tk.Tk()
    root.withdraw()
    path = filedialog.askopenfile(defaultextension=".csv",
                                  filetypes=[("csv files", '*.csv'),
                                             ('all files', '*.*')])  # shows dialog box and return the path
    return path

# Pop-up window branding
def onClick(msg):
    tkinter.messagebox.showinfo("Precision Genetics", msg)

# Prompt & Load First Pass
genotyper1_col = ['Gene Symbol', 'Sample ID', 'NCBI SNP Reference', 'Assay ID', 'Call']
onClick('Upload First Pass')
geno1 = pd.read_csv(ask_path(), skiprows=17, usecols=genotyper1_col)

# Prompt & Load the Second Pass
genotyper2_col = ['Gene Symbol', 'Assay ID', 'Sample ID', 'NCBI SNP Reference', 'Call']
onClick('Upload Second Pass')
geno2 = pd.read_csv(ask_path(), skiprows=17, usecols=genotyper2_col)

# Merge on specified column
merged_data = pd.merge(geno1, geno2, on=['NCBI SNP Reference', 'Sample ID'])

# Filter out rows that don't have an NCBI SNP Reference in both passes
merged_data = merged_data[~merged_data['NCBI SNP Reference'].isna()]

# Here is where we will attempt to flag the Call Columns if they are not equal to one another
merged_data.loc[merged_data['Call_x'] != merged_data['Call_y'], 'Flag'] = 'Not in Agreement'
merged_data.loc[merged_data['Call_x'] == merged_data['Call_y'], 'Flag'] = '-'

# Highlight Cell if it isnt in agreement
def highlight_cells(value):
    if value == merged_data['Not in Agreement']:
        color = 'red'
    else:
        color = ''
    return 'background-color: {}'.format(color)

merged_data.style.applymap(highlight_cells)

# Save the data
onClick('Please Choose Where You Would Like To Store This Data')

def save_loc(dataframe):
    root = tk.Tk()
    root.withdraw()
    file_path = filedialog.asksaveasfilename(defaultextension=".csv",
                                             filetypes=[("CSV Files", "*.csv"),
                                                        ("All Files", "*.*")])
    if file_path:
        dataframe.to_csv(file_path, index=False)
    root.destroy()

save_loc(merged_data)

• 新的更新 * 这是新编辑的代码，在惊人的帮助后，你们都给了我。无论什么原因，我仍然得到“不一致”的一切在标记栏。此外，我认为这将是一个好主意，以更新到底是什么样的合并csv看起来像它的标题，以及为什么我改变了位置值，他们是什么。再次感谢一切。

def get_root_id(s):
    return s.split("_", 1)[0]

sample_ids = defaultdict(list)
headers = []

with open("merged_data.csv", newline="") as f:
    reader = csv.reader(f)
    header = next(reader)
    for row in reader:
        root_id = get_root_id(row[3])
        sample_ids[root_id].append(row)

for rows_by_id in sample_ids.values():
    call_vals = set()
    for row in rows_by_id:
        call_vals.update(row[4:7])

    if len(call_vals) > 1:
        for row in rows_by_id:
            row[8] = 'Not In Agreement'

output_file = 'output.csv'
with open("output.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(header)
    for rows_by_id in sample_ids.values():
        for row in rows_by_id:
            writer.writerow(row)
output_data = pd.read_csv(output_file)
print(output_data)