How to merge multiple raw input CSV's with pandas containing similar columns with slightly different names?
The short answer is no, as your asking the computer to think for itself. You do however have multiple options to deal with common scenarios.
If the column order and/or positions are fixed you can make use of the header=0, names=['ID', 'Instance'] to ignore the headers sent in the file and make use of known data
You can also generate a config file that maps all possible wrong header names to the right one
I wrote some code to combine multiple CSV's that are interpretered with Pandas and appended to one combined CSV.
The issue I have is that the CSV files are delivered by multiple parties (monthly) and often contain differences with regard to column names, while they essentially contain the same information. For instance:
CSV 1
| ID | Instance number |
| -------- | -------------- |
| 1   | 401421           |
| 2   | 420138           |
CSV 2
| ID | Instance NO |
| -------- | -------------- |
| 1    | 482012           |
| 2   | 465921            |
This will result in two columns in the combined file, Instance Number & Instance NO unless I rename the column beforehand while the idea is to automatically process all files without intervention beforehand.
The solution that should work is to use combine_first or fillna, but next time the column may be entered as e.g. Instance No/number.
Since improving data delivery isn't an option, is there any smart way to solve issues like this without having to write out all possible variations and remap them to one leading column?
Thanks in advance!