'Find data of a specific column of a data file to another file's specific columns and print all rows using python

I have two text files. In one file, I have three columns with a '|' separated data. In another file, I have only one column. The example dataset is below:

File1

CO2|44|Carbondioxide
oxygen|16|02
sulfate|96|so4

File2

co2
sulfate
NO2
so4
o2

I want to create the 2nd column in file2. And I want to search each data of file2 in file1's column 1 and column3. . If matches then print respective column 2 value of file1 to respective rows in file2. If no matches are found, keep all the rows as it is in file2. The order of the rows should be the same as the original file2. So, my output will look like following:

column1           column2
co2                44
sulfate            96
NO2
SO4                 96
O2                   16

So, far I did following:

  import pandas as pd
df1 = pd.read_csv ('file1.txt', sep='|', header=None)
df1.columns = ['pollutant1', 'mass', 'pollutant2']
df2 = pd.read_csv ('file2.txt', header=None)
df2.columns = ['pollutant']
df2["NewColumn"]= " "

I don't understand how to search file2 in file1 specific columns. Any help would be greatly appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source