请求协助：将Yahoo Finance年报数据列转行构建标准化数据库

Got it, let's break down how to solve this wide-to-long data transformation—this is a super common task when dealing with financial datasets, and pandas (Python) is perfect for handling it efficiently, even for batch processing multiple companies.

Step 1: Understand the Raw Data Structure #

First, let's confirm what your input data looks like (per company):

company_account  2020  2021  2022
Revenue          1000  1200  1500
Net Income        200   250   300
Total Assets     5000  5500  6000

Each row is a financial account, each column is a year's value. Our goal is to flip this into rows where each entry has a company, year, account, and value.

Step 2: Single Company Transformation #

Start with a single company to get the pattern right. Here's the pandas code to do the transposition:

import pandas as pd

# Load the single company's data (adjust the file path as needed)
df_single = pd.read_csv("apple_annual_report.csv")

# Use melt() to convert wide columns (years) to a single "year" column
df_long = df_single.melt(
    id_vars=["company_account"],  # Keep this column as-is
    var_name="year",              # Name for the new year column
    value_name="value"            # Name for the new value column
)

# Add the company name column (you can pull this from the filename or hardcode for testing)
df_long["Company"] = "Apple Inc."

# Reorder columns to match your desired structure: Company, year, company_account, value
df_long = df_long[["Company", "year", "company_account", "value"]]

# Optional: Clean up data types (convert year to integer, value to float if needed)
df_long["year"] = df_long["year"].astype(int)
df_long["value"] = df_long["value"].astype(float)

Step 3: Batch Process Multiple Companies #

To scale this to multiple companies, we can loop through all your data files (assuming each file is for one company, named something like google_annual_report.csv):

import os
import pandas as pd

# Create a list to store all processed dataframes
all_processed_data = []

# Define the folder where your annual report CSVs are stored
data_folder = "annual_reports"

# Loop through every file in the folder
for filename in os.listdir(data_folder):
    if filename.endswith(".csv"):
        # Extract company name from the filename (adjust this logic to match your naming convention)
        company_name = filename.replace("_annual_report.csv", "").replace("_", " ")
        
        # Load the file
        df = pd.read_csv(os.path.join(data_folder, filename))
        
        # Apply the same melt transformation as before
        df_long = df.melt(
            id_vars=["company_account"],
            var_name="year",
            value_name="value"
        )
        
        # Add the company name
        df_long["Company"] = company_name
        
        # Reorder columns
        df_long = df_long[["Company", "year", "company_account", "value"]]
        
        # Clean data types (handle any string formatting like $ signs here if needed)
        df_long["year"] = df_long["year"].astype(int)
        df_long["value"] = df_long["value"].replace(r"[\$,]", "", regex=True).astype(float)
        
        # Add to our master list
        all_processed_data.append(df_long)

# Combine all dataframes into one final dataset
final_dataset = pd.concat(all_processed_data, ignore_index=True)

# Save the result to a new CSV (or database, if you're loading directly into SQL)
final_dataset.to_csv("all_companies_annual_data_long.csv", index=False)

Key Notes & Troubleshooting #

• Filename Naming: Make sure your filenames follow a consistent pattern so extracting the company name is easy—adjust the company_name logic if your files are named differently.
• Cleaning Values: If your Yahoo Finance data includes currency symbols ($) or commas, use the replace regex trick in the code above to strip those before converting to float.
• Missing Data: If some years have empty values, use df_long.dropna() to remove them or df_long.fillna(0) to fill with zeros, depending on your needs.
• Database Integration: Once you have the long-format DataFrame, you can use pandas.to_sql() to directly load it into a database (like PostgreSQL, MySQL, or SQLite) instead of saving to CSV.

Hope this helps you get your database set up smoothly—feel free to tweak the code if your raw data has edge cases (like extra columns or non-standard account names)!

内容的提问来源于stack exchange，提问作者Niels van Leeuwen