Wrangling Data with pandas

Missing data means a value is unknown or unavailable. pandas stores a special marker in its place. Detect the marker before calculating totals or deciding which rows are usable.

Recognize the markers

The marker depends on the dtype. Float columns use NaN, datetime columns use NaT, and nullable types such as "Int64", "boolean", and "string" use pd.NA. pandas 3 infers text columns as str, with NaN for missing text. Python's None is also recognized as missing. Use import pandas as pd and import numpy as np for the examples.

df.isna() produces a Boolean table with the same rows and columns as df. A True marks a gap; df.notna() marks present values. The aliases isnull() and notnull() do the same jobs. Do not compare a column with np.nan: NaN is unequal even to itself, so s == np.nan misses the gaps.

Count cells and affected rows

Consider these weather readings:

A Boolean True counts as 1, so df.isna().sum() gives 0 missing cities, 2 temperatures, and 2 rainfall readings. df.isna().mean() gives shares of 0.0, 0.5, and 0.5. Each column's share uses all four rows as its denominator.

To find affected rows, use df.isna().any(axis=1). It returns False, True, True, True; summing gives 3. In contrast, df.isna().sum().sum() gives 4 missing cells. Kyiv contributes two cells but only one affected row. Use the measure that answers your question.

Recognize hidden missing codes

An instrument may record an unavailable reading as -999, "-", or "unknown". These are ordinary values until you convert them. After confirming the code's meaning, use df.replace([-999, "unknown"], np.nan). During CSV loading, na_values=[-999, "unknown"] adds these codes to the recognized markers. Empty CSV fields and common strings such as "NA", "n/a", and "NULL" are already recognized by default.

A zero rainfall reading can mean no rain, so replacing every zero would destroy real observations. Also, an empty string created in memory, "", and a string containing spaces are not missing to isna(). Inspect stored text as well as marker counts. To include missing categories in a frequency table, use value_counts(dropna=False).

Check calculations and dtypes

For s = pd.Series([4, np.nan, 8]), s.mean() is 6.0: it uses the two known values. s.count() is 2, while len(s) is 3. Dividing s.sum() by len(s) gives 4.0 and wrongly treats the gap as zero. Most statistics skip gaps by default; an all-missing column has sum 0 but mean NaN.

A gap can change storage too. pd.Series([3, None, 5]) becomes float64, holding 3.0, NaN, and 5.0. Giving dtype="Int64" keeps integer values with <NA> for the gap. The capital I identifies pandas' nullable integer dtype.

Dropping a row removes its known values along with its gaps. Filling a gap keeps the row but introduces an estimate. Choose based on what the observation means and which columns the analysis needs.

Control which rows are removed

df.dropna() removes every row with any missing value. On the weather table, only Lima remains. Narrow the decision with subset, or require a minimum number of present cells with thresh.

axis="columns" drops columns with gaps and keeps only city here. On a wide table, the default row rule may discard much of the data because a gap in any column is enough. Compare len(df) before and after removal.

Assign the result

Learn the reassignment form: df = df.dropna(). By default, cleaning methods return a new object. pandas 3.1 deprecates inplace=True for methods including dropna, drop_duplicates, and rename.

For one column, write df["temp_c"] = df["temp_c"].fillna(0). A chained call such as df["temp_c"].fillna(0, inplace=True) never updates the parent DataFrame under pandas 3 Copy-on-Write. Assignment makes the destination explicit.

Fill with a compatible value

A per-column dictionary lets a mixed table receive suitable values: df.fillna({"rain_mm": 0, "city": "Unknown"}). Columns omitted from the dictionary keep their gaps. A scalar such as 0 is convenient for an all-numeric table whose columns all accept it. Filling text with a number is incompatible; pandas 3.1 warns about this and a future version will reject it.

The known temperatures are 18.0 and 31.0. Their mean is 24.5, so df["temp_c"].fillna(df["temp_c"].mean()) puts 24.5 into both gaps. An extreme value can pull the mean strongly; a median is less sensitive. Before filling, keep an indicator with df["temp_missing"] = df["temp_c"].isna() so estimated and observed values remain distinguishable.

Mean filling reduces the apparent spread of the column. A distant fixed value can increase it. Filling rainfall with 0 asserts that no rain fell, which is inappropriate for a sensor that was off.

