Agfdhyk

What's the best way to get all cell values based on a combination of column values?

Sample dataframe One:

  Stock                         Name  Price

0    AMD       Advanced Micro Devices    100

1     GE     General Electric Company    200

2    BAC  Bank of America Corporation    300

3   AAPL                   Apple Inc.    500

4   MSFT        Microsoft Corporation   1000

5  GOOGL                Alphabet Inc.   2000

Sample dataframe Two:

  Stock                         Name  Price

0    AMD       Advanced Micro Devices    100

1     GE     General Electric Company    200

2    BAC  Branch of America Corporation  300

3   AAPL                   Apple Inc.    500

4   MSFT        Microsoft Corporation   1000

5  GOOGL                Alphabet Inc.   2000

For example: I want to use (Stock and Name) as key columns and then compare the datasets. The goal is to print the mismatch entries between the two datasets with the Stock+Name columns used as a combination key.

I'm using Pandas/Python3.7

Sample Output:

BAC Bank of America Corporation 300 --- BAC Branch of America
Corporation 300

Perhaps, a FULL INNER JOIN using merge + query?

df1.merge(df2, on='Stock').query('Name_x != Name_y')



  Stock                       Name_x  Price_x                         Name_y  Price_y

2   BAC  Bank of America Corporation      300  Branch of America Corporation      300

Or, a slightly different solution with map, you can use to get the stock symbols:

m = df1.Stock.map(df2.set_index('Stock').Name).ne(df1.Name)

symbols = df1.loc[m, 'Stock']



print(symbols)

2    BAC

Name: Stock, dtype: object

And then access each DataFrame row by stock symbol:

df1[df1.Stock.isin(symbols)]

  Stock                         Name  Price

2   BAC  Bank of America Corporation    300



df2[df2.Stock.isin(symbols)]

  Stock                           Name  Price

2   BAC  Branch of America Corporation    300

If they are in two dataframes, merging them without condition is pretty straightforward with .concat. Once they are joined, here's one way to get the mismatch:

import pandas as pd



df1 = pd.DataFrame({

    "Ticker_y": list("qwerty"),

    "Name_y": list("asdfgh"),

    "Ticker_x": list("qw3r7y"),

    "Name_x": list("as6f8h")

})



mismatch = df1[(df1["Ticker_y"] != df1["Ticker_x"]) & (df1["Name_y"] != df1["Name_x"])]

The last line just says "the df only where these conditions are met."

We can use isin using the sequence of values to test as it ensures each element in the DataFrame is contained in values

First DataFrame

>>> df1

   Stock                         Name  Price

0    AMD       Advanced Micro Devices    100

1     GE     General Electric Company    200

2    BAC  Bank of America Corporation    300

3   APPL                   Apple Inc.    500

4   MSFT        Microsoft Corporation   1000

5  GOOGL                Alphabet Inc.   2000

Second DataFrame

>>> df2

   Stock                           Name  Price

0    AMD         Advanced Micro Devices    100

1     GE       General Electric Company    200

2    BAC  Branch of America Corporation    300

3   APPL                     Apple Inc.    500

4   MSFT          Microsoft Corporation   1000

5  GOOGL                  Alphabet Inc.   2000

Here you can go..

>>> df2[~df2.Name.isin(df1.Name.values)]

  Stock                           Name  Price

2   BAC  Branch of America Corporation    300

OR

>>> df1[~df1.Name.isin(df2.Name.values)]

  Stock                         Name  Price

2   BAC  Bank of America Corporation    300