CODEWITHSUNDEEP
pythonpandasdataframe
Egelbets
Egelbets

Reputation: 336

Merge columns in pandas to excel

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")

row_merge

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

column_merge

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)

Upvotes: 0

Views: 3654

Answers (1)

CodeMonkey
CodeMonkey

Reputation: 23748

To create transposed output as described, try a 2-stage process to first tranpose the data in Pandas then use openpyxl to merge the cells for columns when the animal is the same. With openpyxl you can merge cells, set cell alignment, etc.

import pandas as pd

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

df1 = df.transpose()
df1.to_excel("out1.xlsx")

This creates structure:

            0      1     2      3
animal  horse  horse   dog    dog
color   black  white  grey  black

Next use openpyxl to merge the cells, set cell alignment to center, etc.

from openpyxl import load_workbook
from openpyxl.styles import colors, Font
from openpyxl.styles.alignment import Alignment

wb = load_workbook(filename="out1.xlsx", data_only=True)
ws = wb.worksheets[0]

# delete first row with numbers
ws.delete_rows(1)

last_animal = ''
font = Font(name='Arial', b=True)
for col in range(2, ws.max_column+1):
    cell = ws.cell(row=1, column=col)
    if last_animal == cell.value:
        ws.merge_cells(start_row=1, start_column=col-1, end_row=1, end_column=col)
        # set BOLD font and center alignment on cell
        cell = ws.cell(row=1, column=col-1)
        cell.font = font
        cell.alignment = Alignment(horizontal="center")
    last_animal = cell.value
wb.save('out2.xlsx')

Output:

excel output

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

Upvotes: 1

Related Questions