Use neighboring readings when order matters

ffill() carries the last known value forward; bfill() carries the next known value backward. For 12.0, NaN, NaN, NaN, 15.0, forward filling gives four 12.0 values followed by 15.0. ffill(limit=1) fills only the first gap in that run. A leading gap has no previous value and stays missing. Use ffill() directly; fillna(method="ffill") was removed in pandas 3.

interpolate() estimates between neighboring positions. For 10, NaN, NaN, 16, linear interpolation gives 10, 12, 14, 16. It fits smoothly changing measurements in a meaningful order. Carrying or interpolating across unrelated cities would impose a relationship the rows do not have.

After any operation, rerun isna().sum() as well as checking row counts. A successful method call tells you that the code ran; these checks tell you what changed.

Two rows can look alike without describing the same event. Before removing duplicates, decide which values identify one record. An order ID may identify an order; customer and date alone may identify several legitimate purchases.

Mark the extra copies

df.duplicated() returns one Boolean per row. By default it compares every column, keeps the first occurrence, and marks later copies True. Summing the result counts extra copies, not all members of repeated groups.

Consider six order rows with IDs 101, 102, 102, 103, 104, 104. The two 102 rows are identical. The two 104 rows have different recorded weights, 0.5 kg and 0.8 kg. With every column compared, there is one extra copy: df.duplicated().sum() is 1. df.drop_duplicates() keeps five rows because the two different 104 rows are not exact copies.

Compare the columns that define identity

df.duplicated(subset=["order_id"]) compares IDs alone. It marks the second 102 and second 104, giving two extra copies. The other column values no longer affect this decision. Inspect the conflicting weights before choosing which order record to keep.

df.drop_duplicates(subset="order_id", keep="last") keeps the 0.8 kg version of order 104 in this order. Keeping the last row does not itself mean keeping the newest record. Establish that order first:

latest = df.sort_values("updated").drop_duplicates(
    subset="customer_id", keep="last"
)

Sorting by updated puts older records before newer ones. The duplicate check then compares only customer_id, so differing update times do not prevent two rows from being recognized as versions of the same customer.

Separate row values from index labels

These methods compare values, ignoring index labels. Identical rows labeled 4 and 9 still count as duplicates. Missing values at corresponding positions count as equal for this check. To test repeated labels themselves, use df.index.duplicated().

Exact comparison also preserves differences in case and whitespace. The values " Lisbon", "lisbon", and "LISBON " are distinct. Clean with .str.strip().str.lower() before checking duplicates when those spellings represent the same city. Otherwise nunique() will count three names for one place.

Check uniqueness where the relationship needs it

A lookup table should have one row per lookup key when each record must receive one set of details. lookup["customer_id"].is_unique checks that requirement. Repeated customer IDs in an orders table are normal because a customer can place several orders.

If a lookup key repeats, a later merge repeats each matching order for every matching lookup row. Removing duplicates from the orders would discard genuine purchases without fixing the lookup. Check the intended key on the correct table, inspect conflicting records, and compare the row count after removal.

A replacement edits matching values, a lookup translates values, and a rename changes labels. These jobs can look similar in code but produce different results when a value is absent from a dictionary.

Replace exact values

s.replace("N/A", np.nan) changes only cells equal to "N/A". A list can name several old values: s.replace(["-", "?"], np.nan). A dictionary provides separate replacements, such as s.replace({"M": "male", "F": "female"}). Unmatched values remain as they were.

For a whole table, a nested dictionary restricts the edit to one column: df.replace({"status": {0: "open", 1: "closed"}}). This avoids changing unrelated zeros and ones elsewhere. Assign the returned result to keep the change.

replace matches entire cells. With "Main St" and "St", s.replace("St", "Street") changes only "St". To edit parts of a string, use s.str.replace("St", "Street", regex=False), which also produces "Main Street".

Translate with a lookup

For codes "PT", "JP", and "KE", compare the same dictionary in two methods:

A dictionary passed to map defines the available outputs. A missing key has no output and becomes missing. Check isna().sum() afterward so an incomplete lookup is visible.

