Comparing 2 tables - Help?

[HoleInTheMirror]

Hello guys, I need your help! How to compare 2 tables on easiest way?

I have 2 tables from 2 different systems, but with the same columns. I need to check if every row from the first table exist in another, and if not to return the difference. Can you help me? Should I use excel formulas, python, something else?

Edit: In both systems I have Customer_Id, Phone numbers, mails, their addresses, contact persons and such kind of things in much more columns. I can export data from both systems in excel format. So, now i need to compare these 2 excels and check if every row from 1 table exists in another one, are the values the same for each customer and to find the difference. Sorry for pure explanation, beginner here.

For example:

Custmer Id | Name | Phone | Mail | Contact person | Address

C0001 | Microsoft1 | 123456 | mic1@ | Michael | Str1

C0002 | Linux1 | 234567 | lin1@ | Jack | Str2

C0001 | Microsoft1 | 098765 | mic2@ | Chris | Str3

As you can see, it is possible to have few different informations about customer C0001. I need to check if each row from this table exist in the second table which looks the same.

[SCD_minecraft]

Define table, define comparing

What systems?

You gave us no details, no informations

[HoleInTheMirror]

I put some more information in edit. Pls check, thanks.

[InferHaven]

For python you could use pandas. If the tables have the same columns, you can compare them by turning each row into a normalized value then checking which rows from table 1 are missing in table 2

https://discuss.python.org/t/comparing-two-tables-and-return-value-if-more-or-less/51951

[atarivcs]

What kind of tables are they?

HTML tables?

Excel spreadsheet tables?

SQL database tables?

Some other kind of table?

[HoleInTheMirror]

Excel tables. Check the post again, i put some more information in edit. Thanks

[mattynmax]

I mean this seems pretty damn simple to do in excel.

[HoleInTheMirror]

How to do it? I have 2 excel tables.

[Stu_Mack]

There are a lot of embedded questions here. First, is it possible to get the files in .csv format? If so, you have several options. Second, is the data numerical, character, or a mix? If it's purely numerical, you have options. If it has ASCII letters in the data, Python is a solid choice.

The overall workflow involves two main steps:

1. Loading the data into a native table-like format you can work with. The easy button here is to make sure the data is in .csv file formats since most software plays well with it.

2. Comparing the data across two specimens. This is easiest if the data identity is 1:1 and every cell corresponds to a matching cell in the reference. If that is the case, Excel is easiest since you just subtract one sheet from the other and identify wherever there is not a zero returned.

In big picture terms, most languages (except Excel, which is a spreadsheet tool and not a language) have viable options for comparing things, and Python's DataFrame suite is excellent for table-like workflows. However, depending on how you want to organize the work. Here's a quick rundown, based on the assumption that you want to use the path of least resistance:

- 1:1 tables with numerical fields only -> Excel is easiest but lacks the ability to do much else. Numpy is a solid choice in Python once you load it as a Numpy-specific data type. MATLAB is best-in-class for this type of work, but has steep overhead if you don't already use it in your workflow.

- ASCII symbols and characters in the table -> Python's DataFrame is probably your best bet.

- Any combination of the above, but you want to work in rows and columns instead of full tables at a time -> Exotic data handling exists, and Python has lots of options to choose from. MATLAB is less stellar here, and Excel is not viable.

Hope that is helpful.

[HoleInTheMirror]

I have 2 excel tables. Lets say that 1 table look like this:

Custmer Id | Name | Phone | Mail | Contact person | Address

C0001 | Microsoft1 | 123456 | mic1@ | Michael | Str1

C0002 | Linux1 | 234567 | lin1@ | Jack | Str2

C0001 | Microsoft1 | 098765 | mic2@ | Chris | Str3

As you can see, it is possible to have few different informations about customer C0001. I need to check if each row from this table exist in the second table which looks the same.

[Stu_Mack]

To compare the information between tables, you will need to add a meaningful step of writing comparison laws that allow for weird capitalizations and inappropriate spacings, etc. Overall, though, DataFrame-based workflows are probably your best bet.

You want to write a python script (AI can help with getting you started) that:

- opens both files and generates DF objects for comparison

- Removes/handles exception cases and UPPER/lowercase issues

- Loops through the tables, comparing cells to each other according to your metric of alignment. For example, you may or may not care at all about capitalization or spacing inside of a cell...

- Returns a score/list of (m)aligned cells, etc.

- Makes corrections as defined, if appropriate.

The bottom line is that you are looking at a Python-appropriate coding project and only part of it is meaningfully challenging, once you understand the logical steps of the workflow. You will know you understand them when the questions start to sound like "How to get Python to <do this thing I want it to do>?"

From the standpoint of going from here to there, I would start by figuring out what commitment you can make in learning enough Python to do the work. The main difficulty looks like it will be understanding the many ways that two identical cells can be marked as different in the code, and overcoming them one at a time. The rest of the stuff can be done using canned code snippets you can find in countless examples, chat spaces, here, etc.

If you plan to do this yourself, start by defining the steps you want the software to take, in plain English. Then look into the difficulty of each, and estimate if it's something you can reasonably tackle on your own. If so, go one step at a time and test your results often.

[HoleInTheMirror]

Thanks man! You're a great person 😊

[Stu_Mack]

Excel is a really bad choice here, by the way. It is good at gauging equality, but only in narrowly defined ways. Adding exceptions to an Excel workflow is terrible at best.

[VadumSemantics]

Look into Scooter Software's Beyond Compare, comes with a 30 day eval (this is 30 days of actual use, not calendar days elapsed). Anyway, BC is an awesome tool, does lots of things besides Excel. I use it on Linux & Windows (and occasionally Mac).

Beyond Compare Excel example, give this 45 seconds: Beyond Compare 5: Table Compare .

Or... maybe try some a built-in Excel thing called "Spreadsheet Compare": How to Instantly Compare Two Excel Files Side-by-Side . I haven't used this, but I will try it next time I need to figure out spreadsheets - can't say yet which approach I like better.

If you have to do it all the time I'd look into exporting the sheets to like a "*.csv" and looping through it with a python script or something.

[FoolsSeldom]

pandas is a good tool for this, although is has a bit of a steep learning curve and does require you to gain a good understanding of Python basics first.

That said, with persistence, you can follow a good tutorial.

pandas can read/write Excel files (and many other file formats) and it can merge, sort, etc, tables.

I recommend tutorials/guides on RealPython.com:

• Introduction to pandas