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 re
4from typing import (
5 TYPE_CHECKING,
6 Any,
7)
9import numpy as np
11from pandas.util._decorators import set_module
13from pandas.core.dtypes.common import (
14 is_iterator,
15 is_list_like,
16)
17from pandas.core.dtypes.concat import concat_compat
18from pandas.core.dtypes.missing import notna
20import pandas.core.algorithms as algos
21from pandas.core.indexes.api import MultiIndex
22from pandas.core.reshape.concat import concat
23from pandas.core.tools.numeric import to_numeric
25if TYPE_CHECKING:
26 from collections.abc import Hashable
28 from pandas._typing import AnyArrayLike
30 from pandas import DataFrame
33def ensure_list_vars(arg_vars, variable: str, columns) -> list[Hashable]:
34 if arg_vars is not None:
35 if not is_list_like(arg_vars):
36 return [arg_vars]
37 elif isinstance(columns, MultiIndex) and not isinstance(arg_vars, list):
38 raise ValueError(
39 f"{variable} must be a list of tuples when columns are a MultiIndex"
40 )
41 else:
42 return list(arg_vars)
43 else:
44 return []
47@set_module("pandas")
48def melt(
49 frame: DataFrame,
50 id_vars=None,
51 value_vars=None,
52 var_name=None,
53 value_name: Hashable = "value",
54 col_level=None,
55 ignore_index: bool = True,
56) -> DataFrame:
57 """
58 Unpivot a DataFrame from wide to long format, optionally leaving identifiers set.
60 This function is useful to reshape a DataFrame into a format where one
61 or more columns are identifier variables (`id_vars`), while all other
62 columns are considered measured variables (`value_vars`), and are "unpivoted" to
63 the row axis, leaving just two non-identifier columns, 'variable' and
64 'value'.
66 Parameters
67 ----------
68 frame : DataFrame
69 The DataFrame to unpivot.
70 id_vars : scalar, tuple, list, or ndarray, optional
71 Column(s) to use as identifier variables.
72 value_vars : scalar, tuple, list, or ndarray, optional
73 Column(s) to unpivot. If not specified, uses all columns that
74 are not set as `id_vars`.
75 var_name : scalar, tuple, list, or ndarray, optional
76 Name to use for the 'variable' column. If None it uses
77 ``frame.columns.name`` or 'variable'. Must be a scalar if columns are a
78 MultiIndex.
79 value_name : scalar, default 'value'
80 Name to use for the 'value' column, can't be an existing column label.
81 col_level : scalar, optional
82 If columns are a MultiIndex then use this level to melt.
83 ignore_index : bool, default True
84 If True, original index is ignored. If False, the original index is retained.
85 Index labels will be repeated as necessary.
87 Returns
88 -------
89 DataFrame
90 Unpivoted DataFrame.
92 See Also
93 --------
94 DataFrame.melt : Identical method.
95 pivot_table : Create a spreadsheet-style pivot table as a DataFrame.
96 DataFrame.pivot : Return reshaped DataFrame organized
97 by given index / column values.
98 DataFrame.explode : Explode a DataFrame from list-like
99 columns to long format.
101 Notes
102 -----
103 Reference :ref:`the user guide <reshaping.melt>` for more examples.
105 Examples
106 --------
107 >>> df = pd.DataFrame(
108 ... {
109 ... "A": {0: "a", 1: "b", 2: "c"},
110 ... "B": {0: 1, 1: 3, 2: 5},
111 ... "C": {0: 2, 1: 4, 2: 6},
112 ... }
113 ... )
114 >>> df
115 A B C
116 0 a 1 2
117 1 b 3 4
118 2 c 5 6
120 >>> pd.melt(df, id_vars=["A"], value_vars=["B"])
121 A variable value
122 0 a B 1
123 1 b B 3
124 2 c B 5
126 >>> pd.melt(df, id_vars=["A"], value_vars=["B", "C"])
127 A variable value
128 0 a B 1
129 1 b B 3
130 2 c B 5
131 3 a C 2
132 4 b C 4
133 5 c C 6
135 The names of 'variable' and 'value' columns can be customized:
137 >>> pd.melt(
138 ... df,
139 ... id_vars=["A"],
140 ... value_vars=["B"],
141 ... var_name="myVarname",
142 ... value_name="myValname",
143 ... )
144 A myVarname myValname
145 0 a B 1
146 1 b B 3
147 2 c B 5
149 Original index values can be kept around:
151 >>> pd.melt(df, id_vars=["A"], value_vars=["B", "C"], ignore_index=False)
152 A variable value
153 0 a B 1
154 1 b B 3
155 2 c B 5
156 0 a C 2
157 1 b C 4
158 2 c C 6
160 If you have multi-index columns:
162 >>> df.columns = [list("ABC"), list("DEF")]
163 >>> df
164 A B C
165 D E F
166 0 a 1 2
167 1 b 3 4
168 2 c 5 6
170 >>> pd.melt(df, col_level=0, id_vars=["A"], value_vars=["B"])
171 A variable value
172 0 a B 1
173 1 b B 3
174 2 c B 5
176 >>> pd.melt(df, id_vars=[("A", "D")], value_vars=[("B", "E")])
177 (A, D) variable_0 variable_1 value
178 0 a B E 1
179 1 b B E 3
180 2 c B E 5
181 """
182 if value_name in frame.columns:
183 raise ValueError(
184 f"value_name ({value_name}) cannot match an element in "
185 "the DataFrame columns."
186 )
187 id_vars = ensure_list_vars(id_vars, "id_vars", frame.columns)
188 value_vars_was_not_none = value_vars is not None
189 value_vars = ensure_list_vars(value_vars, "value_vars", frame.columns)
191 # GH61475 - prevent AttributeError when duplicate column in id_vars
192 if len(frame.columns.get_indexer_for(id_vars)) > len(id_vars):
193 raise ValueError("id_vars cannot contain duplicate columns.")
195 if id_vars or value_vars:
196 if col_level is not None:
197 level = frame.columns.get_level_values(col_level)
198 else:
199 level = frame.columns
200 labels = id_vars + value_vars
201 idx = level.get_indexer_for(labels)
202 missing = idx == -1
203 if missing.any():
204 missing_labels = [
205 lab for lab, not_found in zip(labels, missing, strict=True) if not_found
206 ]
207 raise KeyError(
208 "The following id_vars or value_vars are not present in "
209 f"the DataFrame: {missing_labels}"
210 )
211 if value_vars_was_not_none:
212 frame = frame.iloc[:, algos.unique(idx)]
213 else:
214 frame = frame.copy(deep=False)
215 else:
216 frame = frame.copy(deep=False)
218 if col_level is not None: # allow list or other?
219 # frame is a copy
220 frame.columns = frame.columns.get_level_values(col_level)
222 if var_name is None:
223 if isinstance(frame.columns, MultiIndex):
224 if len(frame.columns.names) == len(set(frame.columns.names)):
225 var_name = frame.columns.names
226 else:
227 var_name = [f"variable_{i}" for i in range(len(frame.columns.names))]
228 else:
229 var_name = [
230 frame.columns.name if frame.columns.name is not None else "variable"
231 ]
232 elif is_list_like(var_name):
233 if isinstance(frame.columns, MultiIndex):
234 if is_iterator(var_name):
235 var_name = list(var_name)
236 if len(var_name) > len(frame.columns):
237 raise ValueError(
238 f"{var_name=} has {len(var_name)} items, "
239 f"but the dataframe columns only have {len(frame.columns)} levels."
240 )
241 else:
242 raise ValueError(f"{var_name=} must be a scalar.")
243 else:
244 var_name = [var_name]
246 output_names = (*id_vars, *var_name, value_name)
247 dups = list({x for x in output_names if output_names.count(x) > 1})
248 if dups:
249 raise ValueError(f"melt output columns cannot contain duplicate names: {dups}")
251 num_rows, K = frame.shape
252 num_cols_adjusted = K - len(id_vars)
254 mdata: dict[Hashable, AnyArrayLike] = {}
255 for col in id_vars:
256 id_data = frame.pop(col)
257 if not isinstance(id_data.dtype, np.dtype):
258 # i.e. ExtensionDtype
259 if num_cols_adjusted > 0:
260 mdata[col] = concat([id_data] * num_cols_adjusted, ignore_index=True)
261 else:
262 # We can't concat empty list. (GH 46044)
263 mdata[col] = type(id_data)([], name=id_data.name, dtype=id_data.dtype)
264 else:
265 mdata[col] = np.tile(id_data._values, num_cols_adjusted)
267 mcolumns = id_vars + var_name + [value_name]
269 if frame.shape[1] > 0 and not any(
270 not isinstance(dt, np.dtype) and dt._supports_2d for dt in frame.dtypes
271 ):
272 mdata[value_name] = concat(
273 [frame.iloc[:, i] for i in range(frame.shape[1])], ignore_index=True
274 )._values
275 else:
276 mdata[value_name] = frame._values.ravel("F")
277 for i, col in enumerate(var_name):
278 mdata[col] = frame.columns._get_level_values(i).repeat(num_rows)
280 result = frame._constructor(mdata, columns=mcolumns)
282 if not ignore_index:
283 taker = np.tile(np.arange(len(frame)), num_cols_adjusted)
284 result.index = frame.index.take(taker)
286 return result
289@set_module("pandas")
290def lreshape(
291 data: DataFrame, groups: dict[Any, list[Any]], dropna: bool = True
292) -> DataFrame:
293 """
294 Reshape wide-format data to long. Generalized inverse of DataFrame.pivot.
296 Accepts a dictionary, ``groups``, in which each key is a new column name
297 and each value is a list of old column names that will be "melted" under
298 the new column name as part of the reshape.
300 Parameters
301 ----------
302 data : DataFrame
303 The wide-format DataFrame.
304 groups : dict
305 {new_name : list_of_columns}.
306 dropna : bool, default True
307 Do not include columns whose entries are all NaN.
309 Returns
310 -------
311 DataFrame
312 Reshaped DataFrame.
314 See Also
315 --------
316 melt : Unpivot a DataFrame from wide to long format, optionally leaving
317 identifiers set.
318 pivot : Create a spreadsheet-style pivot table as a DataFrame.
319 DataFrame.pivot : Pivot without aggregation that can handle
320 non-numeric data.
321 DataFrame.pivot_table : Generalization of pivot that can handle
322 duplicate values for one index/column pair.
323 DataFrame.unstack : Pivot based on the index values instead of a
324 column.
325 wide_to_long : Wide panel to long format. Less flexible but more
326 user-friendly than melt.
328 Examples
329 --------
330 >>> data = pd.DataFrame(
331 ... {
332 ... "hr1": [514, 573],
333 ... "hr2": [545, 526],
334 ... "team": ["Red Sox", "Yankees"],
335 ... "year1": [2007, 2007],
336 ... "year2": [2008, 2008],
337 ... }
338 ... )
339 >>> data
340 hr1 hr2 team year1 year2
341 0 514 545 Red Sox 2007 2008
342 1 573 526 Yankees 2007 2008
344 >>> pd.lreshape(data, {"year": ["year1", "year2"], "hr": ["hr1", "hr2"]})
345 team year hr
346 0 Red Sox 2007 514
347 1 Yankees 2007 573
348 2 Red Sox 2008 545
349 3 Yankees 2008 526
350 """
351 mdata = {}
352 pivot_cols = []
353 all_cols: set[Hashable] = set()
354 K = len(next(iter(groups.values())))
355 for target, names in groups.items():
356 if len(names) != K:
357 raise ValueError("All column lists must be same length")
358 to_concat = [data[col]._values for col in names]
360 mdata[target] = concat_compat(to_concat)
361 pivot_cols.append(target)
362 all_cols = all_cols.union(names)
364 id_cols = list(data.columns.difference(all_cols)) # type: ignore[arg-type]
365 for col in id_cols:
366 mdata[col] = np.tile(data[col]._values, K)
368 if dropna:
369 mask = np.ones(len(mdata[pivot_cols[0]]), dtype=bool)
370 for c in pivot_cols:
371 mask &= notna(mdata[c])
372 if not mask.all():
373 mdata = {k: v[mask] for k, v in mdata.items()}
375 return data._constructor(mdata, columns=id_cols + pivot_cols)
378@set_module("pandas")
379def wide_to_long(
380 df: DataFrame, stubnames, i, j, sep: str = "", suffix: str = r"\d+"
381) -> DataFrame:
382 r"""
383 Unpivot a DataFrame from wide to long format.
385 Less flexible but more user-friendly than melt.
387 With stubnames ['A', 'B'], this function expects to find one or more
388 group of columns with format
389 A-suffix1, A-suffix2,..., B-suffix1, B-suffix2,...
390 You specify what you want to call this suffix in the resulting long format
391 with `j` (for example `j='year'`)
393 Each row of these wide variables are assumed to be uniquely identified by
394 `i` (can be a single column name or a list of column names)
396 All remaining variables in the data frame are left intact.
398 Parameters
399 ----------
400 df : DataFrame
401 The wide-format DataFrame.
402 stubnames : str or list-like
403 The stub name(s). The wide format variables are assumed to
404 start with the stub names.
405 i : str or list-like
406 Column(s) to use as id variable(s).
407 j : str
408 The name of the sub-observation variable. What you wish to name your
409 suffix in the long format.
410 sep : str, default ""
411 A character indicating the separation of the variable names
412 in the wide format, to be stripped from the names in the long format.
413 For example, if your column names are A-suffix1, A-suffix2, you
414 can strip the hyphen by specifying `sep='-'`.
415 suffix : str, default '\\d+'
416 A regular expression capturing the wanted suffixes. '\\d+' captures
417 numeric suffixes. Suffixes with no numbers could be specified with the
418 negated character class '\\D+'. You can also further disambiguate
419 suffixes, for example, if your wide variables are of the form A-one,
420 B-two,.., and you have an unrelated column A-rating, you can ignore the
421 last one by specifying `suffix='(!?one|two)'`. When all suffixes are
422 numeric, they are cast to int64/float64.
424 Returns
425 -------
426 DataFrame
427 A DataFrame that contains each stub name as a variable, with new index
428 (i, j).
430 See Also
431 --------
432 melt : Unpivot a DataFrame from wide to long format, optionally leaving
433 identifiers set.
434 pivot : Create a spreadsheet-style pivot table as a DataFrame.
435 DataFrame.pivot : Pivot without aggregation that can handle
436 non-numeric data.
437 DataFrame.pivot_table : Generalization of pivot that can handle
438 duplicate values for one index/column pair.
439 DataFrame.unstack : Pivot based on the index values instead of a
440 column.
442 Notes
443 -----
444 All extra variables are left untouched. This simply uses
445 `pandas.melt` under the hood, but is hard-coded to "do the right thing"
446 in a typical case.
448 Examples
449 --------
450 >>> np.random.seed(123)
451 >>> df = pd.DataFrame(
452 ... {
453 ... "A1970": {0: "a", 1: "b", 2: "c"},
454 ... "A1980": {0: "d", 1: "e", 2: "f"},
455 ... "B1970": {0: 2.5, 1: 1.2, 2: 0.7},
456 ... "B1980": {0: 3.2, 1: 1.3, 2: 0.1},
457 ... "X": dict(zip(range(3), np.random.randn(3), strict=True)),
458 ... }
459 ... )
460 >>> df["id"] = df.index
461 >>> df
462 A1970 A1980 B1970 B1980 X id
463 0 a d 2.5 3.2 -1.085631 0
464 1 b e 1.2 1.3 0.997345 1
465 2 c f 0.7 0.1 0.282978 2
466 >>> pd.wide_to_long(df, ["A", "B"], i="id", j="year")
467 ... # doctest: +NORMALIZE_WHITESPACE
468 X A B
469 id year
470 0 1970 -1.085631 a 2.5
471 1 1970 0.997345 b 1.2
472 2 1970 0.282978 c 0.7
473 0 1980 -1.085631 d 3.2
474 1 1980 0.997345 e 1.3
475 2 1980 0.282978 f 0.1
477 With multiple id columns
479 >>> df = pd.DataFrame(
480 ... {
481 ... "famid": [1, 1, 1, 2, 2, 2, 3, 3, 3],
482 ... "birth": [1, 2, 3, 1, 2, 3, 1, 2, 3],
483 ... "ht1": [2.8, 2.9, 2.2, 2, 1.8, 1.9, 2.2, 2.3, 2.1],
484 ... "ht2": [3.4, 3.8, 2.9, 3.2, 2.8, 2.4, 3.3, 3.4, 2.9],
485 ... }
486 ... )
487 >>> df
488 famid birth ht1 ht2
489 0 1 1 2.8 3.4
490 1 1 2 2.9 3.8
491 2 1 3 2.2 2.9
492 3 2 1 2.0 3.2
493 4 2 2 1.8 2.8
494 5 2 3 1.9 2.4
495 6 3 1 2.2 3.3
496 7 3 2 2.3 3.4
497 8 3 3 2.1 2.9
498 >>> long_format = pd.wide_to_long(df, stubnames="ht", i=["famid", "birth"], j="age")
499 >>> long_format
500 ... # doctest: +NORMALIZE_WHITESPACE
501 ht
502 famid birth age
503 1 1 1 2.8
504 2 3.4
505 2 1 2.9
506 2 3.8
507 3 1 2.2
508 2 2.9
509 2 1 1 2.0
510 2 3.2
511 2 1 1.8
512 2 2.8
513 3 1 1.9
514 2 2.4
515 3 1 1 2.2
516 2 3.3
517 2 1 2.3
518 2 3.4
519 3 1 2.1
520 2 2.9
522 Going from long back to wide just takes some creative use of `unstack`
524 >>> wide_format = long_format.unstack()
525 >>> wide_format.columns = wide_format.columns.map("{0[0]}{0[1]}".format)
526 >>> wide_format.reset_index()
527 famid birth ht1 ht2
528 0 1 1 2.8 3.4
529 1 1 2 2.9 3.8
530 2 1 3 2.2 2.9
531 3 2 1 2.0 3.2
532 4 2 2 1.8 2.8
533 5 2 3 1.9 2.4
534 6 3 1 2.2 3.3
535 7 3 2 2.3 3.4
536 8 3 3 2.1 2.9
538 Less wieldy column names are also handled
540 >>> np.random.seed(0)
541 >>> df = pd.DataFrame(
542 ... {
543 ... "A(weekly)-2010": np.random.rand(3),
544 ... "A(weekly)-2011": np.random.rand(3),
545 ... "B(weekly)-2010": np.random.rand(3),
546 ... "B(weekly)-2011": np.random.rand(3),
547 ... "X": np.random.randint(3, size=3),
548 ... }
549 ... )
550 >>> df["id"] = df.index
551 >>> df # doctest: +NORMALIZE_WHITESPACE, +ELLIPSIS
552 A(weekly)-2010 A(weekly)-2011 B(weekly)-2010 B(weekly)-2011 X id
553 0 0.548814 0.544883 0.437587 0.383442 0 0
554 1 0.715189 0.423655 0.891773 0.791725 1 1
555 2 0.602763 0.645894 0.963663 0.528895 1 2
557 >>> pd.wide_to_long(df, ["A(weekly)", "B(weekly)"], i="id", j="year", sep="-")
558 ... # doctest: +NORMALIZE_WHITESPACE
559 X A(weekly) B(weekly)
560 id year
561 0 2010 0 0.548814 0.437587
562 1 2010 1 0.715189 0.891773
563 2 2010 1 0.602763 0.963663
564 0 2011 0 0.544883 0.383442
565 1 2011 1 0.423655 0.791725
566 2 2011 1 0.645894 0.528895
568 If we have many columns, we could also use a regex to find our
569 stubnames and pass that list on to wide_to_long
571 >>> stubnames = sorted(
572 ... set(
573 ... [
574 ... match[0]
575 ... for match in df.columns.str.findall(r"[A-B]\(.*\)").values
576 ... if match != []
577 ... ]
578 ... )
579 ... )
580 >>> list(stubnames)
581 ['A(weekly)', 'B(weekly)']
583 All of the above examples have integers as suffixes. It is possible to
584 have non-integers as suffixes.
586 >>> df = pd.DataFrame(
587 ... {
588 ... "famid": [1, 1, 1, 2, 2, 2, 3, 3, 3],
589 ... "birth": [1, 2, 3, 1, 2, 3, 1, 2, 3],
590 ... "ht_one": [2.8, 2.9, 2.2, 2, 1.8, 1.9, 2.2, 2.3, 2.1],
591 ... "ht_two": [3.4, 3.8, 2.9, 3.2, 2.8, 2.4, 3.3, 3.4, 2.9],
592 ... }
593 ... )
594 >>> df
595 famid birth ht_one ht_two
596 0 1 1 2.8 3.4
597 1 1 2 2.9 3.8
598 2 1 3 2.2 2.9
599 3 2 1 2.0 3.2
600 4 2 2 1.8 2.8
601 5 2 3 1.9 2.4
602 6 3 1 2.2 3.3
603 7 3 2 2.3 3.4
604 8 3 3 2.1 2.9
606 >>> long_format = pd.wide_to_long(
607 ... df, stubnames="ht", i=["famid", "birth"], j="age", sep="_", suffix=r"\w+"
608 ... )
609 >>> long_format
610 ... # doctest: +NORMALIZE_WHITESPACE
611 ht
612 famid birth age
613 1 1 one 2.8
614 two 3.4
615 2 one 2.9
616 two 3.8
617 3 one 2.2
618 two 2.9
619 2 1 one 2.0
620 two 3.2
621 2 one 1.8
622 two 2.8
623 3 one 1.9
624 two 2.4
625 3 1 one 2.2
626 two 3.3
627 2 one 2.3
628 two 3.4
629 3 one 2.1
630 two 2.9
631 """
633 def get_var_names(df, stub: str, sep: str, suffix: str):
634 regex = rf"^{re.escape(stub)}{re.escape(sep)}{suffix}$"
635 return df.columns[df.columns.str.match(regex)]
637 def melt_stub(df, stub: str, i, j, value_vars, sep: str):
638 newdf = melt(
639 df,
640 id_vars=i,
641 value_vars=value_vars,
642 value_name=stub.rstrip(sep),
643 var_name=j,
644 )
645 newdf[j] = newdf[j].str.replace(re.escape(stub + sep), "", regex=True)
647 # GH17627 Cast numerics suffixes to int/float
648 try:
649 newdf[j] = to_numeric(newdf[j])
650 except (TypeError, ValueError, OverflowError):
651 # TODO: anything else to catch?
652 pass
654 return newdf.set_index([*i, j])
656 if not is_list_like(stubnames):
657 stubnames = [stubnames]
658 else:
659 stubnames = list(stubnames)
661 if df.columns.isin(stubnames).any():
662 raise ValueError("stubname can't be identical to a column name")
664 if not is_list_like(i):
665 i = [i]
666 else:
667 i = list(i)
669 if df[i].duplicated().any():
670 raise ValueError("the id variables need to uniquely identify each row")
672 _melted = []
673 value_vars_flattened = []
674 for stub in stubnames:
675 value_var = get_var_names(df, stub, sep, suffix)
676 value_vars_flattened.extend(value_var)
677 _melted.append(melt_stub(df, stub, i, j, value_var, sep))
679 melted = concat(_melted, axis=1)
680 id_vars = df.columns.difference(value_vars_flattened)
681 new = df[id_vars]
683 if len(i) == 1:
684 return new.set_index(i).join(melted)
685 else:
686 return new.merge(melted.reset_index(), on=i).set_index([*i, j])