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