So say I have a table with the headers as something like this:
|age| |gender| |xxxxxx| |xxxxxxxxxxxxxxxxxxxxxxxx|
|xxx| |xxxxxx| |canada| |russia| |malta| |fiji|
What I wanted was to set the new dataframe to have the country names on the second row as the headers for those columns, and to keep age and gender as the headers for their columns. The amount of countries and the names of the countries are not consistent so I cannot just hard code swapping the data.
I also can't remove any of the non-header related labels (or at least I have to save them to put back at the end for formatting), so if I would pretty much need to swap the second half of the second row with the first half of the first row, but some of the cells in the first row also take up multiple cells on the second row.
What I am currently doing is running pd.read_excel() with the header option set to 1 (there is a block of text above all the headers), but getting the first row with iloc[0] is making me unsure of what to do because it returns something like this with unnameds:
age: xxx
gender: xxx
xxxxx: canada
xxxxx: russia
Unnamed 1: malta
Unnamed 2: fiji
and this is what I would be looking for:
age: xxxx
gender: xxxx
canada: xxxx
russia: xxxx
malta: Unnamed 1
fiji: Unnamed 2
Any help would be greatly appreciated!