3

I am processing some CSV files that do not fit in RAM.

The 2 CSV files have the following structure:

first.csv

id name timestamp
serial str yyyy-mm-dd hh:mm:ss

second.csv

id name date
serial str yyyy-mm-dd

The goal is to select rows from first.csv that match some criteria compared to second.csv:

  • name is equal
  • timestamp is in the range of [date-1, date+1].

After iterating all these rows the output can be combined into one output file.

7
  • How big are the files? Are both too big to fit in RAM? You could probably do this several ways in Awk; or you might be able to use join if you first filter out the daterange you want. Also you may want to look into dateutils (fresse.org/dateutils) for command-line date processing.
    – Wildcard
    Nov 17, 2021 at 0:09
  • only the first csv cannot fit to ram. The other fits it. I'll check dateutils it looks promising. Thanks! Nov 17, 2021 at 8:21
  • Ignoring the date requirement, I think you can do the rest with awk 'FNR==NR{a[$4]++; next} $3 in a' second.csv first.csv > output.csv but it would be easier to write and test a complete answer if you would give sample input and output.
    – Wildcard
    Nov 17, 2021 at 8:37
  • So as I understand, you use FNR==NR in order to iterate over the second.csv only and you save in an array the names. Then you check which rows in first.csv have their value in the 3rd column into the array you saved. Basically, that would solve the first requirement about having the same name column, right? Nov 17, 2021 at 8:49
  • 1
    Right. General site advice: for text processing questions, if you post example input and expected output for that input (not just format of the input/output but actual lines of text that can be used for a test case), you're a lot more likely to get complete answers.
    – Wildcard
    Nov 17, 2021 at 19:10

1 Answer 1

0

I don't know what's possible for this in shell, but I think it'd be difficult to write, and very hard to read (and maybe modify) later.

I have tested Go against awk for basic CSV tasks (selecting/dropping columns, filtering rows) and Go is faster (sometimes "much" faster).

For your post, I made a test file that's 8,640,001 rows and ~271 MB, and then made 2 example processors, one in Python and one in Go that utilize a write-as-you-read pattern so there's no intermediate storage (and both use buffered IO which gives efficiency gains on large files).

  • Python script: ran in ~70 seconds and used ~6.5 MB of memory
  • Go binary: ran in ~3.5 seconds and used ~10 MB of memory

But first, does it even do the job?

Basic setup

I made these two small samples to develop on:

first.csv

id,name,timestamp
1,foo,2000-01-01 00:00:00
2,foo,2000-01-02 00:00:00
3,foo,2000-01-03 00:00:00
4,foo,2000-01-04 00:00:00
5,foo,2000-01-05 00:00:00
6,bar,2000-02-01 00:00:00
7,bar,2000-02-02 00:00:00
8,bar,2000-02-03 00:00:00
9,bar,2000-02-04 00:00:00

second.csv

id,name,date
10,foo,2000-01-03
11,bar,2000-02-02

It wasn't clear what "date-1" and "date+1" meant, so I assumed you wanted "plus-or-minus one day".

When I run either the Go or Python code against those files, I get:

2,foo,2000-01-02 00:00:00
3,foo,2000-01-03 00:00:00
4,foo,2000-01-04 00:00:00
6,bar,2000-02-01 00:00:00
7,bar,2000-02-02 00:00:00
8,bar,2000-02-03 00:00:00

which is what I expect given my interpretation of your requirements, and the inputs:

foo 2000-01-03 and bar 2000-02-02

The test file

I made this test generator, which creates records for foo only, 1 second apart, for 100 days:

import csv
from datetime import datetime, timedelta

dt_start = datetime(2000, 1, 1)

