Comparing two tables and return value if more or less

Python Help
1

Hello all. I’m new to the community as well as python.
May I ask if there’s any discussion to compare two tables (one is reference table and one is table to-compare) or someone that can help with where we read the value from to-compare table and lookup the value in reference table and compare whether it is exceed the value in reference table or not.

And example would be like this

Table 1 (Reference Table)
column1 column2 column3
A 1 2
B 1 3
C 2 2
D 4 1

Table 2 (To-compare table)
column0 column1 column2 column3
AA A 0.1 1
BB B 0.1 2
CC C 2 1
DD D 3 2

The compare table will compare the value in from table 2 referring from table 1 and return yes [when exceed] or no [when not exceed]

The output is expected to be like this

column0 column1 column2 column3
AA A no no
BB B no no
CC C no no
DD D no yes

Let me know if the question is not clear

2

Should columns 0 and 1 not be compared? Only columns 2 and 3?

I would use pandas, both for parsing the tables from whatever format they are stored as, and for comparison.

Let’s say the tables are stored in CSV files (with whitespace delimitation in this example):

reference.csv

column1 column2 column3
A 1 2
B 1 3
C 2 2
D 4 1

to_compare.csv

column0 column1 column2 column3
AA A 0.1 1
BB B 0.1 2
CC C 2 1
DD D 3 2

Then you can read these files and compare them like so:

import pandas as pd

reference = pd.read_csv("reference.csv", delim_whitespace=True)
to_compare = pd.read_csv("to_compare.csv", delim_whitespace=True)
output = to_compare.copy()
compare_cols = ["column2", "column3"]
output[compare_cols] = reference[compare_cols] < to_compare[compare_cols]
output[compare_cols] = output[compare_cols].applymap(lambda x: "yes" if x else "no")
print(output.to_string(index=False))

Output:

column0 column1 column2 column3
     AA       A      no      no
     BB       B      no      no
     CC       C      no      no
     DD       D      no     yes
1 Like
3

Hello Alexander,

Yes exactly. Column 0 and column 1 is not for comparison. However, for column 1, it will the reference row for the comparison. From here the code need to find the column name and value in column 1 to do the comparison.

Thank you for your response. At least from this point I can understand and will modify accordingly to my need. :slight_smile:

1 Like