I need to turn a .csv table that includes nested tables with their own column headers into a normalized structure where nested column headers are brought out into a standard column. As an example, I need the original csv with first row headers shown below: ID1,ID2,Result1,Result2,Result3 US,PA,5,, US,FL,3,, US,CA,Year,Value1,Value2 US,CA,1997,1,2 US,CA,1999,3,4 US,CA,2000,5,6 FR,,9,, UK,,Color,Num1,Num2 UK,,Red,10%,11% UK,,Blue,13%,2% UK,,Green,5%,6% Transformed into the one below where the nested column headers (here simply shown as the first row in a specific ID1/ID2 combination subtable) are extracted into a new column as shown: ID1,ID2,Result1,Result2,Result3,Nested_Col_Header US,PA,5,,, US,FL,3,,, US,CA,Year,Value1,Value2 US,CA,1997,,,Year US,CA,1999,,,Year US,CA,2000,,,Year US,CA,,1,,Value1 US,CA,,3,,Value1 US,CA,,5,,Value1 US,CA,,,2,Value2 US,CA,,,4,Value2 US,CA,,,6,Value2 FR,,9,,, UK,,Color,Num1,Num2 UK,,Red,,,Color UK,,Blue,,,Color UK,,Green,,,Color UK,,,10%,,Num1 UK,,,13%,,Num1 UK,,,5%,,Num1 UK,,,,11%,Num2 UK,,,,2%,Num2 UK,,,,6%,Num2 I'm bringing the csv into pandas as a dataframe with the first row becoming the column names in the dataframe (using read_csv with no parameters other than the csv file name). I can segment out the subtables based on the ID1/ID2 combination for the additional processing and rebuild a new table appending the various subtables together, but I can't solve for how to pull the nested column headers out and lengthen the table. I've tried combinations of stack and melt functions with multiIndex (one of which I suspect is the right answer), but I'm not sufficiently familiar with pandas to know how to bring the pieces together. I know I could brute force this with some nasty loops, but I expect there is a clean piece of code that could resolve this very easily. Continue reading...
