rename specific columns in excel with pandas-CodePudding

There are two files. One is a text file with names and the other is an excel file with a list of participants. The excel list also contains names that appear in the text file. In the excel file there is a column with the parameter participated. I would like to give the value "yes" in the column "participated" within the excel file for all participants that are in the text file.

Thank you for your help and ideas.

My python code:

import pandas as pd
excelList = pd.read_excel("participantsList.xlsx", sheet_name="Table1")
participatedList = open('participated.txt','r')
#CODE TO CHANGE THE PARAMETER
excelList.to_excel(r'newList.xlsx')

participated.txt 
(The following names should get the value "Yes" in the excel file.)

participant one
participant two
participant three

participantsList.xlsx

First name  | Last Name | Participated

Magnus      | one       | 
Maverick    | two       |
Rhett       | three     |
Winston     | four      |
Xander      | five      |

CodePudding user response：

I'm a beginner and this is my try to solve your question :) Thank you

import pandas as pd
excelList = pd.read_excel("participantsList.xlsx", sheet_name="Table1")
#participatedList = open('participated.txt','r')
#CODE TO CHANGE THE PARAMETER
#excelList.to_excel(r'newList.xlsx')

data = pd.read_csv('participated.txt', sep=" ", header=None)
data.columns = ["First Name", "Last Name"]

newList = excelList.copy()

txt = 0
while txt < len(data):
    i = 0
    while i < len(excelList):
        if data['Last Name'][txt] == excelList['Last Name'][i]:
            newList.loc[i,'Participated'] = "Yes"
        i  = 1
    i = 0
    txt  = 1

newList.to_excel(r'newList.xlsx')

print(newList)