Merging columns with duplicate headers?

Preface:
I dont mind using Excel, Python, or I can attempt to learn SQL. Or anything else that you suggest I can attempt to learn!
Issue:
I'm trying to merge multiple columns that have identical headers. I hate that this is a problem but one of my partners provides data to us - but their spreadsheets have duplicate columns created. Their database generates duplicate columns and I'm trying to merge these columns - keeping the value that is present in any of the columns. If I can join with ", " that works too (Shop 4 example).

Example Table:

Store Section Part Section Part Section Part Color Color Color
Ron's Shop 1 Floorboard Grey
Ron's Shop 2 Floorboard Grey
Ron's Shop 3 Piping Brown
Ron's Shop 4 Floorboard Piping Brown Grey

Example Output Desired:

Store Section Part Color
Ron's Shop 1 Floorboard Grey
Ron's Shop 2 Floorboard Grey
Ron's Shop 3 Piping Brown
Ron's Shop 4 Floorboard, Piping Brown, Grey

How do I go about accomplishing this task across MULTIPLE columns. I have around ... 200 columns of duplicates and I'm trying to join all of them. There are maybe 20ish column variants. Any direction would be great. Trying to wrap my head around the process.



Read more here: https://stackoverflow.com/questions/66270214/merging-columns-with-duplicate-headers

Content Attribution

This content was originally published by Big Secret at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: