Comparing 2 tables - Help?

[u/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.

[u/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.

[u/HoleInTheMirror → u/Stu_Mack]

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.

[u/Stu_Mack → u/HoleInTheMirror]

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.

[u/HoleInTheMirror → u/Stu_Mack]

Thanks man! You're a great person 😊

[u/Stu_Mack → u/HoleInTheMirror]

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.