读取大型文本文件时pandas数据类型异常问题求助

Great question! I’ve dealt with this exact quirk in the UCI household power dataset before—let’s break down what’s going on and how to fix it.

Root Cause #

This dataset uses ? characters to mark missing values. When you only load the first 5 rows with nrows=5, those sample rows don’t contain any ? markers. Pandas can easily infer the numeric types for all columns in this case.

But when you load the full dataset, pandas hits those scattered ? values. Since it can’t parse ? as a number, it defaults to setting the entire column’s data type to object (string type) to accommodate both valid numbers and the missing value markers. That’s why most columns switch from float64 to object in your full dataset output.

Fixes & Solutions #

1. Explicitly mark ? as missing values #

The simplest fix is to use the na_values parameter in pd.read_csv() to tell pandas that ? should be treated as NaN (pandas’ standard missing value marker). This keeps columns in their proper numeric types while handling missing data correctly:

df_all = pd.read_csv("household_power_consumption.txt", header=0, delimiter=';', na_values="?")

Run df_all.info() after this, and you’ll see all numeric columns are back to float64, with missing values properly encoded as NaN instead of strings.

2. Optional: Parse dates during loading (for time-series work) #

Since this is a time-series dataset, you can also combine the Date and Time columns into a datetime index on load—this makes time-based analysis way smoother. Add these parameters to your read call:

df_all = pd.read_csv(
    "household_power_consumption.txt",
    header=0,
    delimiter=';',
    na_values="?",
    parse_dates={'datetime': ['Date', 'Time']},  # Merge Date + Time into one datetime column
    index_col='datetime'  # Set the new datetime column as the dataset index
)

3. Handle missing values (if needed) #

Once your data is loaded with correct data types, you can choose how to address NaNs:

• Drop rows with missing values: df_clean = df_all.dropna()
• Fill missing values with a statistic (e.g., column mean): df_filled = df_all.fillna(df_all.mean(numeric_only=True))

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