如何按设备组保留CSV文件中最新的100条时间戳记录？

Hey there! Let's work through this problem for your app—since you're dealing with large, regular timestamped data (100k rows every few minutes) and need to drop the earliest entry per device group, here are practical, efficient solutions for both Python and JavaScript.

Python Approaches #

The built-in csv module doesn't have a direct "limit rows per group" parameter, but we can implement this with two passes over the data (to avoid loading everything into memory at once) or use a data processing library like pandas for cleaner code.

1. Memory-Efficient Two-Pass Method (Using Built-in csv) #

This method is great for extremely large datasets since it only keeps track of the earliest timestamp per device, not all rows.

import csv
from datetime import datetime

# Configure these based on your CSV structure
TIMESTAMP_COL = "时间戳"
DEVICE_ID_COL = "设备ID"
# Adjust the format to match your timestamp (e.g., '%Y-%m-%dT%H:%M:%S' for ISO)
TIMESTAMP_FORMAT = "%Y-%m-%d %H:%M:%S"

# First pass: Collect the earliest timestamp for each device
device_earliest_ts = {}
with open("input.csv", "r", newline="") as infile:
    reader = csv.DictReader(infile)
    for row in reader:
        device_id = row[DEVICE_ID_COL]
        try:
            ts = datetime.strptime(row[TIMESTAMP_COL], TIMESTAMP_FORMAT)
        except ValueError:
            # Handle invalid timestamps if needed
            continue
        if device_id not in device_earliest_ts or ts < device_earliest_ts[device_id]:
            device_earliest_ts[device_id] = ts

# Second pass: Write all rows except the earliest per device
with open("input.csv", "r", newline="") as infile, open("output.csv", "w", newline="") as outfile:
    reader = csv.DictReader(infile)
    writer = csv.DictWriter(outfile, fieldnames=reader.fieldnames)
    writer.writeheader()
    
    for row in reader:
        device_id = row[DEVICE_ID_COL]
        ts = datetime.strptime(row[TIMESTAMP_COL], TIMESTAMP_FORMAT)
        if ts != device_earliest_ts[device_id]:
            writer.writerow(row)

Notes:

• If your timestamps are Unix epoch numbers (e.g., 1699999999), skip datetime.strptime and convert directly to int(row[TIMESTAMP_COL]) for faster comparisons.
• This uses minimal memory since we only store one timestamp per device, not all rows.

2. Simplified Method with Pandas #

If you're comfortable with pandas, this reduces the code to a few lines and handles large datasets efficiently (pandas uses optimized under-the-hood operations):

import pandas as pd

# Read the CSV, parsing timestamps automatically
df = pd.read_csv(
    "input.csv",
    parse_dates=[TIMESTAMP_COL],  # Replace with your timestamp column name
    dtype={DEVICE_ID_COL: str}  # Ensure device IDs are treated as strings
)

# Filter out the earliest timestamp per device group
filtered_df = df.groupby(DEVICE_ID_COL).apply(
    lambda group: group[group[TIMESTAMP_COL] != group[TIMESTAMP_COL].min()]
).reset_index(drop=True)

# Write the result to a new CSV
filtered_df.to_csv("output.csv", index=False)

Notes:

• Pandas is fast for this kind of grouping/filtering and works well with 100k-row datasets.
• If memory is a concern, use pd.read_csv with chunksize to process data in batches.

JavaScript (Node.js) Approach #

If you prefer JavaScript, you can handle large CSV files efficiently using Node.js streams to avoid loading all data into memory. You'll need two lightweight packages: csv-parser for reading and csv-writer for writing.

First, install the dependencies:

npm install csv-parser csv-writer

Then use this code:

const fs = require('fs');
const csv = require('csv-parser');
const createCsvWriter = require('csv-writer').createObjectCsvWriter;

// Configure these based on your CSV structure
const TIMESTAMP_COL = "时间戳";
const DEVICE_ID_COL = "设备ID";

// First pass: Collect earliest timestamp per device
const deviceEarliestTs = {};
let fieldnames = [];

fs.createReadStream('input.csv')
  .pipe(csv())
  .on('headers', (headers) => {
    fieldnames = headers;
  })
  .on('data', (row) => {
    const deviceId = row[DEVICE_ID_COL];
    // Convert timestamp to Date object (use parseInt(row[TIMESTAMP_COL]) for Unix epoch)
    const ts = new Date(row[TIMESTAMP_COL]);
    
    if (!deviceEarliestTs[deviceId] || ts < deviceEarliestTs[deviceId]) {
      deviceEarliestTs[deviceId] = ts;
    }
  })
  .on('end', () => {
    // Initialize CSV writer
    const csvWriter = createCsvWriter({
      path: 'output.csv',
      header: fieldnames.map(name => ({ id: name, title: name }))
    });

    // Second pass: Write rows excluding the earliest per device
    const filteredRows = [];
    fs.createReadStream('input.csv')
      .pipe(csv())
      .on('data', (row) => {
        const deviceId = row[DEVICE_ID_COL];
        const ts = new Date(row[TIMESTAMP_COL]);
        
        if (ts.getTime() !== deviceEarliestTs[deviceId].getTime()) {
          filteredRows.push(row);
        }
      })
      .on('end', () => {
        csvWriter.writeRecords(filteredRows)
          .then(() => console.log('Filtered data written to output.csv successfully!'));
      });
  });

Notes:

• Streams ensure we only process small chunks of data at a time, making this suitable for large files.
• For Unix epoch timestamps, replace new Date(...) with parseInt(row[TIMESTAMP_COL]) and compare numeric values directly (faster than Date objects).

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