2

I need to automate the validations performed on text file. I have two text files and I need to check if the row in one file having unique combination of two columns is present in other text file having same combination of columns then the new column in text file two needs to be written in text file one.

The text file 1 has thousands of records and text file 2 is considered as reference to text file 1.

As of now I have written the following code. Please help me to solve this.

import pandas as pd

data=pd.read_csv("C:\\Users\\hp\\Desktop\\py\\sample2.txt",delimiter=',')
df=pd.DataFrame(data)
print(df)

# uniquecal=df[['vehicle_Brought_City','Vehicle_Brand']]
# print(uniquecal)

data1=pd.read_csv("C:\\Users\\hp\\Desktop\\py\\sample1.txt",delimiter=',')
df1=pd.DataFrame(data1)
print(df1)

# uniquecal1=df1[['vehicle_Brought_City','Vehicle_Brand']]
# print(uniquecal1

How can I put the vehicle price in dataframe one and save it to text file1?

Below is my sample dataset:

File1:

   fname lname vehicle_Brought_City Vehicle_Brand  Vehicle_price
0   aaa   xxx                 pune         honda            NaN
1   aaa   yyy               mumbai           tvs            NaN
2   aaa   xxx                  hyd        maruti            NaN
3   bbb   xxx                 pune         honda            NaN
4   bbb   aaa               mumbai           tvs            NaN

File2:

  vehicle_Brought_City Vehicle_Brand  Vehicle_price
0                 pune         honda          50000
1               mumbai           tvs          40000
2                  hyd        maruti          45000
petezurich
  • 9,280
  • 9
  • 43
  • 57
Saurabh
  • 79
  • 8
  • can you explain your problem by giving example? – Nihal Sep 08 '18 at 05:53
  • As I mentioned in my data set ,I have two text files. One having fname, lname, vehical_brought_city, Vehical Brand ,Vehicle Price.First text file dont have the values for vehicle price .And other text file has vehical brought city,vehicle brand nad vehical price.So what I need to do depending upon the combination of vehical brought city and vehical brand as a combination vehical price need to enter in text file 1 .Like in row 0 I want price as 5000 and for row 4 want price value as 4000. – Saurabh Sep 08 '18 at 06:29
  • ok i got your problem – Nihal Sep 08 '18 at 06:42
  • Possible duplicate of [Merge two DataFrames based on multiple keys in pandas](https://stackoverflow.com/questions/32277473/merge-two-dataframes-based-on-multiple-keys-in-pandas) – petezurich Sep 08 '18 at 06:42
  • In my actual data set file 1 has thousands of record and file 2 has only 30 records.Both file has vehical_brought_city and Vehical_Brand Basically we can say file 2 is reference file. so depending upon the combination of vehical_brought_city and Vehical_Brand I need to put value of Vehical_Price in file 1. I created a dataframe for that but I am stuck how I can iterate through different dataframe? is merge works in this case too? – Saurabh Sep 08 '18 at 06:51

1 Answers1

1
del df['Vehicle_price']
print(df)

dd = pd.merge(df, df1, on=['vehicle_Brought_City', 'Vehicle_Brand'])
print(dd)

output:

  fname lname vehicle_Brought_City Vehicle_Brand  Vehicle_price
0   aaa   xxx                 pune         honda          50000
1   aaa   yyy               mumbai           tvs          40000
2   bbb   aaa               mumbai           tvs          40000
3   aaa   xxx                  hyd        maruti          45000
Nihal
  • 5,262
  • 7
  • 23
  • 41