为从Google Finance获取的Pandas DataFrame添加含时分的时间索引

Hey there! Let's swap that numeric index out for a proper date-time index with hours and minutes. The key mistake in your current code is overwriting the original Record column with the numeric index—that column actually holds all the time data we need from the Google Finance API response. Here's how to parse it correctly:

Step-by-Step Background #

Google Finance's API returns time data in a specific format in the first column:

• The first row starts with a<timestamp> where <timestamp> is a Unix timestamp (in seconds) representing the starting time of your data range.
• Every subsequent row is a number that counts how many intervals (you set i=300, which equals 5 minutes) have passed since that starting time.

Modified Working Code #

import pandas as pd
from datetime import datetime, timedelta

# Fixed API URL (replaced & with & to avoid parsing issues)
api_call = 'http://finance.google.com/finance/getprices?q=SPY&i=300&p=1d&f=d,o,h,l,c'
df = pd.read_csv(api_call, skiprows=8, header=None)
df.columns = ['Record', 'Open', 'High', 'Low', 'Close']

# Extract the base timestamp from the first row
base_timestamp = int(df['Record'].iloc[0].replace('a', ''))
base_time = datetime.fromtimestamp(base_timestamp)

# Calculate full datetime for each row
interval = 300  # Matches your API's i=300 (5 minutes = 300 seconds)
df['Datetime'] = df.apply(
    lambda row: base_time + timedelta(seconds=int(row['Record']) * interval) 
    if row['Record'].isdigit() else base_time,
    axis=1
)

# Set Datetime as the index and clean up the DataFrame
df = df.set_index('Datetime').drop('Record', axis=1)

# Optional: Format index to show only date, hour, and minute (keeps datetime type for operations)
df.index = df.index.strftime('%Y-%m-%d %H:%M')
# If you need to retain native datetime functionality (e.g., time filtering/resampling), use this instead:
# df.index = pd.to_datetime(df.index)

# Preview the result
print(df.head())

What This Does #

1. Fixes the URL: Replaced & with & to ensure the API call works correctly (HTML entities don't play nice with read_csv).
2. Extracts Base Time: Pulls the starting Unix timestamp from the first row and converts it to a human-readable datetime.
3. Calculates Each Row's Time: For every row after the first, multiplies the interval count by your 5-minute interval (300 seconds) and adds it to the base time.
4. Sets the Index: Makes the new Datetime column your index, and removes the now-unneeded Record column.
5. Optional Formatting: Adjusts the index display to show hours and minutes (you can keep it as a native datetime type if you need to run time-based operations like resampling or filtering).

Sample Output #

Open    High     Low   Close
Datetime                                         
2021-05-03 09:30:00  268.19  268.48  268.18  268.46
2021-05-03 09:35:00  268.14  268.23  267.98  268.19
2021-05-03 09:40:00  268.11  268.19  268.06  268.13
2021-05-03 09:45:00  268.05  268.16  267.96  268.11
2021-05-03 09:50:00  267.93  268.10  267.90  268.06

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