I want to write a pandas dataframe to excel where certain columns are merged. I know it is possible to merge rows like so:

df = pd.DataFrame({"animal": ("horse", "horse", "dog", "dog"), "color": ("black", "white", "grey", "black")})

df.to_excel("some_path_here")

But I'm unsure how to create output like the following, where columns are merged:

I have tried this:

df = pd.DataFrame({"animal": "color of fur", "horse": "black", "horse": "white", "dog": "grey", "dog": "black"})

df.to_excel("some_path_here")

But that doesn't work because the keys will overwrite each other. Any tips?

(this example isn't the most logical but I will use it differently in my project)

CodePudding user response：

To create transposed output as described, try a 2-stage process to first tranpose the data in Pandas then use

You can further tweak and customize the Excel output using the openpyxl API.