Merge and Concatenate Inconsistent Order Files with Pandas

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Take-home Project rounds at Boston Consulting Group.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Boston Consulting Group during technical interviews.

Question

orders_2023

+----------+-------------+--------+
| order_id | customer_id | amount |
+----------+-------------+--------+
| 101      | C001        | 120.5  |
| 102      | C002        |  75.0  |
| 103      | C003        | 140.0  |
+----------+-------------+--------+

​

orders_2024

+----------+-------------+--------+
| orderid  | customer_id | amount |
+----------+-------------+--------+
| 201      | C001        | 110.0  |
| 202      | C004        |  95.0  |
| 203      | C005        | 180.0  |
+----------+-------------+--------+

##### Scenario

BCG CodeSignal notebook – merging annual order files with schema inconsistencies

##### Question

Using Python (pandas), load orders_2023.csv and orders_2024.csv, rename columns so both have ['order_id','customer_id','amount'], cast amount to float, then vertically concatenate them into one DataFrame called orders_all.

##### Hints

read_csv ➜ rename ➜ astype ➜ concat; watch the typo in orderid.

PracHub · Accepted Answer

This question evaluates proficiency with pandas-based data manipulation, including schema alignment, column renaming, type casting, and vertical concatenation when merging CSV files.

Quick Overview

Merge and Concatenate Inconsistent Order Files with Pandas