按日期时间与Type分组求和Quantity的数据集批量处理工具咨询

Hey there! For your task of aggregating Quantity by date_time and Type at scale (instead of manual processing), here are the best tools and practical code examples to get you sorted:

1. R (Using dplyr - Tidyverse) #

Since you provided an R data structure, dplyr is a natural, readable choice—it’s built for intuitive grouped operations and handles large datasets efficiently.

# Load the required library
library(dplyr)

# Your initial dataset (as provided)
df <- structure(list(date_time = structure(c(1517516099, 1517516099, 1517516099, 1517516099, 1517516095, 1517516092, 1517516092, 1517516092, 1517516092, 1517516092, 1517516092, 1517516088, 1517516084, 1517516081, 1517516074, 1517516073, 1517516071, 1517516068, 1517516061, 1517516053 ), class = c("POSIXct", "POSIXt"), tzone = ""), Buyer_from = c("127 - TULLETT PREBON", "127 - TULLETT PREBON", "127 - TULLETT PREBON", "3 - XP Investimentos CCTVM S/A", "85 - BTG Pactual CTVM S.A.", "85 - BTG Pactual CTVM S.A.", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "115 - H.COMMCOR DTVM LTDA", "115 - H.COMMCOR DTVM LTDA", "8 - UBS BRASIL CCTVM S/A", "3 - XP Investimentos CCTVM S/A", "8 - UBS BRASIL CCTVM S/A", "8 - UBS BRASIL CCTVM S/A", "8 - UBS BRASIL CCTVM S/A"), Price = c(3176.5, 3176.5, 3176.5, 3176.5, 3177, 3177, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3178, 3178.5, 3178, 3178, 3178), Quantity = c(10, 5, 50, 5, 5, 5, 55, 5, 5, 5, 30, 70, 30, 10, 10, 5, 5, 10, 5, 10), Seller_from = c("85 - BTG Pactual CTVM S.A.", "122 - BGC LIQUIDEZ DTVM", "85 - BTG Pactual CTVM S.A.", "3 - XP Investimentos CCTVM S/A", "88 - CM Capital Markets CCTVM LTDA", "122 - BGC LIQUIDEZ DTVM", "122 - BGC LIQUIDEZ DTVM", "122 - BGC LIQUIDEZ DTVM", "8 - UBS BRASIL CCTVM S/A", "8 - UBS BRASIL CCTVM S/A", "92 - RENASCENÇA DTVM LTDA", "92 - RENASCENÇA DTVM LTDA", "85 - BTG Pactual CTVM S.A.", "85 - BTG Pactual CTVM S.A.", "122 - BGC LIQUIDEZ DTVM", "122 - BGC LIQUIDEZ DTVM", "3 - XP Investimentos CCTVM S/A", "77 - CITIGROUP GMB CCTVM S/A", "386 - RICO INVESTIMENTOS - GRUPO XP", "386 - RICO INVESTIMENTOS - GRUPO XP"), Type = structure(c(4L, 4L, 4L, 4L, 4L, 4L, 4L, 1L, 1L, 1L, 1L, 4L, 1L, 4L, 4L, 4L, 1L, 4L, 4L, 4L), .Label = c("Buyer", "3", "4", "Seller"), class = "factor")), row.names = c(NA, 20L), class = "data.frame")

# Perform grouped aggregation
aggregated_df <- df %>%
  group_by(date_time, Type) %>%
  summarise(total_quantity = sum(Quantity), .groups = 'drop')

# View the result
print(aggregated_df)

Why this works: #

• group_by(date_time, Type) clusters rows by your two target columns
• summarise() calculates the sum of Quantity for each group
• .groups = 'drop' ensures the output is a clean, ungrouped data frame for further processing

2. R (Using data.table - Ultra-Fast for Large Data) #

If you’re working with millions of rows, data.table is optimized for speed and memory efficiency—it’s significantly faster than base R or even dplyr for big datasets.

library(data.table)

