CODEWITHSUNDEEP
pythonexcelpandas
dustin
dustin

Reputation: 49

Rename specific columns in excel with pandas

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      |

Upvotes: 2

Views: 271

Answers (1)

yasu_naka
yasu_naka

Reputation: 66

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)

Upvotes: 1

Related Questions