CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Rahul Sharma
Rahul Sharma

Reputation: 2495

Compare columns of two dataframes and create a new dataframe

I have two different dataframes and i want to compare some columns for every row in df A

Dataframe A:

M_ID From To M_Type    T_Type  T_Length T_Weight #Trucks Loading_Time
1025 A    B  Boxes     Open    12-Tyre  22       3       27-March-2019 6:00PM
1029 C    D  Cylinders Trailer High     23       2       28-March-2019 6:00PM
1989 G    H  Scrap     Open    14-Tyre  25       5       26-March-2019 9:00PM

Dataframe B

 T_ID From To T_Type  T_Length T_Weight #Trucks  Price
6569  A    B  Open    12-Tyre  22       5        1500
8658  G    H  Open    14-Tyre  25       4        1800
4595  A    B  Open    12-Tyre  22       3        1400
1252  A    B  Trailer Low      28       5        2000
7754  C    D  Trailer High     23       4        1900
3632  G    H  Open    14-Tyre  25       10       2000
6521  C    D  Trailer High     23       8        1700
8971  C    D  Open    12-Tyre  22       8        1200
4862  G    H  Trailer High     25       15       2200

I want to compare certain columns of A and B i.e "From, To, T_Type, T_length, T_Weight, #Trucks"

"From, To, T_Type, T_length, T_Weight" of both dataframes has to be equal but B[#Trucks]>=A[#Trucks] and when this condition is true it should sort the matches by price and create a new dataframe with M_ID and T_ID like this

Datframe Results

Manufacturer   Best_match  Second_best_match 
1025           4595        6569
1029           6521        7754
1989           3632         -

Upvotes: 2

Views: 1890

Answers (2)

Frenchy
Frenchy

Reputation: 17037

you could try:

dfc = pd.merge(dfa, dfb, on=['From', 'To', 'T_Type', 'T_Length', 'T_Weight'], how='inner')

dfc.drop(['From', 'To', 'M_Type', 'T_Weight', 'T_Length', 'Loading_Time', 'T_Type'], axis = 1,inplace=True)
dfc = dfc[dfc['#Trucks_y'] >= dfc['#Trucks_x']].drop(['#Trucks_y', '#Trucks_x'], axis=1)
dfc.rename(columns={"M_ID": "Manufacturer", "T_ID": "BestMatches"}, inplace=True)
dfc = dfc.groupby(['Manufacturer', 'Price'])['BestMatches'].agg('first').reset_index().drop(['Price'], axis = 1)

dfc = dfc.groupby(['Manufacturer'])['BestMatches'].agg(list).reset_index()
dfd = dfc['BestMatches'].apply(pd.Series)
dfc.drop(["BestMatches"],axis = 1,inplace = True)
dfc = dfc.join(dfd).fillna('-')

print(dfc)

output:

   Manufacturer       0       1
0          1025  4595.0  6569.0
1          1029  6521.0  7754.0
2          1989  3632.0       -

Upvotes: 2

Matthijs990
Matthijs990

Reputation: 629

If you want to check equals values on a certain column let's say Name you can merge both Dataframes to a new one: 

mergedStuff = pd.merge(df1, df2, on=['Name'], how='inner')
mergedStuff.head()

I think this is more efficient and faster then whereif you have a big data set

and if you want to get the differences you can do something like this:

This approach, df1 != df2, works only for dataframes with identical rows and columns. In fact, all dataframes axes are compared with _indexed_same method, and exception is raised if differences found, even in columns/indices order.

If I got you right, you want not to find changes, but symmetric difference. For that, one approach might be concatenate dataframes:

>>> df = pd.concat([df1, df2])
>>> df = df.reset_index(drop=True)

group by 

>>> df_gpby = df.groupby(list(df.columns))

get index of unique records

>>> idx = [x[0] for x in df_gpby.groups.values() if len(x) == 1]

Upvotes: 0

Related Questions