从多份不同长度CSV提取指定列并按日期合并为数据框（R/Python）

R Solution #

First, let's fix the key issues in your original code:

1. Using ls() to collect data frames can pull in unintended objects from your environment—better to read files directly into a list.
2. left_join in Reduce only keeps dates from the first file; you need full_join to preserve all dates across every dataset.

Tidyverse Approach (Clean & Readable)

library(tidyverse)

# Set your target directory path
data_path <- ".../path"

# Get all CSV filenames (excludes full path for cleaner column names)
filenames <- list.files(data_path, pattern = "\\.csv$", full.names = FALSE)

# Read each file, keep only Date + Open, rename Open to the filename (without .csv)
df_list <- map(filenames, function(file) {
  read_csv(file.path(data_path, file), col_select = c(Date, Open)) %>%
    rename(!!str_remove(file, "\\.csv$") := Open) %>%
    mutate(Date = as.Date(Date)) # Ensure Date is formatted correctly
})

# Merge all data frames on Date, keeping every date from any file
merged_data <- reduce(df_list, full_join, by = "Date") %>%
  arrange(Date) # Sort results chronologically

Data.table Approach (Faster for Large Datasets)

If you need better performance with 1300 files, data.table is optimized for this kind of heavy lifting:

library(data.table)
library(stringr)

data_path <- ".../path"
filenames <- list.files(data_path, pattern = "\\.csv$", full.names = FALSE)

# Read files into a list of data.tables, rename Open column to filename
dt_list <- lapply(filenames, function(file) {
  fread(file.path(data_path, file), select = c("Date", "Open"))[, 
    (str_remove(file, "\\.csv$")) := Open][, Open := NULL][, Date := as.Date(Date)]
})

# Combine tables (fill NA for missing columns) and deduplicate dates
merged_data <- rbindlist(dt_list, fill = TRUE)[, 
  lapply(.SD, function(x) x[!is.na(x)][1]), by = Date][order(Date)]

Python Solution (Using Pandas) #

Pandas simplifies this task with index alignment and concatenation:

import pandas as pd
import os

data_path = ".../path"
filenames = [f for f in os.listdir(data_path) if f.endswith(".csv")]

# Read each file, set Date as index, rename Open to the filename
df_list = []
for file in filenames:
    df = pd.read_csv(
        os.path.join(data_path, file),
        usecols=["Date", "Open"],
        index_col="Date"
    )
    # Remove .csv suffix from column name
    df.rename(columns={"Open": file.replace(".csv", "")}, inplace=True)
    # Convert index to datetime for proper sorting
    df.index = pd.to_datetime(df.index)
    df_list.append(df)

# Concatenate all dataframes (automatically aligns dates, fills NA for missing values)
merged_data = pd.concat(df_list, axis=1)
# Sort results by date
merged_data.sort_index(inplace=True)

# Optional: Reset index to make Date a regular column instead of index
merged_data.reset_index(inplace=True)

Key fixes from your original code:

• We avoid cluttering the global environment by storing datasets in lists instead of individual variables.
• Using full outer joins (R's full_join, Python's concat) ensures no dates are lost across files.
• Explicit date formatting prevents sorting errors caused by string-based date values.

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