技术求助：DataFrame中Unix时间戳转日期、ISO8601解析及列重命名

Hey Aaron, let's break down your two pandas problems step by step—these are super common when working with financial APIs, so I’ve got you covered!

1. Convert Unix Timestamps to Dates in DataFrame #

Unix timestamps come in two common flavors: second-level and millisecond-level (the latter is pretty standard for financial data). Here’s how to handle both:

For Second-Level Timestamps #

If your timestamp column is named something like unix_timestamp, use pd.to_datetime() with unit='s':

import pandas as pd

# Example DataFrame with Unix timestamp column
df = pd.DataFrame({'unix_timestamp': [1620000000, 1620086400, 1620172800]})

# Convert to readable date
df['time'] = pd.to_datetime(df['unix_timestamp'], unit='s')

For Millisecond-Level Timestamps #

Many financial APIs return timestamps in milliseconds—just switch the unit parameter to 'ms':

df['time'] = pd.to_datetime(df['unix_timestamp'], unit='ms')

If Timestamp is the DataFrame Index #

If your timestamp is already set as the index instead of a column, update it directly:

df.index = pd.to_datetime(df.index, unit='s')  # or 'ms' if needed

2. Fix ISO 8601 Date Recognition & Rename Columns from API Data #

Let’s tackle the unrecognized ISO date and generic column names together with a full workflow:

Step 1: Pull & Load API Data #

First, let’s assume you’re using requests to fetch data (adjust if you’re using a different library):

import requests

response = requests.get("your_financial_api_endpoint_url")
raw_data = response.json()
df = pd.DataFrame(raw_data)

Step 2: Force ISO 8601 Date Parsing #

If pandas isn’t auto-detecting the ISO date in column 0, explicitly convert it with pd.to_datetime(). You can either specify the format or let pandas infer it:

# Option 1: Specify the exact ISO format (adjust if your date has time zones or different separators)
df[0] = pd.to_datetime(df[0], format="%Y-%m-%dT%H:%M:%SZ")

# Option 2: Let pandas auto-infer the format (great if your date has variable time zones)
df[0] = pd.to_datetime(df[0], infer_datetime_format=True)

Step 3: Rename Columns #

Now replace those generic [0,1,2,3,4,5] column names with your desired labels:

df.columns = ['time', 'low', 'high', 'open', 'close', 'volume']

Full Combined Example #

Putting it all together for clarity:

import pandas as pd
import requests

# Fetch data from API
response = requests.get("your_financial_api_endpoint_url")
raw_data = response.json()
df = pd.DataFrame(raw_data)

# Fix ISO date column
df[0] = pd.to_datetime(df[0], infer_datetime_format=True)

# Rename columns
df.columns = ['time', 'low', 'high', 'open', 'close', 'volume']

# Optional: Set 'time' as the index for easier time-series operations
df = df.set_index('time')

If you’re still having trouble with the ISO date, double-check the exact format of the date strings in column 0—sometimes extra characters (like fractional seconds or non-standard time zone codes) can throw off parsing. You can print a sample value with print(df[0].iloc[0]) to spot inconsistencies!

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