map also accepts a function: s.map(lambda x: x * 1000) converts kilometer values to meters. For this arithmetic, s * 1000 is simpler and faster because it operates on the whole column. DataFrame.map applies a function cell by cell; it replaces the removed applymap method.

Convert text to numbers

astype requests a dtype, such as "float64", "Int64", or "category". It raises an error if a value cannot be converted. For text "12", "7.5", and "n/a", pd.to_numeric(s, errors="coerce") instead produces 12.0, 7.5, and NaN. Coercion preserves readable numbers but requires inspection of the failures.

Formatting can block conversion even when the number is valid. For "1,200" and "950", first remove the thousands separator:

cleaned = s.str.replace(",", "", regex=False)
numbers = pd.to_numeric(cleaned, errors="coerce")
failed = s.notna() & numbers.isna()

Here failed marks values that were present before conversion but became missing. Comparing missing counts alone gives a useful total; the mask also locates the original text to inspect. Remove known units from measured text in the same deliberate way before converting.

Rename the labels

df.rename(columns={"Qty": "quantity"}) changes a column heading, leaving its cells unchanged. df.rename(columns=str.lower) lowercases all headings. A sequence such as df.columns.str.strip().str.lower().str.replace(" ", "_", regex=False) turns "Total KG " into total_kg; assign it to df.columns.

Use replace or map for values inside a column. Use rename for the labels you select those values by.

Some analyses need exact measurements, while others need groups or comparable numeric ranges. Keep the original meaning clear: binning creates categories, scaling changes the numeric scale, and capping replaces extreme values.

Put values into intervals

pd.cut(ages, [18, 30, 45, 65]) uses the intervals (18, 30], (30, 45], and (45, 65]. The parenthesis excludes the left endpoint; the square closing symbol includes the right endpoint. Ages 19, 25, and 30 enter the first interval, 41 the second, and 64 the third. Age 18 becomes missing because it is on the excluded left edge.

include_lowest=True includes that lowest value in the first interval. right=False instead makes left-closed intervals such as [18, 30), so 30 enters the second interval. Values outside all intervals remain missing. Test the exact edges, as well as typical values between them.

There must be one label per interval, one fewer than the number of edges. With labels=["18-30", "31-45", "46-65"] and include_lowest=True, the six ages above give counts 4, 1, and 1. These labels describe whole-number ages; the interval boundaries still govern assignment.

Choose boundaries or group sizes

For 2, 4, 6, 8, 10, 12, 14, 16, qcut gives two values per quartile group. Equal-count groups need not cover equal numeric widths.

Scale without creating groups

Min-max scaling uses (x - x.min()) / (x.max() - x.min()). For 10, 20, 40, the range is 30: the results are 0, about 0.333, and 1. Both the minimum and maximum are needed; dividing by the maximum alone is a different operation. A constant column has a zero range, so this expression cannot give it a useful scaled spread.

Standardizing uses (x - x.mean()) / x.std(). For 10, 20, 30, the mean is 20 and pandas' default sample standard deviation is 10. Subtracting 20 and dividing by 10 gives z-scores -1, 0, and 1. A z-score describes distance from the mean in standard-deviation units, not an interval label.

Cap and encode

pd.Series([5, 12, 250, 8]).clip(upper=100) gives 5, 12, 100, 8. Unlike a filter, clip keeps the row and replaces the extreme number. Inspect large values with describe() or a filter before capping: a genuine large measurement is still data.

pd.get_dummies(s) creates one Boolean indicator column per category. With red, green, red, blue, each row is True only in its color's column. Use dtype=int for zeros and ones. Missing values give all-false indicators unless dummy_na=True adds a missing-value column. For ordinary least-squares regression with an intercept, drop_first=True removes a redundant category column; other models may retain every category. With one category per row, the full set of indicators sums to the intercept's constant 1, creating linear dependence.

For cells containing several tags, s.str.get_dummies(sep="|") splits a value such as "Action|Comedy" into separate indicators. Binned intervals can also be passed to get_dummies, giving a column for each range.

A person sees the same city in " Lisbon", "lisbon", and "LISBON ". pandas sees three different strings. Cleaning a text column gives later counts, duplicate checks, and merges a consistent set of values to compare.

Apply string methods across a Series

