Source code

Download source archive ↓

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