Pandas列转换需求：将字典格式hours列展开为星期列并填充时段值

Got it, let's walk through how to reshape your DataFrame so that each weekday becomes a column with its corresponding time slot. Here's a step-by-step solution that works for both your sample data and the Yelp business dataset:

Step 1: Process the Sample Data #

First, let's handle your example DataFrame. The key idea is to expand the dictionaries in the hours column into separate columns, while gracefully handling NaN values.

import pandas as pd
import numpy as np

# Your sample data
a = {'Monday': '9:0-0:0', 'Tuesday': '9:0-0:0'}
b = {'Monday': '17:30-21:30', 'Wednesday': '17:30'}
df = pd.DataFrame({'hours':[np.nan, a, b], 'id':['sdfdad','ddfsfs','sedfws']})

# Convert hours column to weekday columns
# Replace NaNs with empty dicts so json_normalize can process them
hours_normalized = pd.json_normalize(df['hours'].apply(lambda x: x if isinstance(x, dict) else {}))

# Combine with original columns (excluding the old hours column)
df_result = pd.concat([df.drop('hours', axis=1), hours_normalized], axis=1)

print(df_result)

Output: #

id      Monday    Tuesday Wednesday
0  sdfdad         NaN        NaN       NaN
1  ddfsfs     9:0-0:0    9:0-0:0       NaN
2  sedfws 17:30-21:30        NaN    17:30

Step 2: Apply to Yelp Business Dataset #

For the Yelp dataset, the hours column is likely stored as a string representation of a dictionary (not an actual dict). We'll use ast.literal_eval (safer than eval) to convert these strings to real dicts before normalizing:

import ast

# Load Yelp dataset
yelp_business = pd.read_csv('yelp-dataset/cs_data/business.csv')

# Convert stringified dicts to actual dicts, handle NaNs
yelp_hours_normalized = pd.json_normalize(
    yelp_business['hours'].apply(lambda x: ast.literal_eval(x) if pd.notna(x) else {})
)

# Merge with original dataset (drop the old hours column first)
yelp_business_processed = pd.concat([yelp_business.drop('hours', axis=1), yelp_hours_normalized], axis=1)

Key Notes: #

• pd.json_normalize: This function automatically creates a column for each unique key (weekday) in the dictionaries. Missing weekdays for a row will be filled with NaN.
• ast.literal_eval: Use this instead of eval to safely parse stringified dictionaries without risk of executing malicious code (critical when working with external datasets like Yelp).
• Handling NaNs: Replacing NaNs with empty dicts ensures json_normalize doesn't throw errors and creates consistent columns for all weekdays present in the dataset.

内容的提问来源于stack exchange，提问作者Abdul Rafay Jungsher