Python's "Lima ".strip() works on one string. For a column, use the .str accessor: s.str.strip() applies trimming to every non-missing value. s.lower() raises an AttributeError because the Series itself has no lower method. Use s.str.lower().

pandas 3 normally infers text as dtype str, replacing the older object default. The str dtype holds text and missing values, with NaN for missing text. String methods work on both types. Assign text to a str column; convert a number to text first if it belongs there as text.

Combine compatible cleaning operations and assign the result:

df["city"] = df["city"].str.strip().str.lower()

The three Lisbon spellings become "lisbon". strip() removes outer whitespace, not spaces inside a name. upper() makes all letters uppercase, while title() capitalizes each word, turning "new york" into "New York". Choose one consistent form for keys and counts.

Split structured strings

For "Silva, Ana" and "Tanaka, Yui", s.str.split(", ") returns lists such as ["Silva", "Ana"]. Add expand=True to produce separate columns. Retrieve a piece from each list with .str[0] or .str.get(1).

If only the first separator should divide a value, use n=1. Splitting "a,b,c" at commas with n=1 gives "a" and "b,c". Without a limit, it gives three pieces. Match the separator exactly, including any space that is part of the format.

Search and filter

s.str.contains("lis", case=False) checks for text anywhere while ignoring case. s.str.startswith("ID") checks the beginning; s.str.endswith(".csv") checks the end. The Boolean results can select rows:

mask = df["city"].str.contains("san", case=False, na=False)
selected = df[mask]

For the pandas 3 str dtype, missing text gives False in these search tests. Writing na=False also makes the intended handling clear when using nullable string data. contains reads a regex pattern by default; add regex=False for literal text that contains special characters.

Replace parts and build new strings

s.str.replace("kg", "", regex=False) removes "kg" wherever it appears. s.replace("kg", "") changes only cells that are exactly "kg". This distinction matters for values such as "12 kg".

str.replace treats its search text literally by default. For "3.5 kg", .str.replace(".", "", regex=False) removes the period and gives "35 kg". With regex=True, the period is a pattern for almost any character and removes all characters in this single-line example. Never use that change to parse a decimal measurement.

s.str.len() counts characters; a missing value produces NaN and can make the result a float column. s.str[:3] extracts the first three characters. To combine columns, df["first"] + " " + df["last"] joins Ana and Silva as "Ana Silva"; str.cat is another joining method. Each operation returns a result, so save it in the column you intend to use.

A regular expression, or regex, describes a pattern in text. It can recognize a quantity even when the number of digits or the spacing varies. Start with a small pattern whose parts you can explain.

Read the pattern pieces

Write Python patterns as raw strings, such as r"\d+", so Python preserves their backslashes. A period matches any character except a newline by default. Escape it as r"\." to find an actual period. For a plain-text search, regex=False avoids pattern interpretation entirely.

Choose how much text must match

contains finds a pattern anywhere, match starts at the beginning, and fullmatch requires the entire value to fit. For "123a", s.str.contains(r"\d+") and s.str.match(r"\d+") succeed, but s.str.fullmatch(r"\d+") fails. Use the whole-string test when checking a complete code. case=False or flags=re.IGNORECASE after import re allows case-insensitive matching where supported.

Capture a measurement

Take "Rice 5 kg", "Flour 2kg", "Salt", and "Beans 12 kg, 3 kg". The pattern r"(\d+)\s*kg" means one or more digits captured as a group, followed by zero or more spaces and then "kg".

quantity = s.str.extract(r"(\d+)\s*kg", expand=False)
quantity = pd.to_numeric(quantity)

The extracted strings are "5", "2", a missing value, and "12". Conversion gives numeric values 5.0, 2.0, NaN, and 12.0. extract takes the first match in each row, so the second quantity in the beans description is not included. Without expand=False, a single group returns a one-column DataFrame.

Named groups label the output. s.str.extract(r"(?P<year>\d{4})-(?P<month>\d{2})") finds "2024-03" inside "Report 2024-03 final" and returns text columns year and month. The pattern recognizes the digit layout; numeric conversion is a separate step.

Find every match or clean by pattern

findall(r"\d+") gives a list per row, including ["12", "3"] for the beans text and [] for "Salt". count(r"\d+") counts matches. extractall gives one row per match with an extra index level named match.

