Pandas

A DataFrame is essentially a collection of Series that share the same index. Each column can be accessed as a Series, and each row shares the same index across all columns. This structure enables powerful operations like filtering, grouping, and merging.

Think of a Series as a single labeled column and a DataFrame as multiple columns joined together. The index (row labels) aligns data when combining DataFrames or performing operations between columns.

Series operations are element-wise by default, meaning each operation applies independently to every element. When multiplying a Series by 2, each value doubles. When multiplying two Series together, corresponding elements multiply (position 0 with position 0, etc.).

Statistical methods like sum(), mean(), std() summarize the entire Series into a single value. cumsum() is different—it computes a running total, returning a new Series where each element is the sum of all previous elements.

When you perform arithmetic operations between two Series with different indices, Pandas automatically aligns them by their index labels before computing. Here, s1 has indices 'a', 'b', 'c' and s2 has 'b', 'c', 'd'. Only 'b' and 'c' exist in both, so those get computed (2+10=12, 3+20=23). Index 'a' only exists in s1 and 'd' only in s2, resulting in NaN for those positions.

This behavior is incredibly powerful for real-world data where datasets often have missing values or different coverage. Unlike NumPy where position-based operations can silently produce wrong results, Pandas ensures data integrity by matching on labels.

If you want to treat missing values as a specific number instead of NaN, use the fill_value parameter. Here, fill_value=0 means: if an index exists in one Series but not the other, treat the missing value as 0. So for index 'a', it computes 1+0=1; for index 'd', it computes 0+30=30.

This is useful when you're combining data and want a sensible default. For example, if you're adding sales from two regions and one region has no sales for a product, you'd want to treat it as 0 rather than making the total NaN.

The .str accessor unlocks all Python string methods for entire Series at once. Instead of writing a loop to process each string, you call the method once and it applies to every element. str.upper() converts all characters to uppercase, while str.title() capitalizes the first letter of each word.

This vectorized approach is much faster than Python loops, especially for large datasets. Behind the scenes, Pandas optimizes these operations using C-level code rather than Python's interpreter.

str.split() splits each string by whitespace (or any delimiter you specify), returning a Series of lists. This is useful for extracting first/last names or parsing structured text. str.contains() returns a boolean Series indicating whether each element contains the specified substring.

The contains() method is particularly powerful for filtering data. You can use it with regular expressions by setting regex=True, enabling complex pattern matching across your entire dataset in a single operation.

str.len() returns the length of each string. This is useful for data validation (checking if entries meet minimum/maximum length requirements) or for analysis (finding outliers with unusually long or short text).

Other commonly used string methods include str.replace() for substitutions, str.strip() for removing whitespace, str.startswith()/str.endswith() for prefix/suffix checks, and str.extract() for regex group extraction.

df.dtypes shows the data type of each column. Pandas infers types when creating a DataFrame: integers become int64, strings become 'object' (a general-purpose type), and True/False become bool. The 'object' dtype for strings is flexible but can be memory-inefficient.

Knowing your data types is essential for two reasons: some operations only work on certain types (you can't calculate mean of strings), and choosing efficient types can dramatically reduce memory usage for large datasets.

astype() converts a column to a different type. Converting int64 to int32 halves memory usage when you don't need the extra precision. The 'string' dtype is Pandas' dedicated string type with better handling of missing values than 'object'.

pd.Categorical() is powerful for columns with limited unique values (like status, gender, department). It stores each unique value once and uses integer codes internally, saving huge amounts of memory. A column with 1 million rows but only 5 unique categories uses a tiny fraction of the memory.

The most common way to create a DataFrame is from a dictionary where keys become column names and values (as lists) become the column data. This is intuitive because you're defining your data column by column, which is how we typically think about tabular data.

All lists must have the same length—if one column has 3 values and another has 4, Pandas will raise a ValueError. The automatic integer index (0, 1, 2) is created for you, representing row numbers.

An alternative approach treats each dictionary as a row. This row-oriented format is natural when you receive data record by record, such as from API responses, database queries, or log entries. Each dictionary represents one entity with its attributes.

Pandas automatically determines columns from all unique keys across all dictionaries. The column order may vary, so specify columns=['name', 'score', 'grade'] if order matters.

A key advantage of this format is handling incomplete records. If a dictionary is missing a key, Pandas fills that cell with NaN (Not a Number, representing missing data) rather than crashing. This is common when working with real-world data where some fields may be optional.

This flexibility is especially useful when loading JSON data from APIs or databases where not every record has every field. You can later use fillna() to handle these missing values.

When you already have numerical data in a NumPy array, converting to DataFrame adds labels and Pandas functionality. Rows of the array become DataFrame rows, columns become DataFrame columns. Since arrays don't have column names, you must provide them via the columns parameter.

This conversion is zero-copy when possible—Pandas uses the same underlying memory as the NumPy array, making it very efficient. The DataFrame inherits the array's dtype (int64, float64, etc.).

You can specify custom row labels with the index parameter instead of using the default 0, 1, 2. This is useful when rows represent named entities like dates, product IDs, or locations that you want to access by name.

After creating, you can access data both ways: df.loc['row1'] gets by label, df.iloc[0] gets by position. This dual access pattern is a core strength of Pandas over raw NumPy arrays.

The simplest way to load a CSV file is with just the filename. Pandas automatically detects the delimiter (comma by default), infers column types, and treats the first row as headers. This works for most well-formatted CSV files.

pd.read_csv() is one of the most-used functions in data science. It handles file reading, parsing, type conversion, and DataFrame construction all in one call, making it incredibly convenient for quick data exploration.

For real-world data, you'll often need to customize how Pandas reads the file. index_col sets a column as the DataFrame index; parse_dates converts string columns to datetime objects; na_values tells Pandas which strings represent missing values; dtype forces specific column types (crucial for codes like '001' that shouldn't become integers).

