Pandas is the Python data manipulation library every data professional uses daily. DataFrames enable SQL-like operations on in-memory data with vectorized performance. Mastering groupby, merges, and pivot tables transforms raw data into insights.
DataFrames: The Tabular Foundation
DataFrame Structure: Pandas DataFrames are 2D tables with labeled rows (index) and columns. Unlike NumPy arrays (homogeneous, typed), DataFrames hold mixed types—strings, integers, floats, objects in same structure. Column operations vectorized: df[“salary”] * 1.1 multiplies all values at C-speed, no loops. Indexing enables direct row access: df.loc[“row_label”] or df.iloc[3].
df = pd.DataFrame({“name”: [“Alice”, “Bob”], “salary”: [50000, 60000]})
df[“bonus”] = df[“salary”] * 0.1
print(df.loc[0])
print(df[“salary”].sum())
Series: 1D Data: Series is 1D DataFrame equivalent. Single column or row. Operations preserve type. Useful for standalone analysis. Broadcasting: Series index matches DataFrame index, automatically aligns during operations.
print(s[“a”])
df[“metric”] = s
GroupBy: Aggregation Powerhouse: groupby(column) creates groups. aggregate() (agg) applies functions. Named aggregations: .agg(total=(“salary”, “sum”), avg=(“salary”, “mean”)) creates readable column names. Multiple columns: groupby([“dept”, “status”]) creates hierarchical groups. Efficient: single pass through data.
df.groupby([“dept”, “status”])[“salary”].mean()
df.groupby(“dept”).apply(lambda x: x[“salary”].nlargest(2))
Merge & Join: SQL-Like Operations: merge() joins two DataFrames on columns. Inner (both keys), left (all left rows), right (all right rows), outer (all rows). on parameter specifies join keys. left_on/right_on for different column names. Performance: merge on indexed columns is faster than on non-indexed.
df2 = pd.DataFrame({“id”: [1, 2], “salary”: [50000, 60000]})
merged = df1.merge(df2, on=”id”, how=”left”)
df1.join(df2.set_index(“id”), on=”id”)
Pivot Tables: Reshaping Data: pivot_table() reshapes long-format to wide-format. index (rows), columns (new columns), values (aggregated). aggfunc (“sum”, “mean”, “count”) for multi-key aggregations. margins=True adds totals. Opposite: melt() unpivots wide-format to long.
pivot = df.pivot_table(index=”region”, columns=”date”, values=”sales”, aggfunc=”sum”)
melted = pivot.melt(var_name=”date”, value_name=”sales”)
Filtering & Selection: Boolean indexing: df[df[“salary”] > 50000]. Query() for readable filtering: df.query(“salary > 50000 and dept == ‘sales'”). isin() for multiple values: df[df[“status”].isin([“active”, “pending”])]. loc/iloc for label/position-based access.
df.query(“salary > 50000 and dept == ‘engineering'”)
df.loc[df[“status”].isin([“active”, “pending”]), [“name”, “salary”]]
5 Complete Solved Coding Problems
Question 1: GroupBy with Named Aggregations
Given employees with dept, salary, tenure. Find per-department: total_salary, avg_tenure, employee_count, max_salary. Use named agg.
df = pd.DataFrame({“dept”: [“eng”, “eng”, “sales”], “salary”: [50000, 60000, 40000], “tenure”: [3, 5, 2]})
result = df.groupby(“dept”).agg(total_salary=(“salary”, “sum”), avg_tenure=(“tenure”, “mean”), count=(“salary”, “count”), max_salary=(“salary”, “max”))
print(result)
Explanation: Named aggregation makes output readable. Without names: column names become (“salary”, “sum”), hard to remember. agg() accepts dict mapping column to (column, func). Single pass vectorized aggregation.
Google – Data Engineer
Question 2: Merge & Filter
Orders and customers tables. Inner join on customer_id. Filter joined result for orders where customer age > 25 and order_amount > 100.
orders = pd.DataFrame({“customer_id”: [1, 2, 3], “amount”: [150, 50, 200]})
merged = customers.merge(orders, on=”customer_id”, how=”inner”)
result = merged[(merged[“age”] > 25) & (merged[“amount”] > 100)]
print(result)
Explanation: merge() combines tables. Inner join returns only matching customer_ids. Boolean indexing filters. & operator combines conditions (not “and”)—precedence requires parentheses.
Meta – Analytics Engineer
Question 3: Pivot Table with Aggregation
Sales data: date, region, product, sales_amount. Create pivot showing product sales by region. Row=product, Column=region, Value=total sales.
pivot = df.pivot_table(index=”product”, columns=”region”, values=”sales”, aggfunc=”sum”, fill_value=0)
print(pivot)
Explanation: pivot_table reshapes long-format to wide-format. aggfunc=”sum” aggregates multi-row product-region combinations. fill_value=0 replaces NaN (missing region-product pairs). Outcome: matrix structure, easy cross-region comparison.
Amazon – Senior Data Engineer
Question 4: Multi-Column GroupBy with Lambda
Transactions: customer_id, date, amount. Group by customer_id, date. For each group, find max amount transaction with timestamp. Return customer, date, max_amount.
result = df.groupby([“customer_id”, “date”])[“amount”].max().reset_index()
print(result)
Explanation: groupby([]) creates hierarchical groups. [“amount”].max() returns max per group. reset_index() converts index back to columns. Alternative: apply(lambda x: x[“amount”].max()) for complex logic per group.
Apple – Data Scientist
Question 5: Handling Missing Data in Aggregations
DataFrame with NaN values. GroupBy department, calculate mean salary (skip NaN). Count non-null salaries per dept. Use dropna() or skipna parameter.
df = pd.DataFrame({“dept”: [“eng”, “eng”, “sales”], “salary”: [50000, np.nan, 40000]})
result = df.groupby(“dept”)[“salary”].agg(mean=(“mean”), count=(“count”))
print(result)
Explanation: skipna=True (default) ignores NaN in aggregation. mean([50000, nan]) = 50000 (one value). count() counts non-null values automatically. fillna() replaces NaN with default (0, forward-fill, etc.) before grouping if needed.
Microsoft – Senior Engineer
15 Practice Questions (Test Your Understanding)
Question 6: Create DataFrame from dict of lists. Access column, add computed column (c = a + b), filter rows where c > 10.
Question 7: Read CSV (1M rows, 50 columns). Select 5 columns, filter (age > 25 & status == “active”), count matching rows.
Question 8: Two DataFrames: users (id, name), orders (user_id, amount). Left join users to orders. GroupBy user_id, sum amounts.
Question 9: Pivot table: date rows, product columns, count values. Handle products missing on some dates with fill_value.
Question 10: GroupBy multiple columns. Apply custom function per group. Return top 2 rows (nlargest) per group.
Question 11: DataFrame with NaN. Fill forward (ffill), backward (bfill), constant (0). Compare performance.
Question 12: Read parquet file (10M rows). Filter (salary > 50k), groupby dept, agg (sum, mean, count). Write result CSV.
Question 13: Concat multiple DataFrames (axis=0 rows, axis=1 columns). Handle index misalignment.
Question 14: Melt wide-format to long-format. id_vars, value_vars, var_name, value_name parameters.
Question 15: Apply function row-wise (axis=1) vs column-wise (axis=0). When to use apply vs vectorization.
Question 16: Categorical column (100 categories, 10M rows). Use .cat accessor for efficiency. Compare memory vs string dtype.
Question 17: Query method for readable filtering. Query “age > 25 and status == @var” with variable reference.
Question 18: Duplicates: find with duplicated(), remove with drop_duplicates(). Subset parameter for specific columns.
Question 19: Sort by multiple columns. tiebreaker: sort_values([“col1”, “col2”], ascending=[True, False]).
Question 20: Copy vs view behavior. .copy() vs reference. Why df2 = df changes both when modifying df2.
Master Pandas
Get our comprehensive Data Analytics: Ace All The Interview Concepts course.