# Convert your data frame to a data.table
dt <- as.data.table(df)

# Aggregate with data.table syntax
aggregated_dt <- dt[, .(total_quantity = sum(Quantity)), by = .(date_time, Type)]

print(aggregated_dt)

3. Python (Using Pandas) #

If you prefer Python, Pandas is the industry standard for data manipulation. It offers a clean, concise syntax for grouped aggregation.

import pandas as pd

# Recreate your dataset in Python
data = {
    'date_time': pd.to_datetime([1517516099, 1517516099, 1517516099, 1517516099, 1517516095, 1517516092, 1517516092, 1517516092, 1517516092, 1517516092, 1517516088, 1517516084, 1517516081, 1517516074, 1517516073, 1517516071, 1517516068, 1517516061, 1517516053], unit='s'),
    'Buyer_from': ["127 - TULLETT PREBON", "127 - TULLETT PREBON", "127 - TULLETT PREBON", "3 - XP Investimentos CCTVM S/A", "85 - BTG Pactual CTVM S.A.", "85 - BTG Pactual CTVM S.A.", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "147 - ATIVA INVESTIMENTOS S.A. CTCV", "115 - H.COMMCOR DTVM LTDA", "115 - H.COMMCOR DTVM LTDA", "8 - UBS BRASIL CCTVM S/A", "3 - XP Investimentos CCTVM S/A", "8 - UBS BRASIL CCTVM S/A", "8 - UBS BRASIL CCTVM S/A"],
    'Price': [3176.5, 3176.5, 3176.5, 3176.5, 3177, 3177, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3177.5, 3178, 3178.5, 3178, 3178, 3178],
    'Quantity': [10, 5, 50, 5, 5, 5, 55, 5, 5, 5, 30, 70, 30, 10, 10, 5, 5, 10, 5, 10],
    'Seller_from': ["85 - BTG Pactual CTVM S.A.", "122 - BGC LIQUIDEZ DTVM", "85 - BTG Pactual CTVM S.A.", "3 - XP Investimentos CCTVM S/A", "88 - CM Capital Markets CCTVM LTDA", "122 - BGC LIQUIDEZ DTVM", "122 - BGC LIQUIDEZ DTVM", "122 - BGC LIQUIDEZ DTVM", "8 - UBS BRASIL CCTVM S/A", "8 - UBS BRASIL CCTVM S/A", "92 - RENASCENÇA DTVM LTDA", "92 - RENASCENÇA DTVM LTDA", "85 - BTG Pactual CTVM S.A.", "85 - BTG Pactual CTVM S.A.", "122 - BGC LIQUIDEZ DTVM", "122 - BGC LIQUIDEZ DTVM", "3 - XP Investimentos CCTVM S/A", "77 - CITIGROUP GMB CCTVM S/A", "386 - RICO INVESTIMENTOS - GRUPO XP", "386 - RICO INVESTIMENTOS - GRUPO XP"],
    'Type': pd.Categorical(["Seller", "Seller", "Seller", "Seller", "Seller", "Seller", "Seller", "Buyer", "Buyer", "Buyer", "Buyer", "Seller", "Buyer", "Seller", "Seller", "Seller", "Buyer", "Seller", "Seller", "Seller"], categories=["Buyer", "3", "4", "Seller"])
}

df = pd.DataFrame(data)

# Perform grouped aggregation
aggregated_df = df.groupby(['date_time', 'Type'])['Quantity'].sum().reset_index(name='total_quantity')

# View the result
print(aggregated_df)

Scaling further with Pandas: #

For datasets too large to fit in memory, use pd.read_csv(chunksize=...) to process data in chunks, or switch to Dask (a parallel computing library that extends Pandas for distributed datasets).

All these tools are designed for scalability—they can handle repetitive, large-scale aggregation tasks with ease, and you can automate workflows (e.g., batch processing files, scheduling scripts) to eliminate manual work. Pick the one that fits your existing workflow, and you’ll be set!

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