Performance options like usecols (read only needed columns), nrows (read first N rows), and skiprows (skip initial rows) are essential for large files. When saving, to_csv() with index=False prevents adding an extra index column to your output file.

read_excel() works similarly to read_csv() but handles Excel's .xlsx and .xls formats. By default, it reads the first sheet. Use sheet_name to specify a particular sheet by name or by index (0 for first sheet). Setting sheet_name=None reads ALL sheets and returns a dictionary where keys are sheet names.

Excel files often contain multiple related datasets on different sheets. The dictionary approach lets you load everything at once and then work with specific sheets as df = all_sheets['SheetName']. This requires the openpyxl package (pip install openpyxl).

to_excel() writes a DataFrame to an Excel file. Use index=False to avoid writing row numbers as the first column. The sheet_name parameter controls which sheet the data goes to.

For writing multiple DataFrames to different sheets in one file, use ExcelWriter as a context manager. This opens the file once, writes all sheets, and then properly closes and saves the file. Without ExcelWriter, each to_excel() call would overwrite the previous one.

Pandas can directly query databases and return results as DataFrames. First, establish a connection using the appropriate library (sqlite3 for SQLite, psycopg2 for PostgreSQL, mysql-connector for MySQL). Then use read_sql() with any SQL query—the results become a DataFrame.

read_sql() is flexible: you can run complex queries with JOINs, WHERE clauses, aggregations, etc. read_sql_table() is a shortcut for loading an entire table without writing SQL. Both methods handle type conversion automatically.

to_sql() writes a DataFrame to a database table. The if_exists parameter controls behavior when the table already exists: 'replace' drops and recreates it, 'append' adds rows to existing table, 'fail' raises an error. Use index=False to avoid creating an extra index column.

Always close database connections when done. For production code, use SQLAlchemy's create_engine() which provides connection pooling and works with more database types. The with statement can auto-close connections.

read_json() parses JSON files or strings into DataFrames. It works with JSON arrays (each object becomes a row) or JSON objects (keys become rows or columns depending on structure). You can pass a filename or a JSON string directly.

JSON is the standard format for web APIs, so this function is essential when working with data from web services. The function automatically infers the structure and converts it to tabular format.

Real-world JSON is often nested—objects containing other objects. json_normalize() flattens these structures into a flat table. Nested keys become column names with dots: the 'name' inside 'info' becomes the column 'info.name'. This handles arbitrarily deep nesting.

When writing DataFrames to JSON, the orient parameter controls the output format. 'records' produces a list of row objects [{row1}, {row2}], which is the most common format for APIs. Other options include 'columns' (dict of column arrays) and 'index' (dict keyed by index).

When you first load a dataset, these properties give you an instant overview. shape returns a tuple (rows, columns)—here (3, 3) means 3 rows and 3 columns. columns returns an Index object listing all column names. dtypes shows the data type of each column.

These attributes are your first stop for understanding any DataFrame. shape tells you the size of your data, columns shows what fields exist, and dtypes helps identify potential issues like numeric data stored as strings.

head(n) displays the first n rows (default 5)—perfect for previewing data without overwhelming your screen. There's also tail(n) for the last n rows. For large datasets, these methods let you quickly verify the data looks correct.

info() provides a comprehensive summary: number of entries, column names, non-null counts per column, dtypes, and memory usage. This is often the first method to call on a new dataset—it reveals missing values, data types, and memory footprint all at once.

describe() generates summary statistics for all numeric columns in one call. You get count (non-null values), mean, standard deviation (std), minimum, maximum, and quartiles (25%, 50%, 75%). The 50% percentile is the median.

