1

I have a big csv file (Test.csv), which looks like this:

1,2,3,A,5
1,2,3,B,5
1,2,3,E,5
1,2,3,D,5
1,2,3,Z,5
1,2,3,B,5

I want to print the lines in which the 4th column has the same content in different files. Actually, I need to join these lines that have the same content in a new csv or txt file, named as the 4th column content. For example:

Output:

File A

1,2,3,A,5
1,2,3,A,5
1,2,3,A,5

File B

1,2,3,B,5
1,2,3,B,5

Since the input file is large, I have no idea how many different patterns there are in this 4th column. Column 4 contains only words and the other columns contain words and/or numbers.

As I have no experience, I researched similar questions and even tried the following code:

awk 'NR==FNR{a[$4]=NR; next} $NF in a {print > "outfile" a[$NF]}' Test.csv

but nothing worked. Can anyone help me, please? Thanks in advance.

  • Does the order matter? – Ainar-G Aug 10 at 18:29
  • No, the order doesn't matter @Ainar-G – Amália Aug 10 at 18:31
  • How any awk will parse print > "outfile" a[$NF] is undefined by POSIX so different awks will do different things with it. For portability to all awks you need to parenthesize any expression on the right side of input of output redirection, i.e. print > ("outfile" a[$NF]) in your case. Not saying that's your only problem, but it's not helping! – Ed Morton Aug 10 at 20:09
  • 1
    Thanks @EdMorton! – Amália Aug 11 at 12:33
4

This will work efficiently using POSIX sort and any awk in any shell on every UNIX box:

$ sort -t, -k4,4 test.csv |
    awk -F, '$4!=prev{close(out); out="File"$4; prev=$4} {print > out}'

$ head -n 20 File*
==> FileA <==
1,2,3,A,5

==> FileB <==
1,2,3,B,5
1,2,3,B,5

==> FileD <==
1,2,3,D,5

==> FileE <==
1,2,3,E,5

==> FileZ <==
1,2,3,Z,5

Some things to note:

  1. some awks need putting parens around an expression on the right side of output redirection, and
  2. some awks fail if you don't close output files as they go and so trying to retain too many open files once they get past a dozen or so output files, and
  3. keeping multiple open output files is very inefficient in all awks that allow it, and
  4. closing output files line by line to account for that will be very inefficient in all awks.
| improve this answer | |
  • 1
    Thank you @EdMorton for the comments and the solution. It worked properly. – Amália Aug 11 at 12:38
3

You should be able to just use the field in the output filename. A straightforward solution:

awk -F, '{print > ("file_" $4 ".csv")}' Test.csv

This works in at least GNU awk, and creates file_A.csv, file_B.csv etc. Note that this keeps all the output files open, and gets slower the more there are, especially if you hit the per-process open files limit.

-F, sets the field separator to the comma.

I'm not sure what the script you showed should do.

| improve this answer | |
  • That would work OK in GNU awk but would fail in other awks due to 1) no parentheses on the right side of output redirection (would fail at invocation with a syntax error), and 2) keeping too many output files open simultaneously once you get past a dozen or so (would fail while running with a "too many open files" error). It also would be fairly inefficient in GNU awk if there are a lot of output files being generated due to the time gawk would spend managing those open files. – Ed Morton Aug 10 at 21:27
  • 1
    @EdMorton, ah, good point on the parens, thanks. – ilkkachu Aug 11 at 8:44
  • Thanks @ilkkachu, it worked properly – Amália Aug 11 at 12:36
2

Something like this:

$ awk -F, '{ print $0 >> "file-" $4 ".txt"; }' ./tmp.txt

Just as @ilkkachu's answer mentions, flag -F is to change the field separator from the default, whitespace characters, to a comma. You should use >> instead of > so that you don't overwrite the file if it exists.

| improve this answer | |
  • 3
    The only functional difference between your answer and @ilkkachu's is that theirs would overwrite an existing file while yours would append to it. > and >> do not mean the same in awk as they do in shell, > in awk will not overwrite the output with each line written. See my comment under their answer for additional feedback that applies to your answer too. – Ed Morton Aug 10 at 21:25
  • @Quasímodo, do you have any more information, or a source, about gawk truncating files on reopen? I tried that with around 2700 files and couldn't get it to happen (with ulimit on open files set to 1024) – ilkkachu Aug 11 at 8:18
  • Thanks @Ainar-G. It worked properly in my case – Amália Aug 11 at 12:39
  • 1
    @Quasímodo no, that won't happen. When gawk closes an output file and has to re-open it again later then it starts appending to it at that point, it won't overwrite it regardless of whether you're using > or >> - the difference between those 2 constructs is only on the first write to a file. Otherwise it wouldn't be managing the multiple open output files at all, it'd just be trashing them :-). – Ed Morton Aug 11 at 13:49
  • @ilkkachu That's true, sorry. My memory plays tricks on me sometimes. Thanks Ed too. – Quasímodo Aug 11 at 14:59
0

Python

#!/usr/bin/python
uni=[]
k=open('file.txt','r')
for i in k:
    
    g=i.split(",")[3].strip()
    if g not in uni:
        uni.append(g)



for m in uni:
    f=open("{0}.txt".format(m),'w')
    l=open('file.txt','r')
    for d in l:
        if m in d.split(",")[3].strip():
            f.write(d)

Already best solution provided in awk it justs my try

for i in `awk -F "," '{if(!seen[$4]++)print $4}' file.txt`; do awk -v i="$i" -F "," '$4==i{print $0}' file.txt >$i.txt; done
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.