Pandas for Data Manipulation: SQL-to-Pandas Cheat Sheet
After The Python Analytics Stack Explained, the next practical question is how to manipulate data in pandas. In interviews, the favourite prompt is: "Show me how you'd do a GROUP BY in pandas" - because it tests SQL↔pandas translation ability. This guide keeps the focus interview-ready: operations, pandas methods, SQL equivalents, and the six-step data cleaning flow.
- Interview Favourite: "Show me how you'd do a GROUP BY in pandas" - tests SQL↔pandas translation ability.
- Read data with pd.read_csv(), pd.read_excel(), pd.read_sql(); the SQL equivalent is FROM table_name.
- Filter rows with df[condition], df.query(), df.loc[]; the SQL equivalent is WHERE.
- Aggregate with df.groupby().agg(), df.groupby().sum(); the SQL equivalent is GROUP BY + SUM/COUNT.
- Join tables with pd.merge(), pd.concat(); the SQL equivalent is JOIN.
- Use Window / Rolling methods df.rolling(), df.expanding(), df.shift() for OVER() window functions.
- Data Cleaning Checklist - 6-Step Flow: Missing Values, Duplicates, Data Types, Outliers, Inconsistencies, Feature Engineering.
Big Picture: SQL-to-Pandas Translation
Pandas data manipulation becomes easier when each operation is mapped to its SQL equivalent. The core interview skill is not memorising isolated syntax, but knowing which pandas method corresponds to Read data, Filter rows, Select columns, Aggregate, Join tables, Sort, Deduplicate, Window / Rolling, Apply function, and Pivot.
Operation → Pandas Method → SQL Equivalent → Example Code.
Core Operations You Should Be Ready to Translate
For Read data, the pandas methods are pd.read_csv(), pd.read_excel(), pd.read_sql(), with FROM table_name as the SQL equivalent. The example code is df = pd.read_csv('orders.csv').
For Filter rows, the pandas methods are df[condition], df.query(), df.loc[], with WHERE as the SQL equivalent. The example code is df[df['city']=='Mumbai'].
For Select columns, the pandas methods are df[['col1','col2']], df.iloc[:,0:3], with SELECT col1, col2 as the SQL equivalent. The example code is df[['order_id','amount']].
Aggregate and GROUP BY in Pandas
The interview favourite is GROUP BY in pandas. For Aggregate, the pandas methods are df.groupby().agg(), df.groupby().sum(), with GROUP BY + SUM/COUNT as the SQL equivalent.
df.groupby('city')['revenue'].sum()
This is the key translation candidates are expected to know: a city-level grouping followed by revenue summation. It directly maps the SQL idea of GROUP BY + SUM/COUNT into pandas syntax.
Joins, Sorting, Deduplication, and Windows
For Join tables, the pandas methods are pd.merge(), pd.concat(), with JOIN as the SQL equivalent. The example code is pd.merge(orders, customers, on='cust_id').
For Sort, the pandas method is df.sort_values(), with ORDER BY as the SQL equivalent. The example code is df.sort_values('revenue', ascending=False).
For Deduplicate, the pandas method is df.drop_duplicates(), with DISTINCT as the SQL equivalent. The example code is df.drop_duplicates(subset=['order_id']).
For Window / Rolling, the pandas methods are df.rolling(), df.expanding(), df.shift(), with OVER() window functions as the SQL equivalent. The example code is df['7d_avg'] = df['orders'].rolling(7).mean().
Apply Function and Pivot
For Apply function, the pandas methods are df.apply(), df.map(), df.transform(), with CASE WHEN / custom UDF as the SQL equivalent. The example code is df['tier'] = df['spend'].apply(lambda x: 'High' if x>5000 else 'Low').
For Pivot, the pandas methods are df.pivot_table(), pd.crosstab(), with PIVOT (limited in SQL) as the SQL equivalent. The example code is df.pivot_table(values='revenue', index='month', columns='category').
Data Cleaning Checklist - 6-Step Flow
After the SQL-to-pandas translation layer, the practical workflow is the Data Cleaning Checklist - 6-Step Flow. It moves from Missing Values to Feature Engineering in a sequence that keeps the dataset ready for analysis.
Conclusion
Pandas data manipulation is strongest when you can translate core SQL operations into pandas methods and then apply the six-step cleaning flow. For interviews, the final takeaway is simple: know the operation, map it to the pandas method, and explain the example code clearly.
The common mistake is treating duplicate rows after a JOIN as something to hide with DISTINCT or drop_duplicates() instead of diagnosing the JOIN condition and table granularity. This costs points because the best first step is to diagnose the JOIN condition and table granularity.