Pandas describe()返回值异常问题及苹果、Alphabet股票收益统计需求

Hey there! Let's break down why your current code is returning categorical stats (count, unique, top, freq) instead of the numerical metrics you need for stock returns, and how to fix it.

Why This Happens #

The issue is that your stock return columns are likely stored as object/string data types in pandas (maybe they include percent signs, currency symbols, or were misinterpreted during Excel import). When pandas sees an object-type column, it automatically generates summary stats for categorical data instead of numerical stats like mean, min, or max.

Step-by-Step Solution #

1. Verify Data Types First #

First, check what type each column is to confirm the problem:

import pandas as pd
Data = pd.read_excel('Exercise1_DataPython.xlsx')

# Print data types for all columns
print(Data.dtypes)

If your return columns show object instead of float64 or int64, that's the root cause.

2. Convert Columns to Numerical Type #

You'll need to clean any non-numeric characters (like %, $, or commas) and convert the columns to a float type. For example, if your returns are formatted as percentages (e.g., "2.5%"):

# Adjust column names to match your Excel file!
Data['Apple_Return'] = Data['Apple_Return'].str.replace('%', '').astype(float) / 100
Data['Alphabet_Return'] = Data['Alphabet_Return'].str.replace('%', '').astype(float) / 100

If there are other non-numeric values (like missing entries or text), use pd.to_numeric with errors='coerce' to turn invalid values into NaN (which pandas will handle gracefully in stats calculations):

Data['Apple_Return'] = pd.to_numeric(Data['Apple_Return'], errors='coerce')
Data['Alphabet_Return'] = pd.to_numeric(Data['Alphabet_Return'], errors='coerce')

3. Generate Numerical Summary Stats #

Now run describe() again—you'll get the mean, min, max, percentiles, and more:

# Get full numerical stats for all numeric columns
print(Data.describe())

How to Target Specific Columns/Stats #

If you don't want stats for every column, you can narrow it down to just your stock return columns:

Option 1: Summary Stats for Specific Columns #

# Select only the return columns and get their summary stats
return_stats = Data[['Apple_Return', 'Alphabet_Return']].describe()
print(return_stats)

Option 2: Get Individual Stats (Mean, Min, Max) #

If you only need specific metrics, use pandas' aggregation methods:

# Calculate mean returns for both stocks
mean_returns = Data[['Apple_Return', 'Alphabet_Return']].mean()
print("Mean Returns:\n", mean_returns)

# Get min and max returns in one call
min_max_returns = Data[['Apple_Return', 'Alphabet_Return']].agg(['min', 'max'])
print("\nMin & Max Returns:\n", min_max_returns)

Example Full Code #

Putting it all together:

import pandas as pd

# Load data
Data = pd.read_excel('Exercise1_DataPython.xlsx')

# Check initial data types
print("Initial Data Types:\n", Data.dtypes)

# Clean and convert return columns
Data['Apple_Return'] = pd.to_numeric(Data['Apple_Return'].str.replace('%', ''), errors='coerce') / 100
Data['Alphabet_Return'] = pd.to_numeric(Data['Alphabet_Return'].str.replace('%', ''), errors='coerce') / 100

# Confirm converted types
print("\nUpdated Data Types:\n", Data.dtypes)

# Get targeted summary stats
print("\nStock Return Summary Stats:\n", Data[['Apple_Return', 'Alphabet_Return']].describe())

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