Convert Dictionary to DataFrame
Company: Walmart Labs
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
Using Python and pandas, convert the following dictionary into a DataFrame. Each top-level key is a target column name, and each value is a list of `[row_id, cell_value]` pairs. Use the first element of each pair as the row identifier, align rows by `row_id` across all keys, and produce a wide DataFrame with one row per `row_id` and one column per top-level key.
Input:
```python
data = {
'text1': [[123, '2'], [345, '3']],
'text2': [[123, '4'], [345, '4']]
}
```
Expected structure:
- `row_id = 123` -> `text1 = '2'`, `text2 = '4'`
- `row_id = 345` -> `text1 = '3'`, `text2 = '4'`
Write Python code to create the DataFrame.
Overview: This question evaluates proficiency in Python data manipulation and pandas DataFrame construction, specifically handling nested list structures and aligning rows by identifiers.
Given a key-value style table kv_cells with columns (key_name, row_id, cell_value), pivot the data to produce one row per row_id and one column per key_name (text1, text2).
Tables
kv_cells(key_name VARCHAR, row_id INTEGER, cell_value VARCHAR)
Hints
- Use conditional aggregation with CASE expressions to pivot key_name values into columns.
- GROUP BY row_id and compute each target column with MAX(CASE WHEN key_name = 'textX' THEN cell_value END).