CODEWITHSUNDEEP
pythonexcelpandas
titooooo27
titooooo27

Reputation: 33

Merging rows with pandas

I am trying to make a schedule in excel using pandas. I currently have the times of the day as the index in 15 min time frames and have the class that occupies those classes printed in each cell. I want to be able to merge all the rows that have duplicated classes into one large cell but am lost as to how to do that.

How schedule looks as pandas dataframe: pandas dataframe

How schedule prints in excel: current excel schedule

How I want the schedule to look like in excel: dream excel schedule

Is there a way to make this happen?

Upvotes: 0

Views: 805

Answers (2)

marphlap
marphlap

Reputation: 381

I am sure there is a better way, but here is what I came up with:

# create excel workbook from Pandas DataFrame
df.to_excel('output.xlsx', engine='xlsxwriter')
wb = load_workbook(filename='output.xlsx')
ws = wb.active

value = ''
same_values = []  # list to hold values that are the same
count = 0
for row in ws.iter_cols(): # iterate over each column
    for cell in row: # iterate over each row in  column
        if count == 0:  # this if block only get's used once in the beginning
            value = cell.value
            count += 1
            same_values.append(cell.coordinate)
        else:
            if cell.value == ws[same_values[0]].value:  # check if cell value is the same as what is in the same_value list
                if cell.value is None:  # if the cell.value is none skip
                    continue
                same_values.append(cell.coordinate)  # if the cell value is the same as what is in the same_value list then append the cell coordinate
            else:  # if the cell value is different than what is in the same_value list then merge everything in the same_value list
                if len(same_values) > 1:
                    print(same_values)
                    ws.merge_cells(f'{same_values[0]}:{same_values[-1]}')  # the same_value list only stores coordinates i.e ['B2', 'B3', 'B4'] so the merge_cells function takes a range of cells. So we are passing the coordinates of the first instance which is the first value (same_values[0]) of the same_value list and the last instance which is the last value (same_values[-1]) of the same_value list.
                same_values = [cell.coordinate]
            value = cell.value

wb.save('merged_output.xlsx')

Upvotes: 1

hirano_u
hirano_u

Reputation: 26

Perhaps you are converting a Pandas dataframe into an excel file using xlsxwriter?

Looks pretty similar to this post: Merge rows based on value (pandas to excel - xlsxwriter)

Upvotes: 0

Related Questions