I usually do my data cleaning in python but the issue with python (pandas) is that when you read and print a table to excel it doesn't retain any of the excel formatting.
In this case I was given a large table where a lot of the cells are color coded and or commented. I need to retain all the coloring, comments, font styles and etc. I don't know how else to do that but to work in excel
The issue:
In one sheet I have a large table (400 rows x 45 columns). It is structured like below
Sheet 1:
|ID|C|D|E|F|
:--|:--|:--|:--|:--|
|EDMU025|1|2|3|4|
|EDMU026|5|6|7|8|
|EDMU027|9|2|3|4|
|EDMU028|5|6|7|8|
In another sheet I have a series of small tables which look like this
Sheet 2:
|ID|Date|C|D|E|F|
:--|:--|:--|:--|:--|:--|
|EDMU025|9/14/22|100|210|300|450|
|EDMU025|9/14/22|100|200|340|400|
|||||||
|Value to be replaced||100|200|300|400|
|||||||
|EDMU028|9/14/22|700|810|900|550|
|EDMU028|9/14/22|700|800|940|500|
|||||||
|Value to be replaced||700|800|900|500|
For each ID in Sheet 2 I need to find the ID in Sheet 1 and replace the values in sheet 1 columns C-F with the Values to be replaced.
The output would be:
|ID|C|D|E|F|
:--|:--|:--|:--|:--|
|EDMU025|100|200|300|400|
|EDMU026|5|6|7|8|
|EDMU027|9|2|3|4|
|EDMU028|700|800|900|500|
What is the most efficient way to do that for the entire table (while still keeping the original values that don't need to be replaced intact?)??
