Comparing Row Values In Different Columns Using Dictionary

Question

I have two columns that I am trying to iterate through and compare the data of the Pin column to each separate number in the Adj. column and throw a flag if they match. Row 0 Pin only has to compare with each number in row 0 Adj.. I have created a dictionary in which each number corresponds to a color, with several numbers being the same color. If the "color" in the Pin column matches the "color" in the Adj. column I want make a basic print statement. The issue that I am having is using the dictionary to compare the values.

Table
Sample Spreadsheets Being Used

import pandas as pd


excel_file = r'C:\Users\449287\Documents\Sample.adjacent.xlsx'
excel_file1 = r'C:\Users\449287\Documents\Sample.Pin.Out.xlsx'

df = pd.read_excel(excel_file, sheetname='Sheet1')
df1 = pd.read_excel(excel_file1, sheetname='Sheet1')


df1d = {1: "R",2: "O",3: "Y", 4: "GR", 5: "L", 6: "R", 7: "B", 8: "L", 9: "GR", 10: "O"}
"""
df1.set_index('Pin')['Color'].to_dict()
"""
df['Adj.'] = df['Adj.'].str.replace(',', ' ')

pmacinec · Accepted Answer · 2019-01-24 21:02:53Z

1

At first, you will need a code for comparing values from 'Adj.' column with values for same color. You can put it in function:

def check_color(dictionary, value, adj_values):
    for x in adj_values.split():
        if int(x) in [k for k, v in dictionary.items() if v == dictionary[value]]:
            print('Your statement')
            return

Now, you have to just iterate over rows and for each row, call function above for parameters (dictionary that you have already declared, value from 'Pin' column and value from 'Adj.' column):

for index, row in df.iterrows():
    check_color(df1d, row['Pin'], row['Adj.'])

Hope that this helps. If you will have any questions, feel free to ask.

answered Jan 24, 2019 at 21:02

pmacinec

261 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MaxB Over a year ago

Thank you it makes sense now, I had completely hit a wall and could not figure out what to do with the dictionary stuff.

MaxB Over a year ago

Is it possible to do this if the dictionary key is now a tuple? The other cells are tuples as well. i.e.---> ('C137' , 1)

pmacinec Over a year ago

Of course, it is. But what you need to achieve? If you want the same, just change [k[1] ... instead of just k in list comprehension, so the final code will look like: [k[1] for k, v in dictionary.items() ... That means, you want just the second value of tuple to be in list.

Collectives™ on Stack Overflow

Comparing Row Values In Different Columns Using Dictionary

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related