请求协助：将DataFrame中的列拆分为日期和数值字段

Hey there! Let's figure out how to split that mixed column in your DataFrame into separate date and numeric columns. I'll cover the most common scenarios you might run into—just tweak the code to match your actual data format!

Case 1: Date and Value Separated by a Fixed Delimiter #

If your column uses a consistent separator (like space, comma, or |) between the date and number, use str.split() to split the column directly:

import pandas as pd

# Example DataFrame with space-separated date and value
df = pd.DataFrame({
    'mixed_column': ['2023-09-15 789', '2023-10-01 1234', '2023-10-10 56']
})

# Split the column into two new columns (expand=True creates separate columns)
df[['date', 'numeric_value']] = df['mixed_column'].str.split(' ', expand=True)

# Convert to proper data types: date to datetime, value to numeric
df['date'] = pd.to_datetime(df['date'])
df['numeric_value'] = pd.to_numeric(df['numeric_value'])

print(df)

• If your separator is something else (like |), just replace the ' ' in str.split() with your delimiter.
• If there are extra spaces around the separator, add .str.strip() to clean up:

  df[['date', 'numeric_value']] = df['mixed_column'].str.split('|', expand=True).apply(lambda x: x.str.strip())
  

Case 2: Date and Value Have Fixed Lengths (No Delimiter) #

If your date and value are stuck together but follow a fixed length (e.g., 20231005456 where the first 8 characters are the date 20231005 and the rest are the value 456), use str.slice() to extract parts by position:

df = pd.DataFrame({
    'mixed_column': ['20231005456', '20230915789', '2023112012']
})

# Extract first 8 characters for date, remaining for value
df['date'] = pd.to_datetime(df['mixed_column'].str.slice(0, 8), format='%Y%m%d')
df['numeric_value'] = pd.to_numeric(df['mixed_column'].str.slice(8))

print(df)

Case 3: Date is in Text Format (e.g., "Oct 05, 2023") #

If your date uses a textual format (like Oct 05, 2023 987), split from the end to avoid messing up the date's spaces:

df = pd.DataFrame({
    'mixed_column': ['Oct 05, 2023 987', 'Nov 12, 2023 432', 'Dec 31, 2023 10']
})

# Split once from the right to separate the numeric value
df[['date_part', 'numeric_value']] = df['mixed_column'].str.rsplit(' ', n=1, expand=True)

# Convert the date part to datetime using the correct format
df['date'] = pd.to_datetime(df['date_part'], format='%b %d, %Y')

# Drop the temporary date_part column if you don't need it
df = df.drop('date_part', axis=1)

print(df)

Quick Tips #

• If you have messy data with invalid entries, use the errors parameter to handle them gracefully:

  df['date'] = pd.to_datetime(df['date'], errors='coerce')  # Turns invalid dates into NaT
  df['numeric_value'] = pd.to_numeric(df['numeric_value'], errors='coerce')  # Turns invalid values into NaN
  

• First check unique values in your column to confirm the format: print(df['mixed_column'].unique())

内容的提问来源于stack exchange，提问作者Rustem Kussaiynov