Xtykutl

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.

Try this:

In [1411]: excel_df = pd.read_excel('myexcel.xlsx')



In [1412]: excel_df

Out[1412]: 

       Name       Job

0  Damian    Engineer

1  Rose      Musician

2  Eric        Dancer





In [1415]: txt_df = pd.read_csv('hello.txt', header=None)

In [1418]: txt_df.columns = ['Age', 'Job']



In [1419]: txt_df

Out[1419]: 

   Age        Job

0   25   Engineer

1   26     Dancer



In [1447]: pd.merge(excel_df, txt_df, on='Job')

       Name       Job  Age

0  Damian    Engineer   25

1  Eric        Dancer   26

pandas has a merge function. This assumes that your csv has headers, if it doesn't you can provide them or use left_on and right_on

xls = pd.read_excel(excel_file)

other_file = pd.read_csv('hello.txt', sep=',')

xls = xls.merge(other_file, on='Job')

This will work for you. You have to strip the 'Job' column or remove whitespaces from the txt file between columns.

import pandas as pd



xls = pd.read_excel('sample2.xlsx')



csv_df = pd.read_csv('sample.txt', header=None)

csv_df.columns = ['Age', 'Job']

csv_df['Job'] = csv_df['Job'].map(str.strip)

xls = xls.merge(csv_df, on='Job')



print(xls)