First time poster, I am looking to build a project where I am essentially comparing 2 excel files and in a specific column of each file, I compare the values. If the Value Matches, The program will print entire row from File 1 and a column(s) (of the corresponding matched value column) of file 2 into a new file.
Here is an example: (Image in case the Tables are not showing as intended)
File 1:
| Column A | Column B | Column C |
|---|---|---|
| Hello | Cell 1 | KM 123 |
| Hi | Cell 2 | KM 678 |
| ABC | Cell 3 | KM 333 |
| BCS | Cell 4 | KM 444 |
| XYZ | Cell 5 | KM 555 |
| WOW | Cell 6 | KM 666 |
File 2:
| Column A | Column AB |
|---|---|
| Hello | Cell 7 |
| Yt | Cell 8 |
| GTA | Cell 9 |
| BCS | Cell 10 |
| 69 | Cell 11 |
| WOW | Cell 12 |
So the output would be:
| Column A | Column B | Column C | Column AB |
|---|---|---|---|
| Hello | Cell 1 | KM 123 | Cell 7 |
| BCS | Cell 4 | KM 444 | Cell 10 |
| WOW | Cell 6 | KM 666 | Cell 12 |
As you can see the output file merges both files but only if the data in Column A matches.
I am looking to build a project from scratch with almost 0 experience in Python. I have been really interested in learning it and believe, this is how I will be able to jumpstart my learning process (I tend to be bad at self-learning without something like this). So, I am not looking for a 100% solution, but instead a guideline on the things that I could do to make this project. (Also I am not creative enough to have better data in the tables lol)
I know the first step is to breakdown a problem, so this is my best try at it to provide a little more insight:
1. Import Excel Files from panda
2. Ask for input to specify which file
3. Let File one be work orders and File 2 be with KM posts
4. Ask for inputs on what columns to compare. In this specific case, **sign ID** column.
5. Python imports both files. Each file has a **Sign ID** column
6. Python compares **Sign ID** column in each file.
7. If a *sign ID* is present in both files, Python prints: File One and includes KM posts and Condition rating from File 2 for those *specific Sign IDs*.
8. Python generates an excel file or a table that can be imported into an excel file for aforementioned output.
9. Program can be made into an executable file for ease of use by other teammates
I know a simple workaround is to have 2 sheets in the same file, compare the values and highlight the matched, then filter the matched values but I want to use this challenge as a way to learn a new skill that I have wanted to learn for some time.
