dataframe_to_rows() misalignment on multiindex
I am getting an unexpected result on dataframe_to_rows() when the multi-indexed rows have variable numbers of children. Reproduction: ``` import pandas as pd import numpy as np from openpyxl.utils.dataframe import dataframe_to_rows df = pd.DataFrame([ ['2022', '11', 'Group1', 'Category1', '1', '4' ], ['2022', '12', 'Group1', 'Category1', '1', '4' ], ['2023', '1' , 'Group1', 'Category1', '1', '4' ], ['2023', '2' , 'Group1', 'Category1', '1', '4' ], ['2023', '3' , 'Group1', 'Category1', '1', '4' ], ['2023', '4' , 'Group1', 'Category1', '1', '4' ], ['2023', '5' , 'Group1', 'Category1', '1', '4' ], ['2023', '6' , 'Group1', 'Category1', '1', '4' ], ['2023', '7' , 'Group1', 'Category1', '1', '4' ], ['2023', '8' , 'Group1', 'Category1', '1', '4' ], ['2023', '9' , 'Group1', 'Category1', '1', '4' ], ['2023', '10', 'Group1', 'Category1', '1', '4' ], ['2023', '11', 'Group1', 'Category1', '1', '4' ], ['2023', '12', 'Group1', 'Category1', '1', '4' ], ['2024', '1' , 'Group1', 'Category1', '1', '4' ], ['2024', '2' , 'Group1', 'Category1', '1', '4' ], ['2022', '11', 'Group2', 'Category1', '2', '3' ], ['2022', '12', 'Group2', 'Category1', '2', '3' ], ['2023', '1' , 'Group2', 'Category1', '2', '3' ], ['2023', '2' , 'Group2', 'Category1', '2', '3' ], ['2023', '3' , 'Group2', 'Category1', '2', '3' ], ['2023', '4' , 'Group2', 'Category1', '2', '3' ], ['2023', '5' , 'Group2', 'Category1', '2', '3' ], ['2023', '6' , 'Group2', 'Category1', '2', '3' ], ['2023', '7' , 'Group2', 'Category1', '2', '3' ], ['2023', '8' , 'Group2', 'Category1', '2', '3' ], ['2023', '9' , 'Group2', 'Category1', '2', '3' ], ['2023', '10', 'Group2', 'Category1', '2', '3' ], ['2023', '11', 'Group2', 'Category1', '2', '3' ], ['2023', '12', 'Group2', 'Category1', '2', '3' ], ['2024', '1' , 'Group2', 'Category1', '2', '3' ], ['2024', '2' , 'Group2', 'Category1', '2', '3' ], ['2022', '11', 'Group1', 'Category2', '3', '2' ], ['2022', '12', 'Group1', 'Category2', '3', '2' ], ['2023', '1' , 'Group1', 'Category2', '3', '2' ], ['2023', '2' , 'Group1', 'Category2', '3', '2' ], ['2023', '3' , 'Group1', 'Category2', '3', '2' ], ['2023', '4' , 'Group1', 'Category2', '3', '2' ], ['2023', '5' , 'Group1', 'Category2', '3', '2' ], ['2023', '6' , 'Group1', 'Category2', '3', '2' ], ['2023', '7' , 'Group1', 'Category2', '3', '2' ], ['2023', '8' , 'Group1', 'Category2', '3', '2' ], ['2023', '9' , 'Group1', 'Category2', '3', '2' ], ['2023', '10', 'Group1', 'Category2', '3', '2' ], ['2023', '11', 'Group1', 'Category2', '3', '2' ], ['2023', '12', 'Group1', 'Category2', '3', '2' ], ['2024', '1' , 'Group1', 'Category2', '3', '2' ], ['2024', '2' , 'Group1', 'Category2', '3', '2' ], ['2022', '11', 'Group2', 'Category2', '4', '1' ], ['2022', '12', 'Group2', 'Category2', '4', '1' ], ['2023', '1' , 'Group2', 'Category2', '4', '1' ], ['2023', '2' , 'Group2', 'Category2', '4', '1' ], ['2023', '3' , 'Group2', 'Category2', '4', '1' ], ['2023', '4' , 'Group2', 'Category2', '4', '1' ], ['2023', '5' , 'Group2', 'Category2', '4', '1' ], ['2023', '6' , 'Group2', 'Category2', '4', '1' ], ['2023', '7' , 'Group2', 'Category2', '4', '1' ], ['2023', '8' , 'Group2', 'Category2', '4', '1' ], ['2023', '9' , 'Group2', 'Category2', '4', '1' ], ['2023', '10', 'Group2', 'Category2', '4', '1' ], ['2023', '11', 'Group2', 'Category2', '4', '1' ], ['2023', '12', 'Group2', 'Category2', '4', '1' ], ['2024', '1' , 'Group2', 'Category2', '4', '1' ], ['2024', '2' , 'Group2', 'Category2', '4', '1' ], ], columns=['Year', 'Month', 'Group', 'Category', 'Value1', 'Value2']) df=(df.groupby(['Year', 'Month', 'Group', 'Category']) .sum() .unstack(['Group', 'Category']) .reorder_levels([1,2,0], axis=1) .sort_index(axis=1) ) df.columns = df.columns.rename(['Group', 'Category', 'Field']) df2 = pd.DataFrame(dataframe_to_rows(df, index=True, header=True)) df2 ``` Result: Column 1 has 2023 in the wrong position, and 2024 is missing. ``` | | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |----|------|-------|-----------|--------|-----------|--------|-----------|--------|-----------|--------| | 0 | None | None | Group1 | None | None | None | Group2 | None | None | None | | 1 | None | None | Category1 | None | Category2 | None | Category1 | None | Category2 | None | | 2 | None | None | Value1 | Value2 | Value1 | Value2 | Value1 | Value2 | Value1 | Value2 | | 3 | Year | Month | None | None | None | None | None | None | None | None | | 4 | 2022 | 11 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 5 | None | 12 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 6 | None | 1 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 7 | None | 10 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 8 | None | 11 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 9 | None | 12 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 10 | None | 2 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 11 | None | 3 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 12 | None | 4 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 13 | None | 5 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 14 | None | 6 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 15 | None | 7 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 16 | 2023 | 8 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 17 | None | 9 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 18 | None | 1 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 19 | None | 2 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | ``` Below is my attempt to fix the issue. I am new to Python, sorry if it is a dumb solution. ``` def dedupe(arr): s = arr.copy() for col in s.T: seen = col[0] for i in range(1, len(col)): if col[i] != seen: seen = col[i] else: col[i] = None return s indexes = dedupe(np.array(list(list(r) for r in df.index))) values = df.to_numpy() headers = dedupe(np.array(list(list(v) for v in df.columns))).T column_names = np.empty([headers.shape[0], indexes.shape[1]], dtype=object) column_names[:,-1] = df.columns.names data = np.concatenate(( np.concatenate((column_names, headers), axis=1), np.concatenate((indexes, values), axis=1)), axis=0) df3 = pd.DataFrame(data) df3 ``` Result: ``` | | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |----|------|----------|-----------|--------|-----------|--------|-----------|--------|-----------|--------| | 0 | None | Group | Group1 | None | None | None | Group2 | None | None | None | | 1 | None | Category | Category1 | None | Category2 | None | Category1 | None | Category2 | None | | 2 | None | Field | Value1 | Value2 | Value1 | Value2 | Value1 | Value2 | Value1 | Value2 | | 3 | 2022 | 11 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 4 | None | 12 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 5 | 2023 | 1 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 6 | None | 10 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 7 | None | 11 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 8 | None | 12 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 9 | None | 2 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 10 | None | 3 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 11 | None | 4 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 12 | None | 5 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 13 | None | 6 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 14 | None | 7 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 15 | None | 8 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 16 | None | 9 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 17 | 2024 | 1 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | | 18 | None | 2 | 1 | 4 | 3 | 2 | 2 | 3 | 4 | 1 | ```
issue