Explain Stack vs Unstack and their use in data transformation.

Stack and unstack are fundamental Pandas DataFrame methods used for reshaping data between "long" and "wide" formats by manipulating index levels and columns. Stack transforms columns into rows, creating a MultiIndex, moving from a wide to a long format. Conversely, unstack pivots one or more levels from the DataFrame's index into new columns, transforming data from long to wide.

Mechanics and Use Cases

df.stack() takes the innermost column level and rotates it into the innermost row index level, resulting in a Series (if a single column) or DataFrame with a MultiIndex. This is useful for normalizing data, preparing it for statistical analysis that often expects a long format, or for efficient storage in analytical databases where columnar storage benefits from fewer distinct values per column (e.g., Snowflake, BigQuery).

df.unstack() performs the inverse operation. It takes a specified level from the DataFrame's index (which must be a MultiIndex) and pivots its unique values into new columns. This is commonly used for creating cross-tabulations, pivoting data for specific reports, or preparing data for tools that expect a wide format (e.g., certain visualization libraries or machine learning models). Both methods can take a level argument to specify which index/column level to operate on.

Example and Trade-offs

Consider a wide DataFrame. stack() will convert its columns into a new index level, making it "longer." If the original wide format had missing values, stack() might introduce NaNs. Conversely, unstack() will pivot an index level to columns, potentially introducing NaN values where combinations don't exist in the original data.

import pandas as pd
# Wide format DataFrame
df_wide = pd.DataFrame({'Sales_2020': [100, 150], 'Sales_2021': [110, 160]}, index=['NY', 'LA'])
# df_long = df_wide.stack() # Stacks 'Sales_2020', 'Sales_2021' into a new index level
# df_long.unstack(level=0) # Unstacks the first index level back to columns

In production, handling NaN values resulting from these transformations is crucial, often requiring fillna() or careful aggregation. For very large datasets, these operations can be memory-intensive, and their performance should be evaluated.

In the interview, also mention how these operations are fundamental for data normalization and denormalization, impacting data storage efficiency and query performance in systems like columnar databases.