Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1from __future__ import annotations
3import itertools
4from typing import (
5 TYPE_CHECKING,
6 Literal,
7 cast,
8)
10import numpy as np
12from pandas._libs import lib
13from pandas.util._decorators import set_module
15from pandas.core.dtypes.cast import maybe_downcast_to_dtype
16from pandas.core.dtypes.common import (
17 is_list_like,
18 is_nested_list_like,
19 is_scalar,
20)
21from pandas.core.dtypes.dtypes import ExtensionDtype
22from pandas.core.dtypes.generic import (
23 ABCDataFrame,
24 ABCSeries,
25)
27import pandas.core.common as com
28from pandas.core.groupby import Grouper
29from pandas.core.indexes.api import (
30 Index,
31 MultiIndex,
32 get_objs_combined_axis,
33)
34from pandas.core.reshape.concat import concat
35from pandas.core.series import Series
37if TYPE_CHECKING:
38 from collections.abc import (
39 Callable,
40 Hashable,
41 )
43 from pandas._typing import (
44 AggFuncType,
45 AggFuncTypeBase,
46 AggFuncTypeDict,
47 IndexLabel,
48 SequenceNotStr,
49 )
51 from pandas import DataFrame
54@set_module("pandas")
55def pivot_table(
56 data: DataFrame,
57 values=None,
58 index=None,
59 columns=None,
60 aggfunc: AggFuncType = "mean",
61 fill_value=None,
62 margins: bool = False,
63 dropna: bool = True,
64 margins_name: Hashable = "All",
65 observed: bool = True,
66 sort: bool = True,
67 **kwargs,
68) -> DataFrame:
69 """
70 Create a spreadsheet-style pivot table as a DataFrame.
72 The levels in the pivot table will be stored in MultiIndex objects
73 (hierarchical indexes) on the index and columns of the result DataFrame.
75 Parameters
76 ----------
77 data : DataFrame
78 Input pandas DataFrame object.
79 values : list-like or scalar, optional
80 Column or columns to aggregate.
81 index : column, Grouper, array, or sequence of the previous
82 Keys to group by on the pivot table index. If a list is passed,
83 it can contain any of the other types (except list). If an array is
84 passed, it must be the same length as the data and will be used in
85 the same manner as column values.
86 columns : column, Grouper, array, or sequence of the previous
87 Keys to group by on the pivot table column. If a list is passed,
88 it can contain any of the other types (except list). If an array is
89 passed, it must be the same length as the data and will be used in
90 the same manner as column values.
91 aggfunc : function, list of functions, dict, default "mean"
92 If a list of functions is passed, the resulting pivot table will have
93 hierarchical columns whose top level are the function names
94 (inferred from the function objects themselves).
95 If a dict is passed, the key is column to aggregate and the value is
96 function or list of functions. If ``margins=True``, aggfunc will be
97 used to calculate the partial aggregates.
98 fill_value : scalar, default None
99 Value to replace missing values with (in the resulting pivot table,
100 after aggregation).
101 margins : bool, default False
102 If ``margins=True``, special ``All`` columns and rows
103 will be added with partial group aggregates across the categories
104 on the rows and columns.
105 dropna : bool, default True
106 Do not include columns whose entries are all NaN. If True,
108 * rows with an NA value in any column will be omitted before computing margins,
109 * index/column keys containing NA values will be dropped (see ``dropna``
110 parameter in :meth:``DataFrame.groupby``).
112 margins_name : str, default 'All'
113 Name of the row / column that will contain the totals
114 when margins is True.
115 observed : bool, default False
116 This only applies if any of the groupers are Categoricals.
117 If True: only show observed values for categorical groupers.
118 If False: show all values for categorical groupers.
120 .. versionchanged:: 3.0.0
122 The default value is now ``True``.
124 sort : bool, default True
125 Specifies if the result should be sorted.
127 **kwargs : dict
128 Optional keyword arguments to pass to ``aggfunc``.
130 .. versionadded:: 3.0.0
132 Returns
133 -------
134 DataFrame
135 An Excel style pivot table.
137 See Also
138 --------
139 DataFrame.pivot : Pivot without aggregation that can handle
140 non-numeric data.
141 DataFrame.melt: Unpivot a DataFrame from wide to long format,
142 optionally leaving identifiers set.
143 wide_to_long : Wide panel to long format. Less flexible but more
144 user-friendly than melt.
146 Notes
147 -----
148 Reference :ref:`the user guide <reshaping.pivot>` for more examples.
150 Examples
151 --------
152 >>> df = pd.DataFrame(
153 ... {
154 ... "A": ["foo", "foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar"],
155 ... "B": ["one", "one", "one", "two", "two", "one", "one", "two", "two"],
156 ... "C": [
157 ... "small",
158 ... "large",
159 ... "large",
160 ... "small",
161 ... "small",
162 ... "large",
163 ... "small",
164 ... "small",
165 ... "large",
166 ... ],
167 ... "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
168 ... "E": [2, 4, 5, 5, 6, 6, 8, 9, 9],
169 ... }
170 ... )
171 >>> df
172 A B C D E
173 0 foo one small 1 2
174 1 foo one large 2 4
175 2 foo one large 2 5
176 3 foo two small 3 5
177 4 foo two small 3 6
178 5 bar one large 4 6
179 6 bar one small 5 8
180 7 bar two small 6 9
181 8 bar two large 7 9
183 This first example aggregates values by taking the sum.
185 >>> table = pd.pivot_table(
186 ... df, values="D", index=["A", "B"], columns=["C"], aggfunc="sum"
187 ... )
188 >>> table
189 C large small
190 A B
191 bar one 4.0 5.0
192 two 7.0 6.0
193 foo one 4.0 1.0
194 two NaN 6.0
196 We can also fill missing values using the `fill_value` parameter.
198 >>> table = pd.pivot_table(
199 ... df, values="D", index=["A", "B"], columns=["C"], aggfunc="sum", fill_value=0
200 ... )
201 >>> table
202 C large small
203 A B
204 bar one 4 5
205 two 7 6
206 foo one 4 1
207 two 0 6
209 The next example aggregates by taking the mean across multiple columns.
211 >>> table = pd.pivot_table(
212 ... df, values=["D", "E"], index=["A", "C"], aggfunc={"D": "mean", "E": "mean"}
213 ... )
214 >>> table
215 D E
216 A C
217 bar large 5.500000 7.500000
218 small 5.500000 8.500000
219 foo large 2.000000 4.500000
220 small 2.333333 4.333333
222 We can also calculate multiple types of aggregations for any given
223 value column.
225 >>> table = pd.pivot_table(
226 ... df,
227 ... values=["D", "E"],
228 ... index=["A", "C"],
229 ... aggfunc={"D": "mean", "E": ["min", "max", "mean"]},
230 ... )
231 >>> table
232 D E
233 mean max mean min
234 A C
235 bar large 5.500000 9 7.500000 6
236 small 5.500000 9 8.500000 8
237 foo large 2.000000 5 4.500000 4
238 small 2.333333 6 4.333333 2
239 """
240 index = _convert_by(index)
241 columns = _convert_by(columns)
243 if isinstance(aggfunc, list):
244 pieces: list[DataFrame] = []
245 keys = []
246 for func in aggfunc:
247 _table = __internal_pivot_table(
248 data,
249 values=values,
250 index=index,
251 columns=columns,
252 fill_value=fill_value,
253 aggfunc=func,
254 margins=margins,
255 dropna=dropna,
256 margins_name=margins_name,
257 observed=observed,
258 sort=sort,
259 kwargs=kwargs,
260 )
261 pieces.append(_table)
262 keys.append(getattr(func, "__name__", func))
264 table = concat(pieces, keys=keys, axis=1)
265 return table.__finalize__(data, method="pivot_table")
267 table = __internal_pivot_table(
268 data,
269 values,
270 index,
271 columns,
272 aggfunc,
273 fill_value,
274 margins,
275 dropna,
276 margins_name,
277 observed,
278 sort,
279 kwargs,
280 )
281 return table.__finalize__(data, method="pivot_table")
284def __internal_pivot_table(
285 data: DataFrame,
286 values,
287 index,
288 columns,
289 aggfunc: AggFuncTypeBase | AggFuncTypeDict,
290 fill_value,
291 margins: bool,
292 dropna: bool,
293 margins_name: Hashable,
294 observed: bool,
295 sort: bool,
296 kwargs,
297) -> DataFrame:
298 """
299 Helper of :func:`pandas.pivot_table` for any non-list ``aggfunc``.
300 """
301 keys = index + columns
303 values_passed = values is not None
304 if values_passed:
305 if is_list_like(values):
306 values_multi = True
307 values = list(values)
308 else:
309 values_multi = False
310 values = [values]
312 # GH14938 Make sure value labels are in data
313 for i in values:
314 if i not in data:
315 raise KeyError(i)
317 to_filter = []
318 for x in keys + values:
319 if isinstance(x, Grouper):
320 x = x.key
321 try:
322 if x in data:
323 to_filter.append(x)
324 except TypeError:
325 pass
326 if len(to_filter) < len(data.columns):
327 data = data[to_filter]
329 else:
330 values = data.columns
331 for key in keys:
332 try:
333 values = values.drop(key)
334 except (TypeError, ValueError, KeyError):
335 pass
336 values = list(values)
338 grouped = data.groupby(keys, observed=observed, sort=sort, dropna=dropna)
339 if values_passed:
340 # GH#57876 and GH#61292
341 # mypy is not aware `grouped[values]` will always be a DataFrameGroupBy
342 grouped = grouped[values] # type: ignore[assignment]
344 agged = grouped.agg(aggfunc, **kwargs)
346 if dropna and isinstance(agged, ABCDataFrame) and len(agged.columns):
347 agged = agged.dropna(how="all")
349 table = agged
351 # GH17038, this check should only happen if index is defined (not None)
352 if table.index.nlevels > 1 and index:
353 # Related GH #17123
354 # If index_names are integers, determine whether the integers refer
355 # to the level position or name.
356 index_names = agged.index.names[: len(index)]
357 to_unstack = []
358 for i in range(len(index), len(keys)):
359 name = agged.index.names[i]
360 if name is None or name in index_names:
361 to_unstack.append(i)
362 else:
363 to_unstack.append(name)
364 table = agged.unstack(to_unstack, fill_value=fill_value)
366 if not dropna:
367 if isinstance(table.index, MultiIndex):
368 m = MultiIndex.from_product(table.index.levels, names=table.index.names)
369 table = table.reindex(m, axis=0, fill_value=fill_value)
371 if isinstance(table.columns, MultiIndex):
372 m = MultiIndex.from_product(table.columns.levels, names=table.columns.names)
373 table = table.reindex(m, axis=1, fill_value=fill_value)
375 if sort is True and isinstance(table, ABCDataFrame):
376 table = table.sort_index(axis=1)
378 if fill_value is not None:
379 table = table.fillna(fill_value)
380 if aggfunc is len and not observed and lib.is_integer(fill_value):
381 # TODO: can we avoid this? this used to be handled by
382 # downcast="infer" in fillna
383 table = table.astype(np.int64)
385 if margins:
386 if dropna:
387 data = data[data.notna().all(axis=1)]
388 table = _add_margins(
389 table,
390 data,
391 values,
392 rows=index,
393 cols=columns,
394 aggfunc=aggfunc,
395 kwargs=kwargs,
396 observed=dropna,
397 margins_name=margins_name,
398 fill_value=fill_value,
399 dropna=dropna,
400 )
402 # discard the top level
403 if values_passed and not values_multi and table.columns.nlevels > 1:
404 table.columns = table.columns.droplevel(0)
405 if len(index) == 0 and len(columns) > 0:
406 table = table.T
408 # GH 15193 Make sure empty columns are removed if dropna=True
409 if isinstance(table, ABCDataFrame) and dropna:
410 table = table.dropna(how="all", axis=1)
412 return table
415def _add_margins(
416 table: DataFrame | Series,
417 data: DataFrame,
418 values,
419 rows,
420 cols,
421 aggfunc,
422 kwargs,
423 observed: bool,
424 margins_name: Hashable = "All",
425 fill_value=None,
426 dropna: bool = True,
427):
428 if not isinstance(margins_name, str):
429 raise ValueError("margins_name argument must be a string")
431 msg = f'Conflicting name "{margins_name}" in margins'
432 for level in table.index.names:
433 if margins_name in table.index.get_level_values(level):
434 raise ValueError(msg)
436 grand_margin = _compute_grand_margin(data, values, aggfunc, kwargs, margins_name)
438 if table.ndim == 2:
439 # i.e. DataFrame
440 for level in table.columns.names[1:]:
441 if margins_name in table.columns.get_level_values(level):
442 raise ValueError(msg)
444 key: str | tuple[str, ...]
445 if len(rows) > 1:
446 key = (margins_name,) + ("",) * (len(rows) - 1)
447 else:
448 key = margins_name
450 if not values and isinstance(table, ABCSeries):
451 # If there are no values and the table is a series, then there is only
452 # one column in the data. Compute grand margin and return it.
453 return table._append_internal(
454 table._constructor({key: grand_margin[margins_name]})
455 )
457 elif values:
458 marginal_result_set = _generate_marginal_results(
459 table,
460 data,
461 values,
462 rows,
463 cols,
464 aggfunc,
465 kwargs,
466 observed,
467 margins_name,
468 dropna,
469 )
470 if not isinstance(marginal_result_set, tuple):
471 return marginal_result_set
472 result, margin_keys, row_margin = marginal_result_set
473 else:
474 # no values, and table is a DataFrame
475 assert isinstance(table, ABCDataFrame)
476 marginal_result_set = _generate_marginal_results_without_values(
477 table, data, rows, cols, aggfunc, kwargs, observed, margins_name, dropna
478 )
479 if not isinstance(marginal_result_set, tuple):
480 return marginal_result_set
481 result, margin_keys, row_margin = marginal_result_set
483 row_margin = row_margin.reindex(result.columns, fill_value=fill_value)
484 # populate grand margin
485 for k in margin_keys:
486 if isinstance(k, str):
487 row_margin[k] = grand_margin[k]
488 else:
489 row_margin[k] = grand_margin[k[0]]
491 from pandas import DataFrame
493 margin_dummy = DataFrame(row_margin, columns=Index([key])).T
495 row_names = result.index.names
496 # check the result column and leave floats
498 for dtype in set(result.dtypes):
499 if isinstance(dtype, ExtensionDtype):
500 # Can hold NA already
501 continue
503 cols = result.select_dtypes([dtype]).columns
504 margin_dummy[cols] = margin_dummy[cols].apply(
505 maybe_downcast_to_dtype, args=(dtype,)
506 )
507 result = concat([result, margin_dummy])
508 result.index.names = row_names
510 return result
513def _compute_grand_margin(
514 data: DataFrame, values, aggfunc, kwargs, margins_name: Hashable = "All"
515):
516 if values:
517 grand_margin = {}
518 for k, v in data[values].items():
519 try:
520 if isinstance(aggfunc, str):
521 grand_margin[k] = getattr(v, aggfunc)(**kwargs)
522 elif isinstance(aggfunc, dict):
523 if isinstance(aggfunc[k], str):
524 grand_margin[k] = getattr(v, aggfunc[k])(**kwargs)
525 else:
526 grand_margin[k] = aggfunc[k](v, **kwargs)
527 else:
528 grand_margin[k] = aggfunc(v, **kwargs)
529 except TypeError:
530 pass
531 return grand_margin
532 else:
533 return {margins_name: aggfunc(data.index, **kwargs)}
536def _generate_marginal_results(
537 table,
538 data: DataFrame,
539 values,
540 rows,
541 cols,
542 aggfunc,
543 kwargs,
544 observed: bool,
545 margins_name: Hashable = "All",
546 dropna: bool = True,
547):
548 margin_keys: list | Index
549 if len(cols) > 0:
550 # need to "interleave" the margins
551 table_pieces = []
552 margin_keys = []
554 def _all_key(key):
555 return (key, margins_name) + ("",) * (len(cols) - 1)
557 if len(rows) > 0:
558 margin = (
559 data[rows + values]
560 .groupby(rows, observed=observed, dropna=dropna)
561 .agg(aggfunc, **kwargs)
562 )
563 cat_axis = 1
565 for key, piece in table.T.groupby(level=0, observed=observed):
566 piece = piece.T
567 all_key = _all_key(key)
569 piece[all_key] = margin[key]
571 table_pieces.append(piece)
572 margin_keys.append(all_key)
573 else:
574 margin = (
575 data[cols[:1] + values]
576 .groupby(cols[:1], observed=observed, dropna=dropna)
577 .agg(aggfunc, **kwargs)
578 .T
579 )
581 cat_axis = 0
582 for key, piece in table.groupby(level=0, observed=observed):
583 if len(cols) > 1:
584 all_key = _all_key(key)
585 else:
586 all_key = margins_name
587 table_pieces.append(piece)
588 transformed_piece = margin[key].to_frame().T
589 if isinstance(piece.index, MultiIndex):
590 # We are adding an empty level
591 transformed_piece.index = MultiIndex.from_tuples(
592 [all_key],
593 names=[*piece.index.names, None],
594 )
595 else:
596 transformed_piece.index = Index([all_key], name=piece.index.name)
598 # append piece for margin into table_piece
599 table_pieces.append(transformed_piece)
600 margin_keys.append(all_key)
602 if not table_pieces:
603 # GH 49240
604 return table
605 else:
606 result = concat(table_pieces, axis=cat_axis)
608 if len(rows) == 0:
609 return result
610 else:
611 result = table
612 margin_keys = table.columns
614 if len(cols) > 0:
615 row_margin = (
616 data[cols + values]
617 .groupby(cols, observed=observed, dropna=dropna)
618 .agg(aggfunc, **kwargs)
619 )
620 row_margin = row_margin.stack()
622 # GH#26568. Use names instead of indices in case of numeric names
623 new_order_indices = itertools.chain([len(cols)], range(len(cols)))
624 new_order_names = [row_margin.index.names[i] for i in new_order_indices]
625 row_margin.index = row_margin.index.reorder_levels(new_order_names)
626 else:
627 row_margin = data._constructor_sliced(np.nan, index=result.columns)
629 return result, margin_keys, row_margin
632def _generate_marginal_results_without_values(
633 table: DataFrame,
634 data: DataFrame,
635 rows,
636 cols,
637 aggfunc,
638 kwargs,
639 observed: bool,
640 margins_name: Hashable = "All",
641 dropna: bool = True,
642):
643 margin_keys: list | Index
644 if len(cols) > 0:
645 # need to "interleave" the margins
646 margin_keys = []
648 def _all_key():
649 if len(cols) == 1:
650 return margins_name
651 return (margins_name,) + ("",) * (len(cols) - 1)
653 if len(rows) > 0:
654 margin = data.groupby(rows, observed=observed, dropna=dropna)[rows].apply(
655 aggfunc, **kwargs
656 )
657 all_key = _all_key()
658 table[all_key] = margin
659 result = table
660 margin_keys.append(all_key)
662 else:
663 margin = data.groupby(level=0, observed=observed, dropna=dropna).apply(
664 aggfunc, **kwargs
665 )
666 all_key = _all_key()
667 table[all_key] = margin
668 result = table
669 margin_keys.append(all_key)
670 return result
671 else:
672 result = table
673 margin_keys = table.columns
675 if len(cols):
676 row_margin = data.groupby(cols, observed=observed, dropna=dropna)[cols].apply(
677 aggfunc, **kwargs
678 )
679 else:
680 row_margin = Series(np.nan, index=result.columns)
682 return result, margin_keys, row_margin
685def _convert_by(by):
686 if by is None:
687 by = []
688 elif (
689 is_scalar(by)
690 or isinstance(by, (np.ndarray, Index, ABCSeries, Grouper))
691 or callable(by)
692 ):
693 by = [by]
694 else:
695 by = list(by)
696 return by
699@set_module("pandas")
700def pivot(
701 data: DataFrame,
702 *,
703 columns: IndexLabel,
704 index: IndexLabel | lib.NoDefault = lib.no_default,
705 values: IndexLabel | lib.NoDefault = lib.no_default,
706) -> DataFrame:
707 """
708 Return reshaped DataFrame organized by given index / column values.
710 Reshape data (produce a "pivot" table) based on column values. Uses
711 unique values from specified `index` / `columns` to form axes of the
712 resulting DataFrame. This function does not support data
713 aggregation, multiple values will result in a MultiIndex in the
714 columns. See the :ref:`User Guide <reshaping>` for more on reshaping.
716 Parameters
717 ----------
718 data : DataFrame
719 Input pandas DataFrame object.
720 columns : Hashable or a sequence of the previous
721 Column to use to make new frame's columns.
722 index : Hashable or a sequence of the previous, optional
723 Column to use to make new frame's index. If not given, uses existing index.
724 values : Hashable or a sequence of the previous, optional
725 Column(s) to use for populating new frame's values. If not
726 specified, all remaining columns will be used and the result will
727 have hierarchically indexed columns.
729 Returns
730 -------
731 DataFrame
732 Returns reshaped DataFrame.
734 Raises
735 ------
736 ValueError:
737 When there are any `index`, `columns` combinations with multiple
738 values. `DataFrame.pivot_table` when you need to aggregate.
740 See Also
741 --------
742 DataFrame.pivot_table : Generalization of pivot that can handle
743 duplicate values for one index/column pair.
744 DataFrame.unstack : Pivot based on the index values instead of a
745 column.
746 wide_to_long : Wide panel to long format. Less flexible but more
747 user-friendly than melt.
749 Notes
750 -----
751 For finer-tuned control, see hierarchical indexing documentation along
752 with the related stack/unstack methods.
754 Reference :ref:`the user guide <reshaping.pivot>` for more examples.
756 Examples
757 --------
758 >>> df = pd.DataFrame(
759 ... {
760 ... "foo": ["one", "one", "one", "two", "two", "two"],
761 ... "bar": ["A", "B", "C", "A", "B", "C"],
762 ... "baz": [1, 2, 3, 4, 5, 6],
763 ... "zoo": ["x", "y", "z", "q", "w", "t"],
764 ... }
765 ... )
766 >>> df
767 foo bar baz zoo
768 0 one A 1 x
769 1 one B 2 y
770 2 one C 3 z
771 3 two A 4 q
772 4 two B 5 w
773 5 two C 6 t
775 >>> df.pivot(index="foo", columns="bar", values="baz")
776 bar A B C
777 foo
778 one 1 2 3
779 two 4 5 6
781 >>> df.pivot(index="foo", columns="bar")["baz"]
782 bar A B C
783 foo
784 one 1 2 3
785 two 4 5 6
787 >>> df.pivot(index="foo", columns="bar", values=["baz", "zoo"])
788 baz zoo
789 bar A B C A B C
790 foo
791 one 1 2 3 x y z
792 two 4 5 6 q w t
794 You could also assign a list of column names or a list of index names.
796 >>> df = pd.DataFrame(
797 ... {
798 ... "lev1": [1, 1, 1, 2, 2, 2],
799 ... "lev2": [1, 1, 2, 1, 1, 2],
800 ... "lev3": [1, 2, 1, 2, 1, 2],
801 ... "lev4": [1, 2, 3, 4, 5, 6],
802 ... "values": [0, 1, 2, 3, 4, 5],
803 ... }
804 ... )
805 >>> df
806 lev1 lev2 lev3 lev4 values
807 0 1 1 1 1 0
808 1 1 1 2 2 1
809 2 1 2 1 3 2
810 3 2 1 2 4 3
811 4 2 1 1 5 4
812 5 2 2 2 6 5
814 >>> df.pivot(index="lev1", columns=["lev2", "lev3"], values="values")
815 lev2 1 2
816 lev3 1 2 1 2
817 lev1
818 1 0.0 1.0 2.0 NaN
819 2 4.0 3.0 NaN 5.0
821 >>> df.pivot(index=["lev1", "lev2"], columns=["lev3"], values="values")
822 lev3 1 2
823 lev1 lev2
824 1 1 0.0 1.0
825 2 2.0 NaN
826 2 1 4.0 3.0
827 2 NaN 5.0
829 A ValueError is raised if there are any duplicates.
831 >>> df = pd.DataFrame(
832 ... {
833 ... "foo": ["one", "one", "two", "two"],
834 ... "bar": ["A", "A", "B", "C"],
835 ... "baz": [1, 2, 3, 4],
836 ... }
837 ... )
838 >>> df
839 foo bar baz
840 0 one A 1
841 1 one A 2
842 2 two B 3
843 3 two C 4
845 Notice that the first two rows are the same for our `index`
846 and `columns` arguments.
848 >>> df.pivot(index="foo", columns="bar", values="baz")
849 Traceback (most recent call last):
850 ...
851 ValueError: Index contains duplicate entries, cannot reshape
852 """
853 columns_listlike = com.convert_to_list_like(columns)
855 # If columns is None we will create a MultiIndex level with None as name
856 # which might cause duplicated names because None is the default for
857 # level names
858 if any(name is None for name in data.index.names):
859 data = data.copy(deep=False)
860 data.index.names = [
861 name if name is not None else lib.no_default for name in data.index.names
862 ]
864 indexed: DataFrame | Series
865 if values is lib.no_default:
866 if index is not lib.no_default:
867 cols = com.convert_to_list_like(index)
868 else:
869 cols = []
871 append = index is lib.no_default
872 # error: Unsupported operand types for + ("List[Any]" and "ExtensionArray")
873 # error: Unsupported left operand type for + ("ExtensionArray")
874 indexed = data.set_index(
875 cols + columns_listlike, # type: ignore[operator]
876 append=append,
877 )
878 else:
879 index_list: list[Index] | list[Series]
880 if index is lib.no_default:
881 if isinstance(data.index, MultiIndex):
882 # GH 23955
883 index_list = [
884 data.index.get_level_values(i) for i in range(data.index.nlevels)
885 ]
886 else:
887 index_list = [
888 data._constructor_sliced(data.index, name=data.index.name)
889 ]
890 else:
891 index_list = [data[idx] for idx in com.convert_to_list_like(index)]
893 data_columns = [data[col] for col in columns_listlike]
894 index_list.extend(data_columns)
895 multiindex = MultiIndex.from_arrays(index_list)
897 if is_list_like(values) and not isinstance(values, tuple):
898 # Exclude tuple because it is seen as a single column name
899 indexed = data._constructor(
900 data[values]._values,
901 index=multiindex,
902 columns=cast("SequenceNotStr", values),
903 )
904 else:
905 indexed = data._constructor_sliced(data[values]._values, index=multiindex)
906 # error: Argument 1 to "unstack" of "DataFrame" has incompatible type "Union
907 # [List[Any], ExtensionArray, ndarray[Any, Any], Index, Series]"; expected
908 # "Hashable"
909 # unstack with a MultiIndex returns a DataFrame
910 result = cast("DataFrame", indexed.unstack(columns_listlike)) # type: ignore[arg-type]
911 result.index.names = [
912 name if name is not lib.no_default else None for name in result.index.names
913 ]
915 return result
918@set_module("pandas")
919def crosstab(
920 index,
921 columns,
922 values=None,
923 rownames=None,
924 colnames=None,
925 aggfunc=None,
926 margins: bool = False,
927 margins_name: Hashable = "All",
928 dropna: bool = True,
929 normalize: bool | Literal[0, 1, "all", "index", "columns"] = False,
930) -> DataFrame:
931 """
932 Compute a simple cross tabulation of two (or more) factors.
934 By default, computes a frequency table of the factors unless an
935 array of values and an aggregation function are passed.
937 Parameters
938 ----------
939 index : array-like, Series, or list of arrays/Series
940 Values to group by in the rows.
941 columns : array-like, Series, or list of arrays/Series
942 Values to group by in the columns.
943 values : array-like, optional
944 Array of values to aggregate according to the factors.
945 Requires `aggfunc` be specified.
946 rownames : sequence, default None
947 If passed, must match number of row arrays passed.
948 colnames : sequence, default None
949 If passed, must match number of column arrays passed.
950 aggfunc : function, optional
951 If specified, requires `values` be specified as well.
952 margins : bool, default False
953 Add row/column margins (subtotals).
954 margins_name : str, default 'All'
955 Name of the row/column that will contain the totals
956 when margins is True.
957 dropna : bool, default True
958 Do not include columns whose entries are all NaN.
959 normalize : bool, {'all', 'index', 'columns'}, or {0,1}, default False
960 Normalize by dividing all values by the sum of values.
962 - If passed 'all' or `True`, will normalize over all values.
963 - If passed 'index' will normalize over each row.
964 - If passed 'columns' will normalize over each column.
965 - If margins is `True`, will also normalize margin values.
967 Returns
968 -------
969 DataFrame
970 Cross tabulation of the data.
972 See Also
973 --------
974 DataFrame.pivot : Reshape data based on column values.
975 pivot_table : Create a pivot table as a DataFrame.
977 Notes
978 -----
979 Any Series passed will have their name attributes used unless row or column
980 names for the cross-tabulation are specified.
982 Any input passed containing Categorical data will have **all** of its
983 categories included in the cross-tabulation, even if the actual data does
984 not contain any instances of a particular category.
986 In the event that there aren't overlapping indexes an empty DataFrame will
987 be returned.
989 Reference :ref:`the user guide <reshaping.crosstabulations>` for more examples.
991 Examples
992 --------
993 >>> a = np.array(
994 ... [
995 ... "foo",
996 ... "foo",
997 ... "foo",
998 ... "foo",
999 ... "bar",
1000 ... "bar",
1001 ... "bar",
1002 ... "bar",
1003 ... "foo",
1004 ... "foo",
1005 ... "foo",
1006 ... ],
1007 ... dtype=object,
1008 ... )
1009 >>> b = np.array(
1010 ... [
1011 ... "one",
1012 ... "one",
1013 ... "one",
1014 ... "two",
1015 ... "one",
1016 ... "one",
1017 ... "one",
1018 ... "two",
1019 ... "two",
1020 ... "two",
1021 ... "one",
1022 ... ],
1023 ... dtype=object,
1024 ... )
1025 >>> c = np.array(
1026 ... [
1027 ... "dull",
1028 ... "dull",
1029 ... "shiny",
1030 ... "dull",
1031 ... "dull",
1032 ... "shiny",
1033 ... "shiny",
1034 ... "dull",
1035 ... "shiny",
1036 ... "shiny",
1037 ... "shiny",
1038 ... ],
1039 ... dtype=object,
1040 ... )
1041 >>> pd.crosstab(a, [b, c], rownames=["a"], colnames=["b", "c"])
1042 b one two
1043 c dull shiny dull shiny
1044 a
1045 bar 1 2 1 0
1046 foo 2 2 1 2
1048 Here 'c' and 'f' are not represented in the data and will not be
1049 shown in the output because dropna is True by default. Set
1050 dropna=False to preserve categories with no data.
1052 >>> foo = pd.Categorical(["a", "b"], categories=["a", "b", "c"])
1053 >>> bar = pd.Categorical(["d", "e"], categories=["d", "e", "f"])
1054 >>> pd.crosstab(foo, bar)
1055 col_0 d e
1056 row_0
1057 a 1 0
1058 b 0 1
1059 >>> pd.crosstab(foo, bar, dropna=False)
1060 col_0 d e f
1061 row_0
1062 a 1 0 0
1063 b 0 1 0
1064 c 0 0 0
1065 """
1066 if values is None and aggfunc is not None:
1067 raise ValueError("aggfunc cannot be used without values.")
1069 if values is not None and aggfunc is None:
1070 raise ValueError("values cannot be used without an aggfunc.")
1072 if not is_nested_list_like(index):
1073 index = [index]
1074 if not is_nested_list_like(columns):
1075 columns = [columns]
1077 common_idx = None
1078 pass_objs = [x for x in index + columns if isinstance(x, (ABCSeries, ABCDataFrame))]
1079 if pass_objs:
1080 common_idx = get_objs_combined_axis(pass_objs, intersect=True, sort=False)
1082 rownames = _get_names(index, rownames, prefix="row")
1083 colnames = _get_names(columns, colnames, prefix="col")
1085 # duplicate names mapped to unique names for pivot op
1086 (
1087 rownames_mapper,
1088 unique_rownames,
1089 colnames_mapper,
1090 unique_colnames,
1091 ) = _build_names_mapper(rownames, colnames)
1093 from pandas import DataFrame
1095 data = {
1096 **dict(zip(unique_rownames, index, strict=True)),
1097 **dict(zip(unique_colnames, columns, strict=True)),
1098 }
1099 df = DataFrame(data, index=common_idx)
1101 if values is None:
1102 df["__dummy__"] = 0
1103 kwargs = {"aggfunc": len, "fill_value": 0}
1104 else:
1105 df["__dummy__"] = values
1106 kwargs = {"aggfunc": aggfunc}
1108 # error: Argument 7 to "pivot_table" of "DataFrame" has incompatible type
1109 # "**Dict[str, object]"; expected "Union[...]"
1110 table = df.pivot_table(
1111 "__dummy__",
1112 index=unique_rownames,
1113 columns=unique_colnames,
1114 margins=margins,
1115 margins_name=margins_name,
1116 dropna=dropna,
1117 observed=dropna,
1118 **kwargs, # type: ignore[arg-type]
1119 )
1121 # Post-process
1122 if normalize is not False:
1123 table = _normalize(
1124 table, normalize=normalize, margins=margins, margins_name=margins_name
1125 )
1127 table = table.rename_axis(index=rownames_mapper, axis=0)
1128 table = table.rename_axis(columns=colnames_mapper, axis=1)
1130 return table
1133def _normalize(
1134 table: DataFrame, normalize, margins: bool, margins_name: Hashable = "All"
1135) -> DataFrame:
1136 if not isinstance(normalize, (bool, str)):
1137 axis_subs = {0: "index", 1: "columns"}
1138 try:
1139 normalize = axis_subs[normalize]
1140 except KeyError as err:
1141 raise ValueError("Not a valid normalize argument") from err
1143 if margins is False:
1144 # Actual Normalizations
1145 normalizers: dict[bool | str, Callable] = {
1146 "all": lambda x: x / x.sum(axis=1).sum(axis=0),
1147 "columns": lambda x: x / x.sum(),
1148 "index": lambda x: x.div(x.sum(axis=1), axis=0),
1149 }
1151 normalizers[True] = normalizers["all"]
1153 try:
1154 f = normalizers[normalize]
1155 except KeyError as err:
1156 raise ValueError("Not a valid normalize argument") from err
1158 table = f(table)
1159 table = table.fillna(0)
1161 elif margins is True:
1162 # keep index and column of pivoted table
1163 table_index = table.index
1164 table_columns = table.columns
1165 last_ind_or_col = table.iloc[-1, :].name
1167 # check if margin name is not in (for MI cases) and not equal to last
1168 # index/column and save the column and index margin
1169 if (margins_name not in last_ind_or_col) & (margins_name != last_ind_or_col):
1170 raise ValueError(f"{margins_name} not in pivoted DataFrame")
1171 column_margin = table.iloc[:-1, -1]
1172 index_margin = table.iloc[-1, :-1]
1174 # keep the core table
1175 table = table.iloc[:-1, :-1]
1177 # Normalize core
1178 table = _normalize(table, normalize=normalize, margins=False)
1180 # Fix Margins
1181 if normalize == "columns":
1182 column_margin = column_margin / column_margin.sum()
1183 table = concat([table, column_margin], axis=1)
1184 table = table.fillna(0)
1185 table.columns = table_columns
1187 elif normalize == "index":
1188 index_margin = index_margin / index_margin.sum()
1189 table = table._append_internal(index_margin, ignore_index=True)
1190 table = table.fillna(0)
1191 table.index = table_index
1193 elif normalize == "all" or normalize is True:
1194 column_margin = column_margin / column_margin.sum()
1195 index_margin = index_margin / index_margin.sum()
1196 index_margin.loc[margins_name] = 1
1197 table = concat([table, column_margin], axis=1)
1198 table = table._append_internal(index_margin, ignore_index=True)
1200 table = table.fillna(0)
1201 table.index = table_index
1202 table.columns = table_columns
1204 else:
1205 raise ValueError("Not a valid normalize argument")
1207 else:
1208 raise ValueError("Not a valid margins argument")
1210 return table
1213def _get_names(arrs, names, prefix: str = "row") -> list:
1214 if names is None:
1215 names = []
1216 for i, arr in enumerate(arrs):
1217 if isinstance(arr, ABCSeries) and arr.name is not None:
1218 names.append(arr.name)
1219 else:
1220 names.append(f"{prefix}_{i}")
1221 else:
1222 if len(names) != len(arrs):
1223 raise AssertionError("arrays and names must have the same length")
1224 if not isinstance(names, list):
1225 names = list(names)
1227 return names
1230def _build_names_mapper(
1231 rownames: list[str], colnames: list[str]
1232) -> tuple[dict[str, str], list[str], dict[str, str], list[str]]:
1233 """
1234 Given the names of a DataFrame's rows and columns, returns a set of unique row
1235 and column names and mappers that convert to original names.
1237 A row or column name is replaced if it is duplicate among the rows of the inputs,
1238 among the columns of the inputs or between the rows and the columns.
1240 Parameters
1241 ----------
1242 rownames: list[str]
1243 colnames: list[str]
1245 Returns
1246 -------
1247 Tuple(Dict[str, str], List[str], Dict[str, str], List[str])
1249 rownames_mapper: dict[str, str]
1250 a dictionary with new row names as keys and original rownames as values
1251 unique_rownames: list[str]
1252 a list of rownames with duplicate names replaced by dummy names
1253 colnames_mapper: dict[str, str]
1254 a dictionary with new column names as keys and original column names as values
1255 unique_colnames: list[str]
1256 a list of column names with duplicate names replaced by dummy names
1258 """
1259 dup_names = set(rownames) | set(colnames)
1261 rownames_mapper = {
1262 f"row_{i}": name for i, name in enumerate(rownames) if name in dup_names
1263 }
1264 unique_rownames = [
1265 f"row_{i}" if name in dup_names else name for i, name in enumerate(rownames)
1266 ]
1268 colnames_mapper = {
1269 f"col_{i}": name for i, name in enumerate(colnames) if name in dup_names
1270 }
1271 unique_colnames = [
1272 f"col_{i}" if name in dup_names else name for i, name in enumerate(colnames)
1273 ]
1275 return rownames_mapper, unique_rownames, colnames_mapper, unique_colnames