Divide each element of a colum in a file by a number that is present in another file in Python or Pandas

I have a file (file1) that looks like this(but is thousands of lines):

75 line1 name1
72 line1 name2
36 line1 name3

I have another file (file2) that looks like this:

name12 2000
name7 1000
name2 382
name99 323
name3 888
name60 211
name1 333

I need to take all the elements from the first column of file1, find out its associated value in file2, and divide them. So for example, this is what that would look like in a new file:

name1 .225
name2 .188
name3 .0405

1 answer

  • answered 2018-03-20 19:15 Chris

    df = pd.DataFrame({'Col1':[75,72,36],'Col2':['line1','line2','line3'],'Col3':['name1','name2','name3']})
    
    df2 = pd.DataFrame({'Col3':['name12','name7','name2','name99','name3','name60','name1'],'Col1':[2000,1000,382,323,888,211,333]})
    
    df3 = pd.merge(df,df2, on='Col3',how='inner')
    
    df3['Col4'] = df3['Col1_x'] / df3['Col1_y']
    

    OUT:

        Col1_x  Col2    Col3    Col1_y  Col4
    0   75.0    line1   name1   333     0.225225
    1   72.0    line2   name2   382     0.188482
    2   36.0    line3   name3   888     0.040541