This is your go-to method for understanding the distribution of numeric data. Quickly spot outliers (huge max vs small mean), check for missing values (count less than expected), and understand the spread of your data (std relative to mean).

For individual statistics, call the method directly on a column. Common methods include mean(), median(), std(), min(), max(), sum(), and count(). These return single values rather than DataFrames.

You can also use multiple statistics: df['salary'].agg(['mean', 'median', 'std']) returns all three at once. For custom statistics, pass a function: df['age'].agg(lambda x: x.max() - x.min()) calculates the range.

value_counts() is the perfect tool for categorical data analysis. It counts how many times each unique value appears, sorted from most to least common by default. Here, 'A' appears 3 times, 'B' twice, and 'C' once.

Add normalize=True to get percentages instead of counts: df['category'].value_counts(normalize=True) shows A is 50%, B is 33%, C is 17%. This is essential for understanding the distribution of categories in your data.

unique() returns an array of all distinct values in the column—useful for seeing the vocabulary of a categorical column. nunique() returns just the count of unique values, which is helpful for understanding cardinality (how many distinct categories exist).

To check if a specific value exists, use 'value' in df['column'].values. Note the .values—this accesses the underlying NumPy array. Without it, you'd be checking if 'A' is in the column's index, which isn't what you want.

When working with large datasets, memory becomes critical. info(memory_usage='deep') provides accurate memory usage including the actual size of string objects. Without 'deep', it only shows the memory for pointers, not the actual string data.

This deep analysis takes longer but gives you the true picture. A DataFrame that looks small (few columns, reasonable row count) might use gigabytes if it contains long strings or complex objects.

memory_usage(deep=True) breaks down memory by column. Here, 'name' uses far more memory (670KB) than numeric columns (80KB each) because strings require more space than fixed-size integers or floats. The Index also consumes memory.

Understanding per-column memory helps optimize your DataFrame. You might convert string columns to categorical (if few unique values), use smaller integer types (int32 instead of int64), or drop columns you don't need. These optimizations can reduce memory by 50-90%.

Selecting columns is the most common operation in Pandas. Using single brackets df['name'] returns a Series (a single column), while double brackets df[['name', 'age']] return a DataFrame (a subset table). This distinction matters because Series and DataFrames have different methods available.

Dot notation (df.age) is a convenient shortcut but has limitations: it doesn't work with column names containing spaces, special characters, or names that conflict with DataFrame methods. Always use bracket notation for reliability.

Creating new columns is as simple as assignment. df['senior'] = df['age'] >= 30 creates a boolean column based on a condition. df['annual_bonus'] = df['salary'] * 0.1 creates a computed column from existing data. The operation is vectorized—it applies to all rows automatically.

You can create columns from any expression: arithmetic operations, string manipulations, or the results of function calls. This is how you typically feature engineer in data science projects.

loc is the primary way to select data by label. The syntax df.loc[row_selector, column_selector] lets you specify exactly which rows and columns you want. Use a colon (:) to mean "all" — df.loc[:, 'name'] means all rows, just the name column.

Critical difference from Python slicing: loc ranges are INCLUSIVE on both ends. df.loc[0:2] includes rows 0, 1, AND 2. This catches many beginners off guard since Python normally excludes the end of a slice.

loc truly shines with custom indices. After setting 'name' as the index, you can select rows by name directly: df_custom.loc['Alice'] retrieves Alice's row. Slicing works alphabetically when the index is strings: 'Alice':'Carol' includes all names in that alphabetical range.

This label-based access is why Pandas is so powerful for data analysis—you can reference data by meaningful identifiers (like dates, names, or IDs) rather than remembering integer positions.

iloc selects data by integer position, exactly like Python list indexing. df.iloc[0] is the first row, df.iloc[0:2] is the first two rows (positions 0 and 1). Unlike loc, iloc slicing is EXCLUSIVE at the end—standard Python behavior.

Use iloc when you need positional access regardless of what the index labels are. It's especially useful for iteration, random sampling, or when working with data where you don't know or care about the index values.

iloc supports all Python slicing features: negative indices count from the end (-1 is the last row), step values let you skip rows (::2 takes every other row), and you can select arbitrary combinations of row and column positions.

The syntax df.iloc[[0, 2], [1, 3]] selects a subset of non-contiguous rows and columns by their positions, returning the intersection—useful when you need specific cells scattered across the DataFrame.

Boolean filtering is the most powerful way to select data in Pandas. The expression df['age'] >= 30 creates a boolean Series (True/False for each row). When you put this inside brackets df[...], it returns only the rows where the condition is True.

