Wow, I am always amazed by what @jrus and others can do with just a few lines of code!
I am constantly working with table data that has nested headers, and it’s always a pain. Earlier I floated an idea of trying to develop some sort of tool that could be trained to interpret nested headers, and to return unique ‘nested keys’, as your observations accords with my own experience, @davidjayfrancis - there really aren’t many published ways for doing this ‘automatically’.
What I was after was something like this (using your example):
race, , , age, occupation
black, white, asian, response, response
“”,“white”,"", 20, “engineer”
“black”, “white”, “”, 25, “doctor”
race_black, race_white, race_asian, age, occupation
"", "white", "", "20", "engineer"
After a lot of research, I’ve concluded that this is a very, very difficult problem to solve. I have therefore taken to ripping out all my data headers and manually re-writing them, as it’s the fastest way for me to solve the problem. Sorry that this may not be helpful! I just wanted to let you know you’re not alone in this pursuit!
… and i hope this is taken as friendly, and i don’t know your data source or personal views, but as I was typing out your example, I couldn’t help reacting to the ‘race’ categorization… as ‘race’ is a very contested construct. In case you’re interested: