Rename specific columns in excel with pandas

Question

Problem: 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.

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')

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

participant one
participant two
participant three

File: participantsList.xlsx

First name  | Last Name | Participated
______________________________________
Magnus      | one       | 
Maverick    | two       |
Rhett       | three     |
Winston     | four      |
Xander      | five      |

Please take a look at [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) and [edit] your question to include a [mcve] with sample input data and expected output, so that we can understand what you're asking — G. Anderson, Nov 22 '21 at 18:12
Can you show what the excel file and variable `participatedList` both look like? As @G.Anderson commented, it's going to be hard to solve your problem when explained only in words — Derek O, Nov 22 '21 at 18:13
This is how the two files look. A yes should be entered in the Excel file for the names in the text file. — 1ne3o, Nov 22 '21 at 18:33

yasu_naka · Accepted Answer · 2021-11-25T12:09:40.103

Here is my try:

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
    txt += 1

newList.to_excel(r'newList.xlsx')

print(newList)

Rename specific columns in excel with pandas

1 Answers1