This pattern is fundamental to data analysis: create a condition, use it to filter. The condition can be any expression that produces True/False for each row—comparisons, string matches, null checks, etc.

Combining conditions requires special operators: & for AND, | for OR, and ~ for NOT. Critically, you MUST wrap each individual condition in parentheses. Without them, Python's operator precedence causes errors. (df['age'] >= 25) & (df['city'] == 'NYC') is correct; df['age'] >= 25 & df['city'] == 'NYC' will fail.

These operators work element-wise across the boolean Series. The result is a new boolean Series that you can use for filtering. This is different from Python's and/or keywords, which don't work with Pandas Series.

The query() method provides a more readable alternative to boolean indexing. Instead of df[(df['age'] >= 25) & (df['city'] == 'NYC')], you write df.query('age >= 25 and city == "NYC"'). The string uses natural Python/SQL-like syntax with 'and' and 'or' keywords.

To use Python variables inside a query string, prefix them with @. This lets you build dynamic queries: @min_age refers to the variable min_age defined outside the string. This is cleaner than f-string interpolation and safer against injection.

Complex queries are much easier to read with query(). You can use parentheses for grouping, and the precedence follows natural logic. For column names with spaces or special characters, wrap them in backticks within the query string.

query() is also slightly faster for large DataFrames because it can use numexpr under the hood. For simple conditions, both approaches perform similarly, but query() wins on readability for complex filtering logic.

isin() checks whether each value is in a list of acceptable values. It's much cleaner than chaining multiple == comparisons with |. Here, df['city'].isin(['NYC', 'LA']) returns True for rows where city is either NYC or LA.

The ~ operator negates the boolean mask, giving you exclusion filtering. ~df['city'].isin(cities) returns rows where city is NOT in the list. This is how you filter out unwanted categories efficiently.

Combine isin() with other conditions using & (and) or | (or). This example filters for rows where city is NYC or LA AND age is 25 or 30. You can mix isin() with regular boolean conditions too.

isin() is optimized for membership testing and is much faster than equivalent OR chains, especially for long lists of values. Always use isin() when checking against more than 2-3 values.

The str accessor unlocks string methods for filtering. str.contains() checks if a substring exists anywhere in the string. With case=False, it's case-insensitive—matching 'a', 'A', or any combination. By default, it's case-sensitive.

str.startswith() and str.endswith() check the beginning and end of strings respectively. These are faster than contains() when you know exactly where to look, and they're perfect for filtering by prefixes (like product codes) or suffixes (like file extensions).

str.match() applies regular expressions for powerful pattern matching. The pattern ^[A-C] matches strings starting with A, B, or C. You can use any regex pattern—this is incredibly flexible for complex text filtering.

str.len() returns the length of each string, which you can then use in comparisons. This filters for names longer than 4 characters. Combine these methods: df[df['name'].str.len().between(3, 10)] filters for names between 3 and 10 characters.

sort_values() orders rows based on column values. By default it sorts ascending (smallest to largest, A to Z). Set ascending=False for descending order. When sorting by multiple columns, it sorts by the first column first, then uses the second column to break ties.

Sorting is essential for data presentation and often required before certain operations like rolling calculations or when you need to see extremes (top earners, oldest items, etc.).

For multi-column sorts, pass a list to ascending to control each column's direction independently. This is useful when you want, say, alphabetical city names but highest salaries first within each city.

sort_index() sorts by the row index labels. rank() assigns rank positions rather than reordering—useful when you want to keep original order but add a ranking column. ascending=False gives rank 1 to the highest value.

Use loc with a boolean condition to update specific rows. The syntax df.loc[condition, column] = value sets the value for rows where the condition is True. This is the safe, recommended way to modify DataFrame values conditionally.

You can update multiple columns at once by passing a list of column names and a list of values. This is useful when several columns need to change together based on the same condition.

where() and mask() provide vectorized conditional logic. where(condition, other) keeps original values where condition is True, replaces with 'other' where False. mask() is the opposite—it replaces where condition is True. Both create new columns without modifying the original.

replace() substitutes values using a dictionary mapping old values to new values. It's perfect for standardizing data (e.g., converting abbreviations to full names) or fixing data entry inconsistencies.

The groupby() method splits your data into groups based on unique values in a column. Here, rows are grouped by 'category' (A or B), then mean() calculates the average 'value' for each group. Category A has values 10, 30, 50 (mean=30), and B has 20, 40 (mean=30).

The pattern is: df.groupby('column_to_group_by')['column_to_aggregate'].aggregation_function(). This is the most common groupby pattern and handles 90% of aggregation tasks you'll encounter.

groupby() itself doesn't compute anything—it creates a lazy GroupBy object that waits for you to specify what to do with each group. This is efficient because you can chain multiple operations without recalculating groups.

You can iterate over groups using a for loop. Each iteration yields the group name (the value being grouped on) and a DataFrame containing just that group's rows. This is useful for custom processing or debugging.

The agg() method lets you apply multiple aggregation functions simultaneously. Pass a list of function names as strings, and you get a DataFrame with each function as a column. This is more efficient than calling each aggregation separately.

This is perfect for creating summary tables. In one operation, you can get the sum, mean, count, min, max—whatever statistics you need—for each group.

When you need different aggregations for different columns, pass a dictionary where keys are column names and values are lists of functions. This creates a multi-level column index which can be awkward to work with.

Named aggregations solve this elegantly: each argument specifies a result column name, and the value is a tuple of (column_to_aggregate, function). This produces clean, flat column names that are ready for reports or further analysis.

Pass a list of column names to groupby() to group by multiple columns. This creates groups for every unique combination of values. Here we get four groups: East-A, East-B, West-A, and West-B, with sales summed for each combination.

The result has a hierarchical (multi-level) index. The first level is region, and nested within each region are the products. This structure represents the grouping hierarchy clearly.

reset_index() converts the hierarchical index back into regular columns, producing a flat DataFrame that's often easier to work with. Each row now explicitly shows the region, product, and aggregated sales.

This flat format is typically what you want for further analysis, visualization, or exporting to CSV/Excel. The hierarchical format is useful for display but can be cumbersome for programmatic access.

transform() applies an aggregation but broadcasts the result back to match the original DataFrame's shape. Here, the mean of category A (15) is placed in every row belonging to category A. Unlike regular aggregation which reduces data, transform keeps all original rows.

This is incredibly useful for adding group-level statistics as new columns. Common uses include calculating each row's deviation from group mean, percentile within group, or normalized values.

transform() accepts lambda functions for custom calculations. The normalization formula (x - mean) / std standardizes values within each group, centering them around 0 with standard deviation of 1. This is common preprocessing for machine learning.

apply() is the most flexible groupby method—it can return any shape. Your function receives each group as a DataFrame and can do anything: filter rows, add columns, reshape data. However, it's slower than built-in aggregations and should be used when other methods can't solve your problem.

filter() keeps or discards entire groups based on a condition. The lambda function receives each group as a DataFrame and must return True (keep all rows in this group) or False (discard all rows). Here, only category B has sum > 50, so all of B's rows are kept while A and C are removed.

Unlike boolean filtering on individual rows, filter() makes group-level decisions. It's perfect for questions like "keep only customers with total purchases over $1000" or "show only regions with at least 10 orders."

A common use case is filtering by group size. len(x) gives the number of rows in each group. Groups A and B have 2 rows each, while C has only 1. With len(x) >= 2, we keep A and B but drop C entirely.

This is useful for excluding rare categories from analysis, removing low-frequency items that might cause statistical issues, or focusing on groups with enough data to be meaningful.

We create a sample DataFrame with intentionally missing values to demonstrate detection techniques. None is Python's null value, while np.nan (Not a Number) is NumPy's representation of missing data. Pandas treats both as missing values internally.

In real datasets, missing values appear for many reasons: users skipping optional fields, data corruption during transfer, sensors failing to record readings, or records from different sources having different fields. Understanding your data's missingness is the first step in cleaning it.

isna() returns a DataFrame of the same shape where each cell is True if the original value was missing, False otherwise. This boolean mask lets you see exactly where the gaps in your data are located. isnull() is simply an alias—they do exactly the same thing.

The output shows True at positions where data is missing: row 2's name, row 1's age, and row 3's city. This visual representation helps you spot patterns—are missing values concentrated in certain rows or columns?

Chaining .sum() after isna() counts the True values (missing) in each column. Since True equals 1 in Python, summing a boolean column gives the count of True values. Here, each column has exactly 1 missing value.

This is your first quantitative view of data quality. A column with 90% missing values might need to be dropped entirely, while a column with 1% missing can often be filled. This count guides your cleaning strategy.

Double .sum() first sums each column (giving missing count per column), then sums those sums (giving total missing across the entire DataFrame). Here, 1+1+1 = 3 total missing values in the dataset.

This single number is useful for tracking data quality over time or comparing datasets. "Dataset A has 500 missing values, Dataset B has 50"—immediately tells you which needs more cleaning work.

Using .mean() instead of .sum() gives the proportion of missing values (since True=1, False=0, mean gives the fraction that are True). Multiply by 100 to express as a percentage. This is often more meaningful than raw counts.

If a column has 25% missing data, that's concerning regardless of whether your dataset has 100 rows or 1 million rows. Percentages let you set universal thresholds like "drop columns with >50% missing" that work across different dataset sizes.