With regex=True, s.str.replace(r"\s+", " ", regex=True) reduces runs of whitespace to one space. r"\D" means a non-digit, so removing it turns "+351 912-345-678" into "351912345678". The uppercase forms \S and \W similarly negate whitespace and word-character classes.

Repetition is greedy by default. In "<b>x</b>", r"<.*>" reaches from the first opening symbol to the last closing symbol. Removing that match removes everything. The lazy pattern r"<.*?>" matches each tag separately and leaves "x". Check sample outputs before using a pattern across a column.

A column with a few values repeated many times can use the category dtype. It stores the distinct labels once and represents each row with a small integer code. The visible value is still the label.

Understand categories and codes

For sizes M, S, L, M, M, s.astype("category") infers the categories L, M, S in alphabetical order. The codes are 1, 2, 0, 1, 1. Use s.cat.categories to inspect the labels and s.cat.codes to inspect the identifiers. A code of 0 means the first stored category; it is not a measured quantity.

Repeated labels can save considerable memory. With up to 126 categories, codes use one byte each; at 127 categories they use two bytes. In a pandas 3.1 setup with PyArrow, three country names repeated across 3,000,000 rows used about 3 MB as categorical data, about 42 MB as str, and about 165 MB as object. The text storage backend affects these figures. A column with almost all unique values gets no benefit from storing a category list plus a code per row.

Define meaningful order

Alphabetical size order puts L before M before S. Define the actual size order explicitly:

size_type = pd.CategoricalDtype(["S", "M", "L"], ordered=True)
sizes = s.astype(size_type)

Now sorting follows S, M, L, comparisons such as sizes > "S" are meaningful, and sizes.max() is "L" when L occurs. For unordered labels such as countries, equality tests work but greater-than comparisons raise a TypeError. as_ordered() uses the existing category sequence; inspect it before declaring that sequence meaningful.

Change the allowed labels

You cannot assign "XL" to a categorical that allows only S, M, and L. First assign the expanded result, sizes = sizes.cat.add_categories("XL"), then assign the new value.

Converting text to a fixed category list can turn unlisted values into missing values. pandas 3.1 warns that this lossy conversion will become an error. Check the source values with value_counts() before conversion, especially after cleaning spellings.

The .cat methods serve different purposes:

• rename_categories({"S": "small"}) changes a label while keeping its members.
• remove_categories("L") removes an allowed label and makes existing L values missing.
• set_categories([...]) replaces the whole category list.
• reorder_categories([...], ordered=True) changes the order without changing which labels are allowed.
• remove_unused_categories() removes labels with no current members.

value_counts() displays every allowed category, including those with zero observations. With an S/M/L type containing only S and M, L still appears with count 0. This exposes an empty group that ordinary text data has no stored label for.

Keep categories useful downstream

cut produces categorical intervals, and get_dummies converts category membership into indicator columns. Giving unordered country codes to a model as numbers would impose an artificial order and distance. Indicators avoid that interpretation.

Concatenating categorical columns with different category lists normally loses the categorical dtype. Align their category lists first, or use pd.concat(..., union_categories=True) in pandas 3.1 when combining the lists is intended. Inspect both the resulting dtype and the category order.

A merge matches rows through key values and places their information side by side. The key must describe the same thing in both tables, such as the customer ID on an order and the customer ID in a lookup table.

Name the key and choose the join

Use orders.merge(customers, on="customer_id"). Without on, pandas uses all column names shared by both tables. An unrelated shared column such as date could then prevent valid customer matches.

Suppose orders have customer IDs 10, 20, 20, 30, 50. The customer table has one row each for 10, 20, 30, 40.

Inner is the default. For a left merge, customer 50 has missing customer details. A right merge includes customer 40 without an order, plus both orders for 20. An outer merge includes both unmatched keys. A cross merge needs no key because it deliberately pairs every row. pandas 3 also provides left_anti and right_anti for rows with no matching key on the other side.

Check how many matches a row can have

Two left rows for key 20 and three right rows for key 20 produce six pairs. The merge does not choose one right row. In the example, adding a second customer lookup row for 20 increases the inner result from four rows to six.

Express the intended relationship with validate:

merged = orders.merge(
    customers, on="customer_id", how="left",
    validate="many_to_one"
)

This allows many orders per customer but requires unique right-hand customer IDs. A repeated right key raises MergeError. one_to_one requires uniqueness on both sides; one_to_many requires it on the left. many_to_many allows repeats on both sides and does not check uniqueness.

Investigate unmatched rows

An outer merge with indicator=True adds _merge. Here its counts are four both, one left_only, and one right_only. These are rows, not distinct keys: repeated orders can produce several both rows for one customer.

To count distinct unmatched order-side keys, use m.loc[m["_merge"] == "left_only", "customer_id"].nunique(). Inspecting the rows can reveal missing lookup entries or inconsistent text.

Control names and key formats

If keys have different names, use left_on="customer_id", right_on="id". Both key columns remain available. Several keys can be matched together with on=["city", "year"]; both must agree.

Overlapping non-key names receive _x and _y. Choose meaningful suffixes such as suffixes=("_order", "_ship") to keep the origins clear.

Keys need compatible dtypes and equal values. Numeric 1 can match numeric 1.0, but integer and text key columns can raise a merge error. Convert their formats deliberately. "ana" and "Ana " differ as text; standardize case and outer spaces before merging when they identify the same person.

Missing keys also need inspection: pandas matches missing keys on one side with missing keys on the other. If both sides contain several such rows, those matches can multiply too.

An index can hold the key you want to match. pandas uses those labels when joining tables, patching missing values, or adding two Series. Row position alone does not decide the matches.

Join on index labels

left.join(right) matches the two indexes and defaults to a left join. In contrast, merge defaults to an inner join. You can also write left.merge(right, left_index=True, right_index=True) when both keys are in the index.

Suppose stations has index labels A, B, C and elevations 120, 850, 15 m. A readings table has station index labels A, B, B, D. readings.join(stations) keeps four rows and adds elevations 120, 850, 850, and NaN. The two B readings both receive the B elevation. With how="inner", only the three A/B readings survive.

If the readings keep station IDs in an ordinary column, use readings.join(stations, on="station"). The left column matches the right index. The equivalent merge form is readings.merge(stations, left_on="station", right_index=True, how="left").

Keep the key location explicit

set_index("station") moves the station column into the index. reset_index() restores it as an ordinary column. These let you choose a layout that makes the match easy to inspect. left.join([t1, t2]) can join several tables sharing index labels at once.

If both joined tables contain a non-key column such as source, join raises a ValueError unless you distinguish the names. In pandas 3.1, write suffixes=("_left", "_right"); this replaces the older lsuffix and rsuffix arguments. The suffixes keep both versions without deciding which value is authoritative.

Patch gaps from a backup

a.combine_first(b) keeps known values in a, fills its gaps from b at matching labels, and includes labels found only in b.

The result keeps 1 and 3 because the first source already knows them. It uses 20 to fill the gap and adds label d with 40. Reversing the call changes which source has priority. This is useful when a main data source should override a backup, even when their row orders differ.

Recognize alignment in arithmetic

Let x have a = 5 and b = 7; let y have b = 1 and c = 2. x + y gives b = 8, with missing results at a and c because each lacks one operand. It does not add the first value of x to the first value of y.

x.add(y, fill_value=0) supplies zero for the missing side, producing a = 5, b = 8, c = 2. Use that rule when an absent contribution should count as zero. Label alignment is the same organizing idea behind these operations, but their value rules differ: join adds columns, combine_first fills gaps, and arithmetic computes with matched values.

Monthly files usually contain more rows of the same kind of observation. Concatenation collects those pieces into one table. It aligns labels on the other axis; it does not search for matching customer or order key values.

Stack rows and align columns

pd.concat([jan, feb]) stacks rows because the default is axis=0. Suppose January has three trips with columns trip and km, while February has two trips with trip, km, and note. The result has five rows and three columns. January's missing note cells become NaN.

join="outer" is the default: it keeps the union of column names. join="inner" keeps only shared names, so the result has five rows and the two columns trip and km. It still includes all five observations; the join setting controls the other axis.

Column order in the parts does not control the match. Exact names do. If one piece uses km and the other uses Km, the result contains two partly empty columns. Rename the headings before concatenating.

Decide what the row labels should mean

The original indexes survive by default. With starting indexes 0, 1, 2 and 0, 1, the result repeats labels 0 and 1. result.loc[0] selects two rows. Use ignore_index=True when those numbers have no meaning and you want fresh labels 0 through 4.

When source identity matters, use an outer index level:

result = pd.concat(
    [jan, feb], keys=["jan", "feb"], names=["month", "row"]
)

Now result.loc["feb"] selects February's two rows. keys preserves origin within a MultiIndex. In pandas 3, combining keys with ignore_index=True raises ValueError; choose source labels or a new plain index for that operation.

Place columns beside each other

pd.concat([x, y], axis=1) aligns rows by index label. For x with a = 1, b = 2 and y with b = 3, c = 4, the result has rows a, b, c. The x column is missing at c, and y is missing at a. With join="inner", only b remains.

Check storage and collect pieces efficiently

A column mixing numeric values and text may become dtype object. If ordinary NumPy int64 data gains a gap, it becomes float64; pandas nullable "Int64" can keep integers with <NA>. Align dtypes before combining when the column has a defined type.

DataFrame.append was removed in pandas 2.0. For many files, collect DataFrames in a list and call pd.concat once after the loop. Rebuilding a growing result in every iteration repeatedly copies accumulated data and becomes slow.

Check that a row concat's length equals the sum of input lengths. Then inspect column names, missing counts, and dtypes.

A MultiIndex gives each row several labels. A sales row might be identified by both city and year, written as a tuple such as ("Osaka", 2024). Each label has a level, and the order of those levels matters for selection.

Build and name the index

df.set_index(["city", "year"]) moves those columns into the index. pd.MultiIndex.from_product([["Lima", "Osaka"], [2024, 2025]], names=["city", "year"]) builds every combination of the supplied labels. Concatenation with keys also adds a level.

For the examples, sales has these index pairs and values:

After setting the two index levels, df.index.names is ["city", "year"]. rename_axis(["town", "yr"]) changes the level names without changing their values.

Select an outer label or a complete tuple

df.loc["Osaka"] selects both Osaka rows. The selected city level drops away, leaving 2024 and 2025 as the index. df.loc[("Lima", 2025), "sales"] selects one cell and returns 95.

A single label passed to loc searches the outermost level. df.loc[2024] therefore looks for a city named 2024 and raises KeyError in this table. To select a year across cities, use df.xs(2024, level="year"). It returns Osaka's 120 and Lima's 80, with city as the remaining index.

For a more general selection, df.loc[pd.IndexSlice[:, 2025], :] keeps all cities at year 2025. The colon in the first position means all labels in that level; the final colon selects all columns.

Sort before slicing ranges

Exact-label selection does not need a globally sorted index, but slicing a label range needs appropriate sorting. Use df.sort_index() before df.loc["Lima":"Osaka"]. An unsorted MultiIndex can raise UnsortedIndexError for such a slice.

sort_index() sorts all levels in their current order, so cities come first and years within each city. sort_index(level="year") sorts by the named level. swaplevel("city", "year") puts year in the outer position; follow it with sort_index() to group and order the new arrangement. Swapping the level positions alone does not sort the rows.

Move labels back to columns

reset_index() restores both levels as columns. reset_index(level="year") restores only year and leaves city as the index. These operations let you prepare a table for merging or saving without losing its identifying values.

Columns can also have several levels. A reshape with sales and staff values may produce ("sales", 2024) and ("staff", 2025) column labels. df["sales"] selects all columns under the top-level sales label.

For a total by city, df.groupby(level="city")["sales"].sum() gives Lima 175 and Osaka 255. Use grouping for this summary; the old sum(level="city") argument was removed in pandas 2.0.

stack and unstack change where labels sit. Values keep their identifying combination, but the table can expose combinations that had no observation.

Stack column labels into the index

Suppose a wide DataFrame has cities Osaka and Lima as its index and years 2024 and 2025 as its columns. Osaka has values 120 and 135; Lima has 80 and 95. Name the axes city and year to keep their meaning visible.

long = wide.stack() moves the year labels into a new innermost index level. With these single-level columns, the result is a Series containing four values:

• ("Osaka", 2024) gives 120.
• ("Osaka", 2025) gives 135.
• ("Lima", 2024) gives 80.
• ("Lima", 2025) gives 95.

