title: Reading strings from a csv file and finding the labels of the corresponding strings in another csv in Pandas
body: <p>I have two csv files, one very large with thousands of rows and the other one has a normal size. I have a column in each csv file that contains the name of a certain product, which I call ProductName. The large csv contains the name of all the products in one column and the label of those products in another column. The smaller csv file contains some of the products names of the larger csv and some of the names that do not exist there. What I want to do is to read every row of the ProductName column in the smaller csv file and check whether I can find the same name in the ProductName column in the large csv. If, the match is found in the larger csv, I need to copy the content of the label column of the corresponding product in the large csv file and save it in a new column in the smaller csv. I'm using pandas and I could get what I was looking for. Here is my code:</p>
  
  <pre><code>import pandas as pd
  
  df=pd.read_csv('Products.csv')         #small csv file
  df2=pd.read_csv('ProductsMain.csv')    #large csv file
  rowCounter=0
  for name in (df['ProductName']):
  
      nameCounter=df2.ProductName.str.contains(name).sum()
      if nameCounter&gt;0:  # only checking for the product label if it exists in the larger csv
         rowNum=df2[df2['ProductName']==name].index[0]
         label=df2.iloc[rowNum,-1]  #Label column is the last column in df2
         df.set_value(rowCounter,'Label',label)
         df.to_csv('Products.csv',index=False)
      rowCounter +=1
  </code></pre>
  
  <p>I have two questions here: first, is there a better way to do this. In particular, when the size of csv file is very large, I'm not sure if this is the best way (in terms of speed) to find the matched name in the larger csv file. Second, what if I don't know the location of the label column and I want to call it by name and the index of row, since iloc doesn't work with names and numbers together. I mean, I cannot use <code>df2.iloc[rowNum,'label']</code>, but I like to know some way to do this.</p>
  
  <p><strong>Edit</strong>: Please take a look at the this example, if the description above is not clear enough. Let's say I have two csv files as follows:</p>
  
  <pre><code>ProductsMain.csv:                         
  
  ProductName   0  1  2  3  Label
  X1            29 74 30 60   0
  X2            18 25 84 70   0
  X3            10 45 72 43   1
  X4            35 70 65 39   0
  Y1            14 35 80 58   2
  Y2            25 65 40 30   2
  Y3            40 60 18 90   2
  Y4            10 20 35 70   1
  
  
  Products.csv:
  
  ProductName   0  1  2  3  
  X2            18 25 84 70
  Y1            14 35 80 58
  Y5            19 37 49 75
  X1            29 74 30 60
  </code></pre>
  
  <p>After running the code:</p>
  
  <p>Products.csv:</p>
  
  <pre><code>ProductName   0  1  2  3  Label
  X2            18 25 84 70   0
  Y1            14 35 80 58   2
  Y5            19 37 49 75   
  X1            29 74 30 60   0
  </code></pre>
  
  <p>In other words, first I check for the products name in Products.csv, if I can find the matching name in ProductsMain.csv, I will find the corresponding label of that product and save it in a new column (which is called <code>Label</code>) in the Products.csv, if the name doesn't exist in ProductsMain.csv, I don't do anything, continuing to the next productName in the Products.csv, until I reach the end of Products.csv.</p>
  
  <p><strong>Edit2:</strong> I also figured out I can use ix instead of iloc to reach cells by name and index: <code>label=df2.ix[rowNum,'label']</code></p>