merging a excel file and a text file with similar job

3677 views python
4

I have an excel file that holds a data like this:

Name    Job
Damian  Engineer
Rose    Musician
Eric    Dancer

I want to merge this with a textfile with rows with the same job:

25, Engineer
26, Dancer

So the final out put would be:

Name    Job         Age
Damian  Engineer    25
Eric    Dancer      26

I have written a something to start but I'm not sure how to have the final output

xls = pd.read_excel(excel_file)
    excel_dict = xls.astype(str).to_dict('list')
    with open('hello.txt', 'rb') as f1:
        csv_reader = csv.reader(f1)
        for row in csv_reader:
            job = row[1]
            if job in excel_dict['Job']:
                excel_dict['Age'] = row[0]

Could you please help me on how to achieve the final output above? I am using pandas to read the excel file and csv to read the textfile.

answered question

1 Answer

0

Try this:

excel_df = pd.read_excel(excel_file, header=True)
txt_df = pd.read_csv('hello.txt')
txt_df.columns = ['num', 'Job']

pd.merge(excel_df, txt_df, on='Job')

posted this

Have an answer?

JD

Please login first before posting an answer.