src/openpyxl/utils/dataframe.py
1# Copyright (c) 2010-2024 openpyxl
2
3from itertools import accumulate
4import operator
5import numpy
6from openpyxl.compat.product import prod
7
8
9def dataframe_to_rows(df, index=True, header=True):
10 """
11 Convert a Pandas dataframe into something suitable for passing into a worksheet.
12 If index is True then the index will be included, starting one row below the header.
13 If header is True then column headers will be included starting one column to the right.
14 Formatting should be done by client code.
15 """
16 from pandas import Timestamp
17
18 if header:
19 if df.columns.nlevels > 1:
20 rows = expand_index(df.columns, header)
21 else:
22 rows = [list(df.columns.values)]
23 for row in rows:
24 n = []
25 for v in row:
26 if isinstance(v, numpy.datetime64):
27 v = Timestamp(v)
28 n.append(v)
29 row = n
30 if index:
31 row = [None]*df.index.nlevels + row
32 yield row
33
34 if index:
35 yield df.index.names
36
37 expanded = ([v] for v in df.index)
38 if df.index.nlevels > 1:
39 expanded = expand_index(df.index)
40
41 # Using the expanded index is preferable to df.itertuples(index=True) so that we have 'None' inserted where applicable
42 for (df_index, row) in zip(expanded, df.itertuples(index=False)):
43 row = list(row)
44 if index:
45 row = df_index + row
46 yield row
47
48
49def expand_index(index, header=False):
50 """
51 Expand axis or column Multiindex
52 For columns use header = True
53 For axes use header = False (default)
54 """
55
56 # For each element of the index, zip the members with the previous row
57 # If the 2 elements of the zipped list do not match, we can insert the new value into the row
58 # or if an earlier member was different, all later members should be added to the row
59 values = list(index.values)
60 previous_value = [None] * len(values[0])
61 result = []
62
63 for value in values:
64 row = [None] * len(value)
65
66 # Once there's a difference in member of an index with the prior index, we need to store all subsequent members in the row
67 prior_change = False
68 for idx, (current_index_member, previous_index_member) in enumerate(zip(value, previous_value)):
69
70 if current_index_member != previous_index_member or prior_change:
71 row[idx] = current_index_member
72 prior_change = True
73
74 previous_value = value
75
76 # If this is for a row index, we're already returning a row so just yield
77 if not header:
78 yield row
79 else:
80 result.append(row)
81
82 # If it's for a header, we need to transpose to get it in row order
83 # Example: result = [['A', 'A'], [None, 'B']] -> [['A', None], ['A', 'B']]
84 if header:
85 result = numpy.array(result).transpose().tolist()
86 for row in result:
87 yield row