We're sorry but this app doesn't work properly without JavaScript enabled. Please enable it to continue.

This lesson's interactive features are locked, please to keep using them

Standardizing Schemas

The pd.concat() method works best when the DataFrames have the same columns... but real data often doesn't. Before stacking rows from multiple sources, you usually need to standardize the schema: the names and meanings of the columns in a dataset.

Imagine a support team that exports incident data from two systems: email and chat.

email = pd.DataFrame(
    {
        "ticket_id": ["E001", "E002"],
        "opened_at": ["2024-01-01", "2024-01-01"],
        "severity": ["high", "low"],
        "inbox": ["billing", "support"],
    }
)

chat = pd.DataFrame(
    {
        "conversation_id": ["C101", "C102"],
        "started_at": ["2024-01-02", "2024-01-02"],
        "priority": ["high", "medium"],
        "queue": ["support", "sales"],
    }
)

The tricky part is that the two DataFrames use different column names for the same concepts:

  • conversation_id vs. ticket_id
  • started_at vs. opened_at
  • priority vs. severity
  • queue vs. inbox

If we concatenate these tables as-is, Pandas will keep all the different columns and fill the gaps with NaN. The resulting DataFrame won't allow for any meaningful analysis... lame. So, we should rename to a common schema:

email_std = email.rename(columns={"inbox": "source_detail"})

chat_std = chat.rename(
    columns={
        "conversation_id": "ticket_id",
        "started_at": "opened_at",
        "priority": "severity",
        "queue": "source_detail",
    }
)

Now both tables have the same core columns: ticket_id, opened_at, severity, and source_detail. For clarity, I also like to mark which system each incident came from:

email_std["source"] = "email"
chat_std["source"] = "chat"

Finally, we can concatenate the data:

combined = pd.concat([email_std, chat_std], ignore_index=True)
print(combined)
#   ticket_id   opened_at severity source_detail source
# 0      E001  2024-01-01     high       billing  email
# 1      E002  2024-01-01      low       support  email
# 2      C101  2024-01-02     high       support   chat
# 3      C102  2024-01-02   medium         sales   chat

Now we have one clean incidents table with consistent columns and a clear indication of the source system.

Assignment

SnackStack has in-store and online sales exports, but they use different column names for the same concepts.

Complete the standardize_and_combine function. It accepts two DataFrames: in_store and online sales data. It should standardize column names, add a source identifier, and combine them into one DataFrame.

    • "order_id" to "sale_id"
    • "order_date" to "date"
    • "shipping" to "channel_detail"