Split CSV files based on part of a column

Question

I have a CSV file, file.csv, containing date and time like this:

id0,2020-12-12T07:18:26,7f
id1,2017-04-28T19:59:00,80
id2,2017-04-28T03:14:35,e4
id3,2020-12-12T23:45:09,ff
id4,2020-12-12T09:12:34,a1
id5,2017-04-28T00:31:54,65
id6,2020-12-12T20:13:47,45
id7,2017-04-28T21:04:30,7f

I would like to split the file based on the date in column 2. Using the above example, it should create 2 files:

file_1.csv
id1,2017-04-28T19:59:00,80
id2,2017-04-28T03:14:35,e4
id5,2017-04-28T00:31:54,65
id7,2017-04-28T21:04:30,7f

and

file_2.csv
id0,2020-12-12T07:18:26,7f
id3,2020-12-12T23:45:09,ff
id4,2020-12-12T09:12:34,a1
id6,2020-12-12T20:13:47,45

I tried to use sort and awk to do the job but it splits the file into 8 files based on the date and time.

sort -k2 -t, file.csv | awk -F, '!($2 in col) {col[$2]=++i} {print > ("file_" i ".csv")}'

How can I split the file based on the date only (not date and time)?

glenn jackman · Accepted Answer · 2021-01-04 18:47:52Z

5

How about:

awk -F', ' '
  { date = substr($2,1,10) }
  !(date in outfile) { outfile[date] = "file_" (++numout) ".csv" }
  { print > outfile[date] }
' file.csv

If it's a large file with many unique dates, you may need to prevent "too many open files" errors with:

  { print >> outfile[date]; close(outfile[date]) }

edited Jan 4, 2021 at 18:47

answered Jan 3, 2021 at 16:16

glenn jackman

88.6k16 gold badges124 silver badges179 bronze badges

Why are you splitting only up to 7th character, excluding the day? Also, gawk run with --lint reports several warnings.

Arkadiusz Drabczyk
– Arkadiusz Drabczyk

2021-01-03 16:20:05 +00:00
Commented Jan 3, 2021 at 16:20
Thanks for the report. Fixed. There's one remaining lint warning but gawk treats uninitialized variables as zero in numeric context which is OK here.

glenn jackman
– glenn jackman

2021-01-03 16:22:58 +00:00
Commented Jan 3, 2021 at 16:22

Add a comment |

Ed Morton · Accepted Answer · 2021-01-03 17:42:48Z

$ cat tst.sh
#!/usr/bin/env bash

awk -F'[ -]' -v OFS='\t' '{print $2$3, NR, $0}' "${@:--}" |
sort -k1,1n -k2,2n |
cut -f3- |
awk -F'[ -]' '
    { curr = $2$3 }
    curr != prev {
        close(out)
        out = "file_" (++cnt) ".csv"
        prev = curr
    }
    { print > out }
'

./tst.sh file

$ head file_*
==> file_1.csv <==
id1, 2017-04-28T19:59:00, 80
id2, 2017-04-28T03:14:35, e4
id5, 2017-04-28T00:31:54, 65
id7, 2017-04-28T21:04:30, 7f

==> file_2.csv <==
id0, 2020-12-12T07:18:26, 7f
id3, 2020-12-12T23:45:09, ff
id4, 2020-12-12T09:12:34, a1
id6, 2020-12-12T20:13:47, 45

The above will work robustly, efficiently, and portably with any POSIX awk, sort, and cut and will preserve the input order in the output files.

Here's how the first 3 steps are rearranging the input file contents:

$ cat file
id0, 2020-12-12T07:18:26, 7f
id1, 2017-04-28T19:59:00, 80
id2, 2017-04-28T03:14:35, e4
id3, 2020-12-12T23:45:09, ff
id4, 2020-12-12T09:12:34, a1
id5, 2017-04-28T00:31:54, 65
id6, 2020-12-12T20:13:47, 45
id7, 2017-04-28T21:04:30, 7f

so that by the time the final awk script runs it has lines ordered by the year+month from $2 and preserving the input order for all lines with the same date+time:

$ awk -F'[ -]' -v OFS='\t' '{print $2$3, NR, $0}' file
202012  1       id0, 2020-12-12T07:18:26, 7f
201704  2       id1, 2017-04-28T19:59:00, 80
201704  3       id2, 2017-04-28T03:14:35, e4
202012  4       id3, 2020-12-12T23:45:09, ff
202012  5       id4, 2020-12-12T09:12:34, a1
201704  6       id5, 2017-04-28T00:31:54, 65
202012  7       id6, 2020-12-12T20:13:47, 45
201704  8       id7, 2017-04-28T21:04:30, 7f

$ awk -F'[ -]' -v OFS='\t' '{print $2$3, NR, $0}' file | sort -k1,1n -k2,2n
201704  2       id1, 2017-04-28T19:59:00, 80
201704  3       id2, 2017-04-28T03:14:35, e4
201704  6       id5, 2017-04-28T00:31:54, 65
201704  8       id7, 2017-04-28T21:04:30, 7f
202012  1       id0, 2020-12-12T07:18:26, 7f
202012  4       id3, 2020-12-12T23:45:09, ff
202012  5       id4, 2020-12-12T09:12:34, a1
202012  7       id6, 2020-12-12T20:13:47, 45

$ awk -F'[ -]' -v OFS='\t' '{print $2$3, NR, $0}' file | sort -k1,1n -k2,2n | cut -f3-
id1, 2017-04-28T19:59:00, 80
id2, 2017-04-28T03:14:35, e4
id5, 2017-04-28T00:31:54, 65
id7, 2017-04-28T21:04:30, 7f
id0, 2020-12-12T07:18:26, 7f
id3, 2020-12-12T23:45:09, ff
id4, 2020-12-12T09:12:34, a1
id6, 2020-12-12T20:13:47, 45

αғsнιη · Accepted Answer · 2021-01-03 17:37:29Z

0

Going the way as you are, meaning first do sort then split into different files, and also avoid using awk arrays:

<infile sort -t, -k2 \
|awk -F, '{
     substr($2,1,10)!=prev && nxt++;
     print >>("file_"nxt".csv"); close("file_"nxt".csv");
     prev=substr($2,1,10);
}'

edited Jan 3, 2021 at 17:37

answered Jan 3, 2021 at 16:53

αғsнιη

41.9k17 gold badges75 silver badges118 bronze badges

You're welcome. Also, that invocation of sort won't necessarily preserve input order for 2 lines with the same timestamp. If you have GNU sort then you could add -s (for "stable sort") to do that.

Ed Morton
– Ed Morton

2021-01-03 17:38:31 +00:00
Commented Jan 3, 2021 at 17:38

Add a comment |

Stack Exchange Network

Split CSV files based on part of a column

3 Answers 3

You must log in to answer this question.

Hot Network Questions

Split CSV files based on part of a column

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions