'Merge 2 excel's workbook with a common column in python
the main purpose is to merge 2 excel's workbook thanks to a specific column which is similar.
On one side I have this excel workbook :
On the other side I have this excel workbook :
As you can see there is a common column who's called "N° docConstructeur".
The result should be like this :
To arrive at those results I used differents librairies like "openpyxl" or "pandas". But currently after some researchs on websites I don't know how I could code this.
I saw for example with pandas the function "merge" but it doesn't work for me.
I have an idea who is to split a cell in multiple cells with the same id(column N°1) in front of each cell. But I don't know do this in python.
For example : Initial sheet
to : final sheet
Thank's a lot for your help, lucas
Solution 1:[1]
The common column has the same header name and field values in a similar format however the values in the Second Excel Workbook don't necessarily match up to those in the First Excel Workbook. Seems there are no common 'N° docConstructeur' values in the two example workbooks given.
If they did match a copy/paste from second to first workbooks would fail as the second workbook values are not sequential and the second book has multiple 'N° docConstructeur' values for some 'N° IDENTIFIANT_CLIENT' values. Whereas in the first book it appears they are in sequence and one for one.
There also seems to be duplicate entries in the second book, like ADD0002707133 appears 7 times so has multiple 'N° IDENTIFIANT_CLIENT' values in the example.
I'd suggest an option would be to read the second book 'N° docConstructeur' (as key) and 'N° IDENTIFIANT_CLIENT' (as value) into a dictionary then use this a lookup to the first book 'N° docConstructeur' column, inserting the value for any match into a new created 'N° IDENTIFIANT_CLIENT' column. However you'd have to determine how to manage the case where the second book has multiple 'N° IDENTIFIANT_CLIENT' values for the 'N° docConstructeur'
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | moken |