df.isna().any(axis=1) checks each row and returns True if ANY value in that row is missing. axis=1 means "check across columns for each row." This boolean Series can then filter the DataFrame to show only problematic rows.

This view helps you investigate missing data patterns. Are certain records systematically incomplete? Maybe data from a specific source or time period has quality issues. Seeing the actual rows with missing data often reveals the underlying cause.

dropna() removes rows containing missing values. By default, it drops rows with ANY missing value. With how='all', it only drops rows where every value is missing—useful for removing completely empty rows while preserving partial data.

The subset parameter lets you focus on critical columns. If 'name' and 'age' are essential but 'city' is optional, use subset=['name', 'age'] to keep rows where those two columns have data, regardless of other columns.

axis=1 switches from dropping rows to dropping columns. This removes any column that has missing values—use carefully as it can eliminate useful columns with just one missing value.

thresh=n keeps rows with at least n non-null values. This is a more nuanced approach: "I can tolerate some missing data, but the row must have enough information to be useful." It's often better than the all-or-nothing approach of how='any'.

fillna() replaces missing values with a specified value. A single value fills all missing cells. For numeric columns, mean imputation (filling with the column's average) is a common statistical technique that preserves the overall distribution.

Different columns often need different fill strategies. Pass a dictionary to fillna() where keys are column names and values are the fill values. Text columns might get 'Unknown' while numeric columns get 0 or the mean.

Forward fill (ffill) copies the last valid value forward into missing cells—useful for time series where the previous state continues until changed. Backward fill (bfill) does the opposite, copying the next valid value backward.

interpolate() estimates missing numeric values based on surrounding values using linear interpolation by default. For time series or ordered data, this often produces more realistic estimates than simple mean imputation.

duplicated() marks rows as True if they're duplicates of an earlier row. By default, it compares all columns. The first occurrence is marked False (not a duplicate), while subsequent identical rows are True. Summing gives the count of duplicate rows.

Use subset to check duplicates in specific columns only. df.duplicated(subset=['id']) finds rows where 'id' repeats, even if other columns differ. This is crucial when 'id' should be unique—any duplicates indicate data quality issues.

drop_duplicates() removes duplicate rows. keep='first' (default) retains the first occurrence, keep='last' retains the last. keep=False removes ALL copies of duplicated rows, leaving only rows that were never duplicated.

Use subset to de-duplicate based on certain columns. If you have multiple records per user but want one per user, use subset=['user_id'] to keep one row per unique user ID. Combine with keep to choose which record to retain.

Data imported from CSV files often comes in as strings even when it should be numeric. astype() converts a column to a different data type. Converting '1', '2', '3' from strings to integers enables mathematical operations and proper sorting.

Check your data types early with df.dtypes. Incorrect types cause subtle bugs: '10' + '5' = '105' (string concatenation) vs 10 + 5 = 15 (addition). Many Pandas functions behave differently based on type.

pd.to_datetime() parses date strings into datetime objects, enabling date arithmetic, filtering by date ranges, and extracting components like year and month. pd.to_numeric() handles numeric conversion with better error handling.

errors='coerce' converts invalid values to NaN instead of crashing. This is safer for messy real-world data. Category dtype saves memory when a column has few unique values repeated many times (like 'status' with only 'active', 'pending', 'closed').

The .str accessor provides vectorized string methods that work on entire columns. str.strip() removes leading and trailing whitespace—essential because ' Alice ' and 'Alice' won't match in comparisons or grouping.

str.title() capitalizes the first letter of each word, str.lower() converts everything to lowercase, and str.upper() to uppercase. Standardizing case ensures 'BOB', 'Bob', and 'bob' are recognized as the same person.

str.replace() with regex=True enables pattern matching. The pattern \s+ matches one or more whitespace characters, collapsing "John Doe" to "John Doe". Regular expressions give you powerful text cleaning capabilities.

str.split('@').str[1] splits emails at @ and takes the second part (domain). The [^0-9] regex matches anything that's NOT a digit, so replacing with '' removes all non-numeric characters—perfect for standardizing phone numbers like '(555) 123-4567' to '5551234567'.

rename() with a dictionary maps old column names to new ones. Only specified columns are renamed; others stay unchanged. This is the safest method because it's explicit and fails if the old name doesn't exist.

df.columns returns an Index of column names that supports string methods. str.lower() converts all column names to lowercase, str.replace(' ', '_') replaces spaces with underscores. These operations affect all columns at once.

Pass a function to rename() to transform all column names. The lambda receives each column name and returns the new name. This one-liner converts "User Name" to "user_name"—a common convention for Python-friendly column names.

You can also set df.columns directly to a list of new names. The list must have exactly as many elements as there are columns. This is useful when you need completely different names, but be careful—the order must match the existing column order.

We start with two DataFrames that share a common column ('id'). df1 contains employee names with IDs 1-4, while df2 contains salaries for IDs 2-5. Some IDs exist in both (2, 3, 4), some only in df1 (1), and some only in df2 (5).

This scenario is extremely common: you have related data spread across multiple tables or files. Merging combines them into a single DataFrame where you can analyze the relationships between the data.

pd.merge() combines DataFrames based on a common column. The 'on' parameter specifies which column to match. By default, merge performs an inner join—only rows where the key (id) exists in BOTH DataFrames appear in the result.

Alice (id=1) isn't in the result because id=1 doesn't exist in df2. The mysterious id=5 salary isn't included either because id=5 doesn't exist in df1. Inner join is strict: it requires data from both sides.

Left join (how='left') keeps ALL rows from the left DataFrame (df1) regardless of whether they have a match. Alice (id=1) now appears with NaN for salary because there's no matching record in df2. All employees are preserved.

This is the most common join type in practice. You typically have a "primary" table you want to keep complete, while adding data from a "secondary" table where available. Unmatched rows get NaN values that you can later fill or filter.

Right join is the mirror image of left join—it keeps all rows from the right DataFrame. Outer join (how='outer') keeps ALL rows from BOTH DataFrames, filling NaN where there's no match on either side.

Outer join is useful for finding discrepancies: which employees have no salary record? Which salary records have no employee? The NaN values reveal the mismatches between your data sources.

Real-world data rarely has perfectly matching column names. Here, df1 uses 'employee_id' while df2 uses 'emp_id' for the same concept. This happens when data comes from different systems, databases, or when conventions vary across teams.

Pandas handles this gracefully—you don't need to rename columns before merging. Just tell merge which columns correspond to each other.

Use left_on and right_on to specify different column names: left_on is the column from the first (left) DataFrame, right_on from the second (right). Pandas matches rows where employee_id equals emp_id.

After merging, both columns appear in the result (they're technically different columns even though they contain the same values). Use drop() to remove the redundant one for cleaner output.

Sometimes a single column isn't enough to uniquely identify a row. Here, neither 'year' nor 'quarter' alone uniquely identifies a record—we need BOTH: 2023-Q1 is different from 2023-Q2 and from 2024-Q1. This is called a composite key.

Financial data, time series, and hierarchical data commonly require composite keys. Sales by region and month, scores by student and subject, inventory by warehouse and product—all need multiple columns to match correctly.

Pass a list of column names to the 'on' parameter. Pandas will only match rows where ALL specified columns match. 2023-Q1 revenue joins with 2023-Q1 expenses—not with 2023-Q2 or 2024-Q1.

Now you can analyze revenue vs expenses for each period. The resulting DataFrame has one row per unique year-quarter combination with data from both sources side by side.

concat() stacks DataFrames vertically (by default)—it places one below the other. This is different from merge, which combines columns side-by-side based on matching keys. Use concat when you have data split across multiple files with identical structure.

Notice the index values (0, 1, 0, 1)—each DataFrame kept its original index. This can cause issues if you try to access by index, as there are now duplicate index values.

ignore_index=True creates a fresh sequential index (0, 1, 2, 3)—usually what you want when combining data from the same source split across files (like monthly reports). The original indices are discarded.

axis=1 concatenates horizontally, placing DataFrames side by side. This is useful when you have different columns for the same rows. Note: this creates duplicate column names (A, B, A, B)—you'll likely want to rename them afterward.

Sometimes your key isn't a column—it's the index itself. Here, df1 and df2 use string indices ('a', 'b', 'c' and 'a', 'b', 'd') rather than a column to identify rows. This is common with time series (date index) or when you've already set a meaningful index.

Both DataFrames share indices 'a' and 'b', while 'c' is only in df1 and 'd' is only in df2. The join operation will need to handle these mismatches.

The join() method is designed specifically for index-based joins and offers cleaner syntax. By default it does a left join; here we use how='outer' to keep all indices from both DataFrames, with NaN where data is missing.

You can also use merge() with left_index=True and right_index=True to join on indices. This is more verbose but offers more flexibility, like combining index-based and column-based joins (e.g., left_index=True, right_on='column').

Pivot tables transform "long" data into "wide" format while aggregating. Here, each row in the original data represents one region's sales for one date. The pivot table reorganizes this so dates become rows, regions become columns, and sales values fill the cells.

The key parameters: 'values' is what you're aggregating (sales), 'index' becomes row labels (date), 'columns' becomes column headers (region), and 'aggfunc' is the aggregation function (sum, mean, count, etc.).

Pass a list of functions to aggfunc to get multiple summary statistics in one pivot table. This creates hierarchical columns where the first level is the function and the second level is the region.

margins=True adds "All" row and column showing totals. This is like the Grand Total in Excel pivot tables—essential for reports where you need to see both individual values and overall summaries.

This "wide" format is human-readable—each student has one row with all their scores as separate columns. However, many visualization libraries and statistical functions expect "long" format where each observation (student-subject-score) is its own row.

Wide format is common in spreadsheets: columns represent different measurements of the same entity. But for analysis, long format is often more flexible—you can easily filter by subject, group by subject, or plot all subjects at once.

melt() "unpivots" wide data into long format. id_vars specifies columns to keep as-is (name stays as a column). value_vars lists columns to melt (math, science, english become values in the new 'subject' column). Their values go into the 'score' column.

Now you have 6 rows instead of 2—one for each student-subject combination. This format lets you easily create box plots by subject, calculate average by subject, or apply the same analysis function to all scores regardless of subject.

apply() runs a function on each element of a Series or each row/column of a DataFrame. The lambda function converts scores to letter grades using conditional logic. apply() is incredibly flexible—any Python function can be used.

With axis=1, apply() passes each row as a Series to your function. This lets you use multiple columns to compute the result. The function receives the entire row and can access any column by name (row['score'], row['name']).

map() on a Series replaces values according to a dictionary or function. It's perfect for categorical encoding or lookup tables—faster than apply() for simple substitutions. If a value isn't in the dictionary, it becomes NaN.

DataFrame.map() (formerly applymap()) applies a function to every element in a DataFrame. Here it squares all values. This is useful for element-wise transformations that should apply uniformly across all cells, like rounding or formatting.

Rolling windows calculate statistics over a fixed-size sliding window. With window=3, each value is the mean (or sum) of that row and the two previous rows. The first two rows have NaN because there aren't enough prior values to fill the window.

Moving averages smooth out noise in time series data, revealing underlying trends. A 3-day rolling mean shows the average of the last 3 days, which is less volatile than daily values. Longer windows (7, 30 days) create smoother trends.

Expanding windows include ALL previous values, growing as you move down. Cumulative mean at row 5 is the mean of rows 0-5. Cumulative max shows the highest value seen so far—useful for tracking "best performance to date" or setting benchmarks.

Exponential weighted moving average (ewm) gives more weight to recent values, making it more responsive to recent changes than simple rolling mean. The 'span' parameter controls how quickly the influence of older values decays.

shift() moves values up or down by a specified number of rows. shift(1) moves values down by one row, so 'previous_price' contains yesterday's price for each day. The first row becomes NaN because there's no previous value.

shift(-1) moves values up, giving you the "next" value. This is useful for calculating "look-ahead" metrics like "next day's price" or for creating lag features in time series machine learning models.

diff() calculates the difference between the current and previous value. It's shorthand for df['price'] - df['price'].shift(1). This shows absolute change—how much the price increased or decreased from yesterday.

pct_change() calculates the percentage change from the previous value: (current - previous) / previous. Multiply by 100 to express as a percentage. This is essential for financial analysis where you care about relative returns, not absolute price movements.

crosstab() creates a contingency table showing the frequency of combinations between two categorical variables. Here it counts how many employees of each gender work in each department. Each cell shows the count for that gender-department combination.

This is invaluable for exploring relationships between categorical variables. Are certain departments male-dominated? Do purchase patterns vary by region and product type? Cross tabulation answers these questions at a glance.

Add 'values' and 'aggfunc' parameters to aggregate a numeric column instead of counting. This shows mean salary by gender and department—perfect for identifying pay disparities across demographic and organizational dimensions.

You can use any aggregation function: 'sum' for total sales by region and product, 'max' for best score by student and subject, or 'mean' for average ratings by movie genre and decade. It's a quick way to summarize data by two dimensions.

The dt accessor provides access to datetime components, similar to how str works for strings. You can extract year, month, day, weekday name, quarter, week number, and more. These extracted features are invaluable for time-based analysis and grouping.

Each extraction returns a Series you can store as a new column. This is common preprocessing for time series: you might analyze sales patterns by day of week, or group transactions by month. The original datetime column stays intact while you gain analytical dimensions.

Date arithmetic works naturally—subtracting dates gives a Timedelta, and .dt.days extracts the number of days. Filtering by date range uses standard boolean indexing; Pandas automatically parses date strings like '2024-01-01' for comparison.

resample() is the time series equivalent of groupby(). Set the date as index, then resample at a frequency: 'M' for monthly, 'W' for weekly, 'D' for daily, 'Q' for quarterly. This groups data into time buckets and lets you aggregate (sum, mean, count) within each period.