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
52 from pandas.api.typing import (
53 DataFrameGroupBy,
54 SeriesGroupBy,
55 )
58@set_module("pandas")
59def pivot_table(
60 data: DataFrame,
61 values=None,
62 index=None,
63 columns=None,
64 aggfunc: AggFuncType = "mean",
65 fill_value=None,
66 margins: bool = False,
67 dropna: bool = True,
68 margins_name: Hashable = "All",
69 observed: bool = True,
70 sort: bool = True,
71 **kwargs,
72) -> DataFrame:
73 """
74 Create a spreadsheet-style pivot table as a DataFrame.
76 The levels in the pivot table will be stored in MultiIndex objects
77 (hierarchical indexes) on the index and columns of the result DataFrame.
79 Parameters
80 ----------
81 data : DataFrame
82 Input pandas DataFrame object.
83 values : list-like or scalar, optional
84 Column or columns to aggregate.
85 index : column, Grouper, array, or sequence of the previous
86 Keys to group by on the pivot table index. If a list is passed,
87 it can contain any of the other types (except list). If an array is
88 passed, it must be the same length as the data and will be used in
89 the same manner as column values.
90 columns : column, Grouper, array, or sequence of the previous
91 Keys to group by on the pivot table column. If a list is passed,
92 it can contain any of the other types (except list). If an array is
93 passed, it must be the same length as the data and will be used in
94 the same manner as column values.
95 aggfunc : function, list of functions, dict, default "mean"
96 If a list of functions is passed, the resulting pivot table will have
97 hierarchical columns whose top level are the function names
98 (inferred from the function objects themselves).
99 If a dict is passed, the key is column to aggregate and the value is
100 function or list of functions. If ``margins=True``, aggfunc will be
101 used to calculate the partial aggregates.
102 fill_value : scalar, default None
103 Value to replace missing values with (in the resulting pivot table,
104 after aggregation).
105 margins : bool, default False
106 If ``margins=True``, special ``All`` columns and rows
107 will be added with partial group aggregates across the categories
108 on the rows and columns.
109 dropna : bool, default True
110 Do not include columns whose entries are all NaN. If True,
112 * rows with an NA value in any column will be omitted before computing margins,
113 * index/column keys containing NA values will be dropped (see ``dropna``
114 parameter in :meth:``DataFrame.groupby``).
116 margins_name : str, default 'All'
117 Name of the row / column that will contain the totals
118 when margins is True.
119 observed : bool, default True
120 This only applies if any of the groupers are Categoricals.
121 If True: only show observed values for categorical groupers.
122 If False: show all values for categorical groupers.
124 .. versionchanged:: 3.0.0
126 The default value is now ``True``.
128 sort : bool, default True
129 Specifies if the result should be sorted.
131 **kwargs : dict
132 Optional keyword arguments to pass to ``aggfunc``.
134 .. versionadded:: 3.0.0
136 Returns
137 -------
138 DataFrame
139 An Excel style pivot table.
141 See Also
142 --------
143 DataFrame.pivot : Pivot without aggregation that can handle
144 non-numeric data.
145 DataFrame.melt: Unpivot a DataFrame from wide to long format,
146 optionally leaving identifiers set.
147 wide_to_long : Wide panel to long format. Less flexible but more
148 user-friendly than melt.
150 Notes
151 -----
152 Reference :ref:`the user guide <reshaping.pivot>` for more examples.
154 .. versionchanged:: 3.1.0
155 When ``values`` is empty, which is the case when all columns of
156 ``data`` are either in ``index`` or ``columns`` arguments, aggregation
157 will act on a Series of all NA values.
159 Examples
160 --------
161 >>> df = pd.DataFrame(
162 ... {
163 ... "A": ["foo", "foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar"],
164 ... "B": ["one", "one", "one", "two", "two", "one", "one", "two", "two"],
165 ... "C": [
166 ... "small",
167 ... "large",
168 ... "large",
169 ... "small",
170 ... "small",
171 ... "large",
172 ... "small",
173 ... "small",
174 ... "large",
175 ... ],
176 ... "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
177 ... "E": [2, 4, 5, 5, 6, 6, 8, 9, 9],
178 ... }
179 ... )
180 >>> df
181 A B C D E
182 0 foo one small 1 2
183 1 foo one large 2 4
184 2 foo one large 2 5
185 3 foo two small 3 5
186 4 foo two small 3 6
187 5 bar one large 4 6
188 6 bar one small 5 8
189 7 bar two small 6 9
190 8 bar two large 7 9
192 This first example aggregates values by taking the sum.
194 >>> table = pd.pivot_table(
195 ... df, values="D", index=["A", "B"], columns=["C"], aggfunc="sum"
196 ... )
197 >>> table
198 C large small
199 A B
200 bar one 4.0 5.0
201 two 7.0 6.0
202 foo one 4.0 1.0
203 two NaN 6.0
205 We can also fill missing values using the `fill_value` parameter.
207 >>> table = pd.pivot_table(
208 ... df, values="D", index=["A", "B"], columns=["C"], aggfunc="sum", fill_value=0
209 ... )
210 >>> table
211 C large small
212 A B
213 bar one 4 5
214 two 7 6
215 foo one 4 1
216 two 0 6
218 The next example aggregates by taking the mean across multiple columns.
220 >>> table = pd.pivot_table(
221 ... df, values=["D", "E"], index=["A", "C"], aggfunc={"D": "mean", "E": "mean"}
222 ... )
223 >>> table
224 D E
225 A C
226 bar large 5.500000 7.500000
227 small 5.500000 8.500000
228 foo large 2.000000 4.500000
229 small 2.333333 4.333333
231 We can also calculate multiple types of aggregations for any given
232 value column.
234 >>> table = pd.pivot_table(
235 ... df,
236 ... values=["D", "E"],
237 ... index=["A", "C"],
238 ... aggfunc={"D": "mean", "E": ["min", "max", "mean"]},
239 ... )
240 >>> table
241 D E
242 mean max mean min
243 A C
244 bar large 5.500000 9 7.500000 6
245 small 5.500000 9 8.500000 8
246 foo large 2.000000 5 4.500000 4
247 small 2.333333 6 4.333333 2
248 """
249 index = _convert_by(index)
250 columns = _convert_by(columns)
252 if isinstance(aggfunc, list):
253 pieces: list[DataFrame] = []
254 keys = []
255 for func in aggfunc:
256 _table = __internal_pivot_table(
257 data,
258 values=values,
259 index=index,
260 columns=columns,
261 fill_value=fill_value,
262 aggfunc=func,
263 margins=margins,
264 dropna=dropna,
265 margins_name=margins_name,
266 observed=observed,
267 sort=sort,
268 kwargs=kwargs,
269 )
270 pieces.append(_table)
271 keys.append(getattr(func, "__name__", func))
273 table = concat(pieces, keys=keys, axis=1)
274 return table.__finalize__(data, method="pivot_table")
276 table = __internal_pivot_table(
277 data,
278 values,
279 index,
280 columns,
281 aggfunc,
282 fill_value,
283 margins,
284 dropna,
285 margins_name,
286 observed,
287 sort,
288 kwargs,
289 )
290 return table.__finalize__(data, method="pivot_table")
293def __internal_pivot_table(
294 data: DataFrame,
295 values,
296 index,
297 columns,
298 aggfunc: AggFuncTypeBase | AggFuncTypeDict,
299 fill_value,
300 margins: bool,
301 dropna: bool,
302 margins_name: Hashable,
303 observed: bool,
304 sort: bool,
305 kwargs,
306) -> DataFrame:
307 """
308 Helper of :func:`pandas.pivot_table` for any non-list ``aggfunc``.
309 """
310 keys = index + columns
312 values_passed = values is not None
313 if values_passed:
314 if is_list_like(values):
315 values_multi = True
316 values = list(values)
317 else:
318 values_multi = False
319 values = [values]
321 # GH14938 Make sure value labels are in data
322 for i in values:
323 if i not in data:
324 raise KeyError(i)
326 to_filter = []
327 for x in keys + values:
328 if isinstance(x, Grouper):
329 x = x.key
330 try:
331 if x in data:
332 to_filter.append(x)
333 except TypeError:
334 pass
335 if len(to_filter) < len(data.columns):
336 data = data[to_filter]
338 else:
339 values = data.columns
340 for key in keys:
341 try:
342 values = values.drop(key)
343 except (TypeError, ValueError, KeyError):
344 pass
345 values = list(values)
347 grouped: SeriesGroupBy | DataFrameGroupBy = data.groupby(
348 keys, observed=observed, sort=sort, dropna=dropna
349 )
350 if values_passed:
351 # GH#57876 and GH#61292
352 grouped = grouped[values]
353 elif grouped._obj_with_exclusions.columns.empty:
354 grouped = Series(np.nan, index=data.index).groupby(
355 [data[key] for key in keys], observed=observed, sort=sort, dropna=dropna
356 )
358 agged = grouped.agg(aggfunc, **kwargs)
360 if dropna and isinstance(agged, ABCDataFrame) and len(agged.columns):
361 agged = agged.dropna(how="all")
363 table = agged
365 # GH17038, this check should only happen if index is defined (not None)
366 if table.index.nlevels > 1 and index:
367 # Related GH #17123
368 # If index_names are integers, determine whether the integers refer
369 # to the level position or name.
370 index_names = agged.index.names[: len(index)]
371 to_unstack = []
372 for i in range(len(index), len(keys)):
373 name = agged.index.names[i]
374 if name is None or name in index_names:
375 to_unstack.append(i)
376 else:
377 to_unstack.append(name)
378 table = agged.unstack(to_unstack, fill_value=fill_value)
380 if not dropna:
381 if isinstance(table.index, MultiIndex):
382 m = MultiIndex.from_product(table.index.levels, names=table.index.names)
383 table = table.reindex(m, axis=0, fill_value=fill_value)
385 if isinstance(table.columns, MultiIndex):
386 m = MultiIndex.from_product(table.columns.levels, names=table.columns.names)
387 table = table.reindex(m, axis=1, fill_value=fill_value)
389 if sort is True and isinstance(table, ABCDataFrame):
390 table = table.sort_index(axis=1)
392 if fill_value is not None:
393 table = table.fillna(fill_value)
394 if aggfunc is len and not observed and lib.is_integer(fill_value):
395 # TODO: can we avoid this? this used to be handled by
396 # downcast="infer" in fillna
397 table = table.astype(np.int64)
399 if margins:
400 if dropna:
401 data = data[data.notna().all(axis=1)]
402 table = _add_margins(
403 table,
404 data,
405 values,
406 rows=index,
407 cols=columns,
408 aggfunc=aggfunc,
409 kwargs=kwargs,
410 observed=dropna,
411 margins_name=margins_name,
412 fill_value=fill_value,
413 dropna=dropna,
414 )
416 # discard the top level
417 if values_passed and not values_multi and table.columns.nlevels > 1:
418 table.columns = table.columns.droplevel(0)
419 if len(index) == 0 and len(columns) > 0:
420 table = table.T
422 # GH 15193 Make sure empty columns are removed if dropna=True
423 if isinstance(table, ABCDataFrame) and dropna:
424 table = table.dropna(how="all", axis=1)
426 return cast("DataFrame", table)
429def _add_margins(
430 table: DataFrame | Series,
431 data: DataFrame,
432 values,
433 rows,
434 cols,
435 aggfunc,
436 kwargs,
437 observed: bool,
438 margins_name: Hashable = "All",
439 fill_value=None,
440 dropna: bool = True,
441):
442 if not isinstance(margins_name, str):
443 raise ValueError("margins_name argument must be a string")
445 msg = f'Conflicting name "{margins_name}" in margins'
446 for level in table.index.names:
447 if margins_name in table.index.get_level_values(level):
448 raise ValueError(msg)
450 grand_margin = _compute_grand_margin(data, values, aggfunc, kwargs, margins_name)
452 if table.ndim == 2:
453 # i.e. DataFrame
454 for level in table.columns.names[1:]:
455 if margins_name in table.columns.get_level_values(level):
456 raise ValueError(msg)
458 key: str | tuple[str, ...]
459 if len(rows) > 1:
460 key = (margins_name,) + ("",) * (len(rows) - 1)
461 else:
462 key = margins_name
464 if not values and isinstance(table, ABCSeries):
465 # If there are no values and the table is a series, then there is only
466 # one column in the data. Compute grand margin and return it.
467 row = table._constructor({key: grand_margin[margins_name]})
468 if not isinstance(table.index, MultiIndex):
469 row.index.name = table.index.name
470 return table._append_internal(row)
472 elif values:
473 marginal_result_set = _generate_marginal_results(
474 table,
475 data,
476 values,
477 rows,
478 cols,
479 aggfunc,
480 kwargs,
481 observed,
482 margins_name,
483 dropna,
484 )
485 if not isinstance(marginal_result_set, tuple):
486 return marginal_result_set
487 result, margin_keys, row_margin = marginal_result_set
488 else:
489 # no values, and table is a DataFrame
490 assert isinstance(table, ABCDataFrame)
491 marginal_result_set = _generate_marginal_results_without_values(
492 table, data, rows, cols, aggfunc, kwargs, observed, margins_name, dropna
493 )
494 if not isinstance(marginal_result_set, tuple):
495 return marginal_result_set
496 result, margin_keys, row_margin = marginal_result_set
498 # populate grand margin
499 for k in margin_keys:
500 if isinstance(k, str):
501 row_margin[k] = grand_margin[k]
502 else:
503 row_margin[k] = grand_margin[k[0]]
505 # GH#55484 recover the correct dtype when row_margin was initialized as
506 # object (len(cols)==0 path); no-op for already-typed series.
507 row_margin = row_margin.infer_objects()
509 from pandas import DataFrame
511 row_margin = row_margin.reindex(result.columns, fill_value=fill_value)
512 margin_dummy = DataFrame(row_margin, columns=Index([key])).T
514 for dtype in set(result.dtypes):
515 cols = result.select_dtypes([dtype]).columns
516 if isinstance(dtype, ExtensionDtype):
517 # GH#55484 margin_dummy may be object-dtype when row_margin was
518 # initialized with dtype=object (len(cols)==0 path); cast back.
519 margin_dummy[cols] = margin_dummy[cols].astype(dtype)
520 elif dtype != object and (margin_dummy[cols].dtypes == object).all():
521 # GH#55484 object-initialized row_margin can leave non-EA columns
522 # as object (mixed-values case); astype back to the target dtype.
523 margin_dummy[cols] = margin_dummy[cols].astype(dtype)
524 else:
525 margin_dummy[cols] = margin_dummy[cols].apply(
526 maybe_downcast_to_dtype, args=(dtype,)
527 )
529 row_names = result.index.names
530 result = concat([result, margin_dummy])
531 result.index.names = row_names
533 return result
536def _compute_grand_margin(
537 data: DataFrame, values, aggfunc, kwargs, margins_name: Hashable = "All"
538):
539 if values:
540 grand_margin = {}
541 for k, v in data[values].items():
542 try:
543 if isinstance(aggfunc, str):
544 grand_margin[k] = getattr(v, aggfunc)(**kwargs)
545 elif isinstance(aggfunc, dict):
546 if k in aggfunc:
547 if isinstance(aggfunc[k], str):
548 grand_margin[k] = getattr(v, aggfunc[k])(**kwargs)
549 else:
550 grand_margin[k] = aggfunc[k](v, **kwargs)
551 else:
552 grand_margin[k] = aggfunc(v, **kwargs)
553 except TypeError:
554 pass
555 return grand_margin
556 else:
557 from pandas import Categorical
559 # Use groupby for consistency with how values are aggregated.
560 gb = Series(np.nan, index=data.index).groupby(
561 Categorical(np.zeros(len(data.index)), categories=[0]), observed=False
562 )
563 agged = gb.agg(aggfunc, **kwargs)
564 assert isinstance(agged, Series) and len(agged) == 1
565 return {margins_name: agged.iloc[0]}
568def _generate_marginal_results(
569 table,
570 data: DataFrame,
571 values,
572 rows,
573 cols,
574 aggfunc,
575 kwargs,
576 observed: bool,
577 margins_name: Hashable = "All",
578 dropna: bool = True,
579):
580 margin_keys: list | Index
581 if len(cols) > 0:
582 # need to "interleave" the margins
583 table_pieces = []
584 margin_keys = []
586 def _all_key(key):
587 return (key, margins_name) + ("",) * (len(cols) - 1)
589 if len(rows) > 0:
590 margin = (
591 data[rows + values]
592 .groupby(rows, observed=observed, dropna=dropna)
593 .agg(aggfunc, **kwargs)
594 )
595 cat_axis = 1
597 for key, piece in table.T.groupby(level=0, observed=observed):
598 piece = piece.T
599 all_key = _all_key(key)
601 piece[all_key] = margin[key]
603 table_pieces.append(piece)
604 margin_keys.append(all_key)
605 else:
606 margin = (
607 data[cols[:1] + values]
608 .groupby(cols[:1], observed=observed, dropna=dropna)
609 .agg(aggfunc, **kwargs)
610 .T
611 )
613 cat_axis = 0
614 for key, piece in table.groupby(level=0, observed=observed):
615 if len(cols) > 1:
616 all_key = _all_key(key)
617 else:
618 all_key = margins_name
619 table_pieces.append(piece)
620 transformed_piece = margin[key].to_frame().T
621 if isinstance(piece.index, MultiIndex):
622 # We are adding an empty level
623 transformed_piece.index = MultiIndex.from_tuples(
624 [all_key],
625 names=[*piece.index.names, None],
626 )
627 else:
628 transformed_piece.index = Index([all_key], name=piece.index.name)
630 # append piece for margin into table_piece
631 table_pieces.append(transformed_piece)
632 margin_keys.append(all_key)
634 if not table_pieces:
635 # GH 49240
636 return table
637 else:
638 result = concat(table_pieces, axis=cat_axis)
640 if len(rows) == 0:
641 return result
642 else:
643 result = table
644 margin_keys = table.columns
646 if len(cols) > 0:
647 row_margin = (
648 data[cols + values]
649 .groupby(cols, observed=observed, dropna=dropna)
650 .agg(aggfunc, **kwargs)
651 )
652 row_margin = row_margin.stack()
654 # GH#26568. Use names instead of indices in case of numeric names
655 new_order_indices = itertools.chain([len(cols)], range(len(cols)))
656 new_order_names = [row_margin.index.names[i] for i in new_order_indices]
657 row_margin.index = row_margin.index.reorder_levels(new_order_names)
658 else:
659 # GH#55484 use object dtype so setitem works for grand-margin scalars
660 # whose dtype cannot hold NA (e.g. IntervalDtype with integer subtype);
661 # infer_objects is called in _add_margins after the values are set.
662 row_margin = data._constructor_sliced(index=result.columns, dtype=object)
664 return result, margin_keys, row_margin
667def _generate_marginal_results_without_values(
668 table: DataFrame,
669 data: DataFrame,
670 rows,
671 cols,
672 aggfunc,
673 kwargs,
674 observed: bool,
675 margins_name: Hashable = "All",
676 dropna: bool = True,
677):
678 margin_keys: list | Index
679 if len(cols) > 0:
680 # need to "interleave" the margins
681 margin_keys = []
683 def _all_key():
684 if len(cols) == 1:
685 return margins_name
686 return (margins_name,) + ("",) * (len(cols) - 1)
688 if len(rows) > 0:
689 if len(data.index) > 0:
690 gb = Series(np.nan, index=data.index).groupby(
691 [data[row] for row in rows], observed=observed, dropna=dropna
692 )
693 margin = gb.agg(aggfunc, **kwargs)
694 else:
695 from pandas import Categorical
697 gb = Series(np.nan, index=data.index).groupby(
698 Categorical(np.zeros(0), categories=[0]), observed=False
699 )
700 agged = gb.agg(aggfunc, **kwargs)
701 assert isinstance(agged, Series) and len(agged) == 1
702 margin = agged.iloc[0]
703 all_key = _all_key()
704 table[all_key] = margin
705 result = table
706 margin_keys.append(all_key)
708 else:
709 margin = data.groupby(level=0, observed=observed, dropna=dropna).agg(
710 aggfunc, **kwargs
711 )
712 all_key = _all_key()
713 table[all_key] = margin
714 result = table
715 margin_keys.append(all_key)
716 return result
717 else:
718 result = table
719 margin_keys = table.columns
721 if len(result.columns) > 0:
722 row_margin = (
723 Series(np.nan, index=data.index)
724 .groupby([data[col] for col in cols], observed=observed, dropna=dropna)
725 .agg(aggfunc, **kwargs)
726 )
727 else:
728 row_margin = Series(np.nan, index=result.columns)
730 return result, margin_keys, row_margin
733def _convert_by(by):
734 if by is None:
735 by = []
736 elif (
737 is_scalar(by)
738 or isinstance(by, (np.ndarray, Index, ABCSeries, Grouper))
739 or callable(by)
740 ):
741 by = [by]
742 else:
743 by = list(by)
744 return by
747@set_module("pandas")
748def pivot(
749 data: DataFrame,
750 *,
751 columns: IndexLabel,
752 index: IndexLabel | lib.NoDefault = lib.no_default,
753 values: IndexLabel | lib.NoDefault = lib.no_default,
754) -> DataFrame:
755 """
756 Return reshaped DataFrame organized by given index / column values.
758 Reshape data (produce a "pivot" table) based on column values. Uses
759 unique values from specified `index` / `columns` to form axes of the
760 resulting DataFrame. This function does not support data
761 aggregation, multiple values will result in a MultiIndex in the
762 columns. See the :ref:`User Guide <reshaping>` for more on reshaping.
764 Parameters
765 ----------
766 data : DataFrame
767 Input pandas DataFrame object.
768 columns : Hashable or a sequence of the previous
769 Column to use to make new frame's columns.
770 index : Hashable or a sequence of the previous, optional
771 Column to use to make new frame's index. If not given, uses existing index.
772 values : Hashable or a sequence of the previous, optional
773 Column(s) to use for populating new frame's values. If not
774 specified, all remaining columns will be used and the result will
775 have hierarchically indexed columns.
777 Returns
778 -------
779 DataFrame
780 Returns reshaped DataFrame.
782 Raises
783 ------
784 ValueError:
785 When there are any `index`, `columns` combinations with multiple
786 values. `DataFrame.pivot_table` when you need to aggregate.
788 See Also
789 --------
790 DataFrame.pivot_table : Generalization of pivot that can handle
791 duplicate values for one index/column pair.
792 DataFrame.unstack : Pivot based on the index values instead of a
793 column.
794 wide_to_long : Wide panel to long format. Less flexible but more
795 user-friendly than melt.
797 Notes
798 -----
799 For finer-tuned control, see hierarchical indexing documentation along
800 with the related stack/unstack methods.
802 Reference :ref:`the user guide <reshaping.pivot>` for more examples.
804 Examples
805 --------
806 >>> df = pd.DataFrame(
807 ... {
808 ... "foo": ["one", "one", "one", "two", "two", "two"],
809 ... "bar": ["A", "B", "C", "A", "B", "C"],
810 ... "baz": [1, 2, 3, 4, 5, 6],
811 ... "zoo": ["x", "y", "z", "q", "w", "t"],
812 ... }
813 ... )
814 >>> df
815 foo bar baz zoo
816 0 one A 1 x
817 1 one B 2 y
818 2 one C 3 z
819 3 two A 4 q
820 4 two B 5 w
821 5 two C 6 t
823 >>> df.pivot(index="foo", columns="bar", values="baz")
824 bar A B C
825 foo
826 one 1 2 3
827 two 4 5 6
829 >>> df.pivot(index="foo", columns="bar")["baz"]
830 bar A B C
831 foo
832 one 1 2 3
833 two 4 5 6
835 >>> df.pivot(index="foo", columns="bar", values=["baz", "zoo"])
836 baz zoo
837 bar A B C A B C
838 foo
839 one 1 2 3 x y z
840 two 4 5 6 q w t
842 You could also assign a list of column names or a list of index names.
844 >>> df = pd.DataFrame(
845 ... {
846 ... "lev1": [1, 1, 1, 2, 2, 2],
847 ... "lev2": [1, 1, 2, 1, 1, 2],
848 ... "lev3": [1, 2, 1, 2, 1, 2],
849 ... "lev4": [1, 2, 3, 4, 5, 6],
850 ... "values": [0, 1, 2, 3, 4, 5],
851 ... }
852 ... )
853 >>> df
854 lev1 lev2 lev3 lev4 values
855 0 1 1 1 1 0
856 1 1 1 2 2 1
857 2 1 2 1 3 2
858 3 2 1 2 4 3
859 4 2 1 1 5 4
860 5 2 2 2 6 5
862 >>> df.pivot(index="lev1", columns=["lev2", "lev3"], values="values")
863 lev2 1 2
864 lev3 1 2 1 2
865 lev1
866 1 0.0 1.0 2.0 NaN
867 2 4.0 3.0 NaN 5.0
869 >>> df.pivot(index=["lev1", "lev2"], columns=["lev3"], values="values")
870 lev3 1 2
871 lev1 lev2
872 1 1 0.0 1.0
873 2 2.0 NaN
874 2 1 4.0 3.0
875 2 NaN 5.0
877 A ValueError is raised if there are any duplicates.
879 >>> df = pd.DataFrame(
880 ... {
881 ... "foo": ["one", "one", "two", "two"],
882 ... "bar": ["A", "A", "B", "C"],
883 ... "baz": [1, 2, 3, 4],
884 ... }
885 ... )
886 >>> df
887 foo bar baz
888 0 one A 1
889 1 one A 2
890 2 two B 3
891 3 two C 4
893 Notice that the first two rows are the same for our `index`
894 and `columns` arguments.
896 >>> df.pivot(index="foo", columns="bar", values="baz")
897 Traceback (most recent call last):
898 ...
899 ValueError: Index contains duplicate entries, cannot reshape
900 """
901 columns_listlike = com.convert_to_list_like(columns)
903 # GH#35785 without this, downstream label arithmetic raises cryptically.
904 labels_to_check: list[tuple[str, list]] = [("columns", list(columns_listlike))]
905 if index is not lib.no_default:
906 labels_to_check.append(("index", list(com.convert_to_list_like(index))))
907 if values is not lib.no_default and not isinstance(values, tuple):
908 # GH#17160 a tuple ``values`` is a single (MultiIndex) label; the
909 # existing lookup already raises a KeyError naming it.
910 labels_to_check.append(("values", list(com.convert_to_list_like(values))))
911 for param_name, labels in labels_to_check:
912 missing = [label for label in labels if label not in data.columns]
913 if missing:
914 raise KeyError(
915 f"The following '{param_name}' labels are not columns of the "
916 f"DataFrame: {missing}"
917 )
919 # If columns is None we will create a MultiIndex level with None as name
920 # which might cause duplicated names because None is the default for
921 # level names
922 if any(name is None for name in data.index.names):
923 data = data.copy(deep=False)
924 data.index.names = [
925 name if name is not None else lib.no_default for name in data.index.names
926 ]
928 indexed: DataFrame | Series
929 if values is lib.no_default:
930 if index is not lib.no_default:
931 cols = com.convert_to_list_like(index)
932 else:
933 cols = []
935 append = index is lib.no_default
936 # error: Unsupported operand types for + ("List[Any]" and "ExtensionArray")
937 # error: Unsupported left operand type for + ("ExtensionArray")
938 indexed = data.set_index(
939 cols + columns_listlike, # type: ignore[operator]
940 append=append,
941 )
942 else:
943 index_list: list[Index] | list[Series]
944 if index is lib.no_default:
945 if isinstance(data.index, MultiIndex):
946 # GH 23955
947 index_list = [
948 data.index.get_level_values(i) for i in range(data.index.nlevels)
949 ]
950 else:
951 index_list = [
952 data._constructor_sliced(data.index, name=data.index.name)
953 ]
954 else:
955 index_list = [data[idx] for idx in com.convert_to_list_like(index)]
957 data_columns = [data[col] for col in columns_listlike]
958 index_list.extend(data_columns)
959 multiindex = MultiIndex.from_arrays(index_list)
961 if is_list_like(values) and not isinstance(values, tuple):
962 # Exclude tuple because it is seen as a single column name
963 indexed = data._constructor(
964 data[values]._values,
965 index=multiindex,
966 columns=cast("SequenceNotStr", values),
967 )
968 else:
969 indexed = data._constructor_sliced(data[values]._values, index=multiindex)
970 # error: Argument 1 to "unstack" of "DataFrame" has incompatible type "Union
971 # [List[Any], ExtensionArray, ndarray[Any, Any], Index, Series]"; expected
972 # "Hashable"
973 # unstack with a MultiIndex returns a DataFrame
974 result = cast("DataFrame", indexed.unstack(columns_listlike)) # type: ignore[arg-type]
975 result.index.names = [
976 name if name is not lib.no_default else None for name in result.index.names
977 ]
979 return result
982@set_module("pandas")
983def crosstab(
984 index,
985 columns,
986 values=None,
987 rownames=None,
988 colnames=None,
989 aggfunc=None,
990 margins: bool = False,
991 margins_name: Hashable = "All",
992 dropna: bool = True,
993 normalize: bool | Literal[0, 1, "all", "index", "columns"] = False,
994) -> DataFrame:
995 """
996 Compute a simple cross tabulation of two (or more) factors.
998 By default, computes a frequency table of the factors unless an
999 array of values and an aggregation function are passed.
1001 Parameters
1002 ----------
1003 index : array-like, Series, or list of arrays/Series
1004 Values to group by in the rows.
1005 columns : array-like, Series, or list of arrays/Series
1006 Values to group by in the columns.
1007 values : array-like, optional
1008 Array of values to aggregate according to the factors.
1009 Requires `aggfunc` be specified.
1010 rownames : sequence, default None
1011 If passed, must match number of row arrays passed.
1012 colnames : sequence, default None
1013 If passed, must match number of column arrays passed.
1014 aggfunc : function, optional
1015 If specified, requires `values` be specified as well.
1016 margins : bool, default False
1017 Add row/column margins (subtotals).
1018 margins_name : str, default 'All'
1019 Name of the row/column that will contain the totals
1020 when margins is True.
1021 dropna : bool, default True
1022 Do not include columns whose entries are all NaN.
1023 normalize : bool, {'all', 'index', 'columns'}, or {0,1}, default False
1024 Normalize by dividing all values by the sum of values.
1026 - If passed 'all' or `True`, will normalize over all values.
1027 - If passed 'index' will normalize over each row.
1028 - If passed 'columns' will normalize over each column.
1029 - If margins is `True`, will also normalize margin values.
1031 Returns
1032 -------
1033 DataFrame
1034 Cross tabulation of the data.
1036 See Also
1037 --------
1038 DataFrame.pivot : Reshape data based on column values.
1039 pivot_table : Create a pivot table as a DataFrame.
1041 Notes
1042 -----
1043 Any Series passed will have their name attributes used unless row or column
1044 names for the cross-tabulation are specified.
1046 With the default ``dropna=True``, categories without any observed
1047 instances in the input are dropped from the cross-tabulation. When
1048 ``dropna=False``, all categories of any Categorical input are included,
1049 even if the actual data does not contain any instances of a particular
1050 category.
1052 Series arguments are aligned on their index before tabulating; rows where
1053 any value is missing after alignment (e.g. index labels not present in
1054 all Series) are dropped. In the event that there aren't overlapping
1055 indexes an empty DataFrame will be returned.
1057 Reference :ref:`the user guide <reshaping.crosstabulations>` for more examples.
1059 Examples
1060 --------
1061 >>> a = np.array(
1062 ... [
1063 ... "foo",
1064 ... "foo",
1065 ... "foo",
1066 ... "foo",
1067 ... "bar",
1068 ... "bar",
1069 ... "bar",
1070 ... "bar",
1071 ... "foo",
1072 ... "foo",
1073 ... "foo",
1074 ... ],
1075 ... dtype=object,
1076 ... )
1077 >>> b = np.array(
1078 ... [
1079 ... "one",
1080 ... "one",
1081 ... "one",
1082 ... "two",
1083 ... "one",
1084 ... "one",
1085 ... "one",
1086 ... "two",
1087 ... "two",
1088 ... "two",
1089 ... "one",
1090 ... ],
1091 ... dtype=object,
1092 ... )
1093 >>> c = np.array(
1094 ... [
1095 ... "dull",
1096 ... "dull",
1097 ... "shiny",
1098 ... "dull",
1099 ... "dull",
1100 ... "shiny",
1101 ... "shiny",
1102 ... "dull",
1103 ... "shiny",
1104 ... "shiny",
1105 ... "shiny",
1106 ... ],
1107 ... dtype=object,
1108 ... )
1109 >>> pd.crosstab(a, [b, c], rownames=["a"], colnames=["b", "c"])
1110 b one two
1111 c dull shiny dull shiny
1112 a
1113 bar 1 2 1 0
1114 foo 2 2 1 2
1116 When `values` and `aggfunc` are passed, the values are aggregated within
1117 each group instead of counted:
1119 >>> vals = np.array([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11])
1120 >>> pd.crosstab(a, b, values=vals, aggfunc="sum", rownames=["a"], colnames=["b"])
1121 b one two
1122 a
1123 bar 18 8
1124 foo 17 23
1126 Here 'c' and 'f' are not represented in the data and will not be
1127 shown in the output because dropna is True by default. Set
1128 dropna=False to preserve categories with no data.
1130 >>> foo = pd.Categorical(["a", "b"], categories=["a", "b", "c"])
1131 >>> bar = pd.Categorical(["d", "e"], categories=["d", "e", "f"])
1132 >>> pd.crosstab(foo, bar)
1133 col_0 d e
1134 row_0
1135 a 1 0
1136 b 0 1
1137 >>> pd.crosstab(foo, bar, dropna=False)
1138 col_0 d e f
1139 row_0
1140 a 1 0 0
1141 b 0 1 0
1142 c 0 0 0
1143 """
1144 if values is None and aggfunc is not None:
1145 raise ValueError("aggfunc cannot be used without values.")
1147 if values is not None and aggfunc is None:
1148 raise ValueError("values cannot be used without an aggfunc.")
1150 if not is_nested_list_like(index):
1151 index = [index]
1152 if not is_nested_list_like(columns):
1153 columns = [columns]
1155 common_idx = None
1156 pass_objs = [x for x in index + columns if isinstance(x, (ABCSeries, ABCDataFrame))]
1157 if pass_objs:
1158 common_idx, _ = get_objs_combined_axis(pass_objs, intersect=True, sort=False)
1160 rownames = _get_names(index, rownames, prefix="row")
1161 colnames = _get_names(columns, colnames, prefix="col")
1163 # duplicate names mapped to unique names for pivot op
1164 (
1165 rownames_mapper,
1166 unique_rownames,
1167 colnames_mapper,
1168 unique_colnames,
1169 ) = _build_names_mapper(rownames, colnames)
1171 from pandas import DataFrame
1173 data = {
1174 **dict(zip(unique_rownames, index, strict=True)),
1175 **dict(zip(unique_colnames, columns, strict=True)),
1176 }
1177 df = DataFrame(data, index=common_idx)
1179 if values is None:
1180 df["__dummy__"] = 0
1181 kwargs = {"aggfunc": len, "fill_value": 0}
1182 else:
1183 df["__dummy__"] = values
1184 kwargs = {"aggfunc": aggfunc}
1186 # error: Argument 7 to "pivot_table" of "DataFrame" has incompatible type
1187 # "**Dict[str, object]"; expected "Union[...]"
1188 table = df.pivot_table(
1189 "__dummy__",
1190 index=unique_rownames,
1191 columns=unique_colnames,
1192 margins=margins,
1193 margins_name=margins_name,
1194 dropna=dropna,
1195 observed=dropna,
1196 **kwargs, # type: ignore[arg-type]
1197 )
1199 # Post-process
1200 if normalize is not False:
1201 table = _normalize(
1202 table, normalize=normalize, margins=margins, margins_name=margins_name
1203 )
1205 table = table.rename_axis(index=rownames_mapper, axis=0)
1206 table = table.rename_axis(columns=colnames_mapper, axis=1)
1208 return table
1211def _normalize(
1212 table: DataFrame, normalize, margins: bool, margins_name: Hashable = "All"
1213) -> DataFrame:
1214 if not isinstance(normalize, (bool, str)):
1215 axis_subs = {0: "index", 1: "columns"}
1216 try:
1217 normalize = axis_subs[normalize]
1218 except KeyError as err:
1219 raise ValueError("Not a valid normalize argument") from err
1221 if margins is False:
1222 # Actual Normalizations
1223 normalizers: dict[bool | str, Callable] = {
1224 "all": lambda x: x / x.sum(axis=1).sum(axis=0),
1225 "columns": lambda x: x / x.sum(),
1226 "index": lambda x: x.div(x.sum(axis=1), axis=0),
1227 }
1229 normalizers[True] = normalizers["all"]
1231 try:
1232 f = normalizers[normalize]
1233 except KeyError as err:
1234 raise ValueError("Not a valid normalize argument") from err
1236 table = f(table)
1237 table = table.fillna(0)
1239 elif margins is True:
1240 # keep index and column of pivoted table
1241 table_index = table.index
1242 table_columns = table.columns
1243 last_ind_or_col = table.iloc[-1, :].name
1245 # check if margin name is not in (for MI cases) and not equal to last
1246 # index/column and save the column and index margin
1247 if (margins_name not in last_ind_or_col) & (margins_name != last_ind_or_col):
1248 raise ValueError(f"{margins_name} not in pivoted DataFrame")
1249 column_margin = table.iloc[:-1, -1]
1250 index_margin = table.iloc[-1, :-1]
1252 # keep the core table
1253 table = table.iloc[:-1, :-1]
1255 # Normalize core
1256 table = _normalize(table, normalize=normalize, margins=False)
1258 # Fix Margins
1259 if normalize == "columns":
1260 column_margin = column_margin / column_margin.sum()
1261 table = concat([table, column_margin], axis=1)
1262 table = table.fillna(0)
1263 table.columns = table_columns
1265 elif normalize == "index":
1266 index_margin = index_margin / index_margin.sum()
1267 table = table._append_internal(index_margin, ignore_index=True)
1268 table = table.fillna(0)
1269 table.index = table_index
1271 elif normalize == "all" or normalize is True:
1272 column_margin = column_margin / column_margin.sum()
1273 index_margin = index_margin / index_margin.sum()
1274 margin_key: Hashable
1275 if isinstance(index_margin.index, MultiIndex):
1276 # GH#17024 expanding with a partial key is deprecated
1277 nlevels = index_margin.index.nlevels
1278 margin_key = (margins_name,) + ("",) * (nlevels - 1)
1279 else:
1280 margin_key = margins_name
1281 index_margin.loc[margin_key] = 1
1282 table = concat([table, column_margin], axis=1)
1283 table = table._append_internal(index_margin, ignore_index=True)
1285 table = table.fillna(0)
1286 table.index = table_index
1287 table.columns = table_columns
1289 else:
1290 raise ValueError("Not a valid normalize argument")
1292 else:
1293 raise ValueError("Not a valid margins argument")
1295 return table
1298def _get_names(arrs, names, prefix: str = "row") -> list:
1299 if names is None:
1300 names = []
1301 for i, arr in enumerate(arrs):
1302 if isinstance(arr, ABCSeries) and arr.name is not None:
1303 names.append(arr.name)
1304 else:
1305 names.append(f"{prefix}_{i}")
1306 else:
1307 if len(names) != len(arrs):
1308 raise AssertionError("arrays and names must have the same length")
1309 if not isinstance(names, list):
1310 names = list(names)
1312 return names
1315def _build_names_mapper(
1316 rownames: list[str], colnames: list[str]
1317) -> tuple[dict[str, str], list[str], dict[str, str], list[str]]:
1318 """
1319 Given the names of a DataFrame's rows and columns, returns a set of unique row
1320 and column names and mappers that convert to original names.
1322 A row or column name is replaced if it is duplicate among the rows of the inputs,
1323 among the columns of the inputs or between the rows and the columns.
1325 Parameters
1326 ----------
1327 rownames: list[str]
1328 colnames: list[str]
1330 Returns
1331 -------
1332 Tuple(Dict[str, str], List[str], Dict[str, str], List[str])
1334 rownames_mapper: dict[str, str]
1335 a dictionary with new row names as keys and original rownames as values
1336 unique_rownames: list[str]
1337 a list of rownames with duplicate names replaced by dummy names
1338 colnames_mapper: dict[str, str]
1339 a dictionary with new column names as keys and original column names as values
1340 unique_colnames: list[str]
1341 a list of column names with duplicate names replaced by dummy names
1343 """
1344 dup_names = set(rownames) | set(colnames)
1346 rownames_mapper = {
1347 f"row_{i}": name for i, name in enumerate(rownames) if name in dup_names
1348 }
1349 unique_rownames = [
1350 f"row_{i}" if name in dup_names else name for i, name in enumerate(rownames)
1351 ]
1353 colnames_mapper = {
1354 f"col_{i}": name for i, name in enumerate(colnames) if name in dup_names
1355 }
1356 unique_colnames = [
1357 f"col_{i}" if name in dup_names else name for i, name in enumerate(colnames)
1358 ]
1360 return rownames_mapper, unique_rownames, colnames_mapper, unique_colnames