The city labels repeat because each city now has one row per year. Stacking has not summed the values or created additional measurements.

Unstack an index level into columns

long.unstack() moves the innermost index level, year, into columns and recovers the wide arrangement. long.unstack("city") moves city instead, producing years as rows and cities as columns. A numeric level position such as unstack(0) can select the same level, but a name makes the intention easier to read.

If the input has hierarchical columns, stacking one level may leave another level as columns. For columns such as ("sales", 2024) and ("staff", 2024), stack("year") returns a DataFrame with sales and staff columns. Stacking all column levels returns a Series.

Keep missing combinations visible

Now suppose the long Series has no Lima 2025 entry. Unstacking makes a rectangular city-by-year table, so it introduces NaN at that position. Ordinary integer values become floats when this missing cell requires a float missing marker.

unstack(fill_value=0) can fill a newly absent combination with zero. Use it only if absence means zero, such as no sales recorded in a complete sales-event dataset. A missing sensor observation is unknown and should stay missing.

pandas 3 stack() keeps missing cells. Stacking the incomplete 2-by-2 table therefore returns four entries, including NaN. Add .dropna() if you want only the three known values. Removing gaps changes which combinations remain represented, so check the result's length.

Resolve duplicate combinations first

Unstacking needs a single value for each complete index combination. Two entries labeled ("Lima", 2025) compete for the same output cell and raise ValueError: Index contains duplicate entries, cannot reshape.

Use long.index.duplicated() to inspect repeated complete labels, or check the corresponding key columns before setting the index. Decide whether the rows are accidental copies or separate observations that should be combined. Removing or aggregating them is a data decision; unstack does not choose a rule for you.

In long format, identifying columns accompany one value column. In wide format, measurements are spread across columns, such as one column per year. Choose the layout that makes the next task easy: long tables suit filtering and grouping, while wide tables support side-by-side comparisons.

Pivot unique pairs into cells

A long table with columns city, year, and sales becomes wide with df.pivot(index="city", columns="year", values="sales"). City labels form rows, years form columns, and sales fill the cells. It is equivalent to df.set_index(["city", "year"])["sales"].unstack().

Osaka's 2024 and 2025 sales of 120 and 135 become two cells in one row. Lima's 80 and 95 form the second row. Swapping the index and columns arguments transposes this arrangement. Omitting values pivots all remaining data columns and can create hierarchical columns, such as a sales level and a staff level above the years.

Decide whether repeats are errors or observations

pivot rearranges values and cannot combine them. Two Lima 2025 rows would require two values in one output cell, so they raise ValueError. Count repeated pairs with df.duplicated(subset=["city", "year"]).sum().

If separate observations should be combined, use pivot_table with a chosen aggregation. For values 95 and 105 at Lima 2025, its default mean gives 100.0. With aggfunc="sum", the cell is 200. If each pair should have appeared once, keep pivot so it reveals a data error instead of silently summarizing it.

Melt measurement columns into rows

Suppose a CSV gives columns city, 2023, 2024, and 2025. Osaka's values are 110, 120, 135; Lima's are 70, 80, 95. Melt the year columns:

long = wide.melt(
    id_vars="city", var_name="year", value_name="sales"
)

id_vars stays in place and repeats. Each measurement-column label becomes a year value; each cell becomes a sales value. Two rows and three measurement columns produce six long rows, including one such as Osaka, "2023", 110. Missing measurement cells also become rows unless you drop them afterward.

Because these column labels came from CSV text, the resulting years are text. Use long["year"] = long["year"].astype(int) before doing arithmetic with them. Integer input column labels would already give integer year values.

Control what gets melted and restore the shape

value_vars=["2024", "2025"] limits melting to those columns. Forgetting id_vars can melt city names into the same value column as measurements. Always distinguish identifiers from the columns that contain values.

Without explicit names, the value column is called value. The variable column inherits wide.columns.name, or is called variable if that name is empty. Explicit var_name and value_name make the result easier to use.

Pivot the six rows back with index="city", columns="year", values="sales" to restore the three year columns. After pivoting, city is the index and the column axis is named year. reset_index() restores city as a column, and rename_axis(columns=None) removes the column-axis name. Verify both the shape and the labels before merging or saving the table.