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))
df2Result: 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)
df3Result:
| | 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 |Edited by gt kk