Python code to replace a given value in a .csv from another value from a different .csv?

Question

I'm a bit new to Python and am trying to figure out how to find & replace values from a given results output that is automatically generated by Qualtrics with values from a "master sheet" from another .csv from two different columns. The experiment is to measure listening errors based on different speech samples from various experimental conditions, and users are providing either a rating or a transcription of what they hear.

The generated output that I'm attempting to change is something like this:

Participant ID	...	1a_CO	2b_JF	3a_PF	...
R_tVTH8Jm8XDEYb3r	...	4.0	3.0	1.5	...

Where the first row contains a number and letter which correspond to what phrase and condition was presented to the speaker, and the letters after the underscore the initials of the speaker being presented (to see if there is differences across different speakers since they each have different severities of neurological conditions).

I have a mastersheet containing what these ID's correspond to stimulus wise (the first number in the first row from the generated column, with no letters signifying the original stimulus, and a-c representing different experimental conditions), similar to something like this:

Internal ID	Stimulus Presented
1	Target Phrase
2	Target Phrase
3	Target Phrase
...	...
38	Target Phrase
1a	Experimental Condition 1: Corresponding Phrase
2a	Experimental Condition 1: Corresponding Phrase
3a	Experimental Condition 1: Corresponding Phrase
...	...
38b	Experimental Condition 2: Corresponding Phrase
1b	Experimental Condition 2: Corresponding Phrase
2b	Experimental Condition 2: Corresponding Phrase
3b	Experimental Condition 2: Corresponding Phrase
...	...
38b	Experimental Condition 2: Corresponding Phrase
1c	Experimental Condition 3: Corresponding Phrase
2c	Experimental Condition 3: Corresponding Phrase
3c	Experimental Condition 3: Corresponding Phrase
...	...
38c	Experimental Condition 3: Corresponding Phrase

Ideally I want to just replace the values within the header row (which happens to be row 2 in the generated output) and have the output be something like this:

Participant ID	...	CO_Experimental Condition_Corresponding Phrase	JF_Experimental Condition_Corresponding Phrase	PF_Experimental Condition_Corresponding Phrase	...
R_tVTH8Jm8XDEYb3r	...	4.0	3.0	1.5	...

or even (if this is easier):

Participant ID	...	1a_CO	2b_JF	3a_PF	...
Participant ID	...	Experimental Condition_Corresponding Phrase from mastersheet	Experimental Condition_Corresponding Phrase from mastersheet	Experimental Condition_Corresponding Phrase from mastersheet	...
R_tVTH8Jm8XDEYb3r	...	4.0	3.0	1.5	...

I have no clue where to begin and would be very thankful for some guidance as I've tried

Replace list of id in cell by matching value id another dataframe (pandas)

to no success. Thank you in advance!

EDIT: Example Code from update existing csv file with updated info in another csv file:

#!/usr/bin/env python3
import csv
import os

# Define column names
fields = ['Internal ID', 'Experimental_Condition', 'Phrase']

os.chdir('/Users/Kelvin/Desktop/')

# Open the necesary files
with open('results.csv', 'r') as readFile_results:
    with open('master.csv', 'w') as readFile_master:
            with open('results.csv', 'r') as readFile_results:
                master = csv.DictReader(readFile_master, fieldnames=fields)
                update = csv.DictReader(readFile_results, fieldnames=fields)
                writer = csv.DictWriter(readFile_master, fieldnames=fields)

                # Saves and skips header to output file
                writer.writerow(next(readFile_results))

                # Goes through whole master sheet
                for column3 in master:
                    for row2 in update:
                        if column['Phrase'] in update:
                            writer.writerow(row2)
                        else:
                            writer.writerow('NA')

ReadFilemaster.close()
ReadFileresults.close()
results.close()

Answer 1

Do you have a code example to share? Without seeing the code, it's hard to know how you've constructed it.

One idea is to convert the master sheet to a dictionary where the key is Internal ID. Then iterate over the results output headers and split the text with the "_". You can then iterate over those splits, use the dictionary to get the value then concatenate it back into 1 string.