with open('test.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(['id', 'name', 'timestamp'])

    # 1 line per second for 100 days
    for i in range(86400 * 100):
        plus_secs = timedelta(seconds=i + 1)
        writer.writerow([i + 1, 'foo', dt_start + plus_secs])

Here's what test.csv looks like:

% ll test.csv 
-rw-r--r--  1 alice  bob   271M Nov 19 22:19 test.csv

% wc -l test.csv 
 8640001 test.csv

Link the test file to first.csv, ln -fs test.csv first.csv and I'm ready to run the following...

Python

import csv
import sys
from datetime import datetime, timedelta

DATE_FMT = f'%Y-%m-%d'
DATETIME_FMT = f'%Y-%m-%d %H:%M:%S'

# Create lookup from second

# {name: [date-1day, date+1day]}
lookup = {}

with open('second.csv', newline='') as f:
    reader = csv.reader(f)
    header = next(reader)
    nm_col = header.index('name')
    dt_col = header.index('date')

    for row in reader:
        name = row[nm_col]
        dt_str = row[dt_col]

        dt = datetime.strptime(dt_str, DATE_FMT)
        min_dt = dt - timedelta(days=1)
        max_dt = dt + timedelta(days=1) # - timedelta(seconds=1)

        lookup[name] = [min_dt, max_dt]


# Create on-demand writer, and iterate over first, writing when we need to

writer = csv.writer(sys.stdout)

with open('first.csv', newline='') as f:
    reader = csv.reader(f)
    header = next(reader)
    nm_col = header.index('name')
    dt_col = header.index('timestamp')

    writer.writerow(header)

    for row in reader:
        name = row[nm_col]
        if name not in lookup:
            continue

        dt_str = row[dt_col]
        dt = datetime.strptime(dt_str, DATETIME_FMT)
        min_dt = lookup[name][0]
        max_dt = lookup[name][1]
        
        if dt < min_dt or dt > max_dt:
            continue

        writer.writerow(row)

And run the script:

% time python3 main.py > result.csv
python3 main.py > result.csv  69.93s user 0.40s system 98% cpu 1:11.07 total

% head -n5 result.csv 
id,name,timestamp
86400,foo,2000-01-02 00:00:00
86401,foo,2000-01-02 00:00:01
86402,foo,2000-01-02 00:00:02
86403,foo,2000-01-02 00:00:03

% tail -n5 result.csv 
259196,foo,2000-01-03 23:59:56
259197,foo,2000-01-03 23:59:57
259198,foo,2000-01-03 23:59:58
259199,foo,2000-01-03 23:59:59
259200,foo,2000-01-04 00:00:00  # is this right?

Which looks correct to me: only records for a 48-hour span, centered on lookup date. I'm not sure about the last found entry, which is from the first instant of the 4th—that's what the commented out - timedelta(seconds=1) bit is about.

Go

package main

import (
    "encoding/csv"
    "io"
    "os"
    "time"
)

type LookupEntry struct {
    oneDayBefore time.Time
    oneDayAfter  time.Time
}

const DATE_FMT = "2006-01-02"
const DATETIME_FMT = "2006-01-02 15:04:05"

var lookup = make(map[string]LookupEntry)

func main() {
    makeLookupTable()
    findMatchingEntries()
}

func makeLookupTable() {
    f, _ := os.Open("second.csv")
    defer f.Close()

    r := csv.NewReader(f)
    r.Read() // Discard header
    for {
        record, err := r.Read()
        if err == io.EOF {
            break
        }
        dt, _ := time.Parse(DATE_FMT, record[2])
        oneDayBefore := dt.AddDate(0, 0, -1)
        oneDayAfter := dt.AddDate(0, 0, 1)  // .Add(-time.Millisecond * 1000)
        lookup[record[1]] = LookupEntry{oneDayBefore, oneDayAfter}
    }
}

func findMatchingEntries() {
    f1, _ := os.Open("first.csv")
    defer f1.Close()

    w := csv.NewWriter(os.Stdout)

    r := csv.NewReader(f1)
    header, _ := r.Read()
    w.Write(header)

    for {
        record, err := r.Read()
        if err == io.EOF {
            break
        }

        lookupEntry, ok := lookup[record[1]]
        if !ok {
            continue
        }

        dt, _ := time.Parse(DATETIME_FMT, record[2])
        if dt.Before(lookupEntry.oneDayBefore) || dt.After(lookupEntry.oneDayAfter) {
            continue
        }

        w.Write(record)
    }
    w.Flush()
}

Build and run the test:

% go build main.go

% time ./main > result.csv   
./main > result.csv  3.53s user 0.14s system 104% cpu 3.504 total

% head -n5 result.csv 
86400,foo,2000-01-02 00:00:00
86401,foo,2000-01-02 00:00:01
86402,foo,2000-01-02 00:00:02
86403,foo,2000-01-02 00:00:03
86404,foo,2000-01-02 00:00:04

% tail -n5 result.csv 
259196,foo,2000-01-03 23:59:56
259197,foo,2000-01-03 23:59:57
259198,foo,2000-01-03 23:59:58
259199,foo,2000-01-03 23:59:59
259200,foo,2000-01-04 00:00:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .