1

I have large csv file and want to build simple ranking:

$ cat file.csv
2022-12-01     RED     1     
2022-12-01     RED     1     
2022-12-01     RED     2     
2022-12-01     RED     2     
2022-12-01     RED     2     
2022-12-01     YELLOW     1     
2022-12-01     YELLOW     1     
2022-12-01     YELLOW     2     
2022-12-01     YELLOW     2     
2022-12-01     YELLOW     2     
$ sort file.csv | uniq -c | sort -nr > file_sort.csv
$ cat file_sort.csv 
3 2022-12-01     RED     2     
3 2022-12-01     YELLOW     2     
2 2022-12-01     RED     1     
2 2022-12-01     YELLOW     1

I want a result to be added as new column but instead its being added for existing $1 as below:

$ cut -f1 file_sort.csv
3 2022-12-01
3 2022-12-01
2 2022-12-01
2 2022-12-01

Is there any way to save the value of uniq lines as new column? Like this:

$ cut -f1 file_sort.csv
3
3
2
2
Improve this question
5
  • 1
    Do you want it as a new column added on every line, or do you just want the value and nothing else, the way you show? Commented Jan 23, 2023 at 14:00
  • @terdon i want it as new column added on every line Commented Jan 23, 2023 at 14:05
  • 1
    OK, then please edit your question and show us the actual output you want because you are showing something completely different (just one column of output). Commented Jan 23, 2023 at 14:05
  • em, I am not? The output I show to you is what I want after using cut. So yes, I want unique value of each line as new column in file_sort.csv file. But after using cut I want it to be a separeted column with values. Commented Jan 23, 2023 at 14:10
  • 2
    Well, you can get that exact output if there is nothing but a single column in the file. And if that's all you want, why have the rest? Presumably, you want i) all the fields in the file and ii) you want the number of occurrences as the 1st field. It would just be easier and clearer to make that explicit. Commented Jan 23, 2023 at 14:13

4 Answers 4

Reset to default
2

It sounds like all you want to do is simply move the number from the first field to the last. You don't specify this, but given the cut command you show, that should mean that your file is tab-delimited. If so, given that the uniq -c output will pad the line with spaces (which you also haven't shown), and will not add a tab after the number of occurrences, you could do something as simple as:

$ sort file.csv | uniq -c | sort -nr | perl -pe 's/^\s+(\d+) (.*)/$2\t$1/' 
2022-12-01  YELLOW  2   3
2022-12-01  RED 2   3
2022-12-01  YELLOW  1   2
2022-12-01  RED 1   2

Or, in a sed that supports -E:

$ sort file.csv | uniq -c | sort -nr | sed -E 's/^ *([0-9]*) (.*)/\2\t\1/' 
2022-12-01  YELLOW  2   3
2022-12-01  RED 2   3
2022-12-01  YELLOW  1   2
2022-12-01  RED 1   2

And, in any sed:

$ sort file.csv | uniq -c | sort -nr | sed 's/^ *\([0-9]*\) \(.*\)/\2\t\1/' 
2022-12-01  YELLOW  2   3
2022-12-01  RED 2   3
2022-12-01  YELLOW  1   2
2022-12-01  RED 1   2

Alternatively, you could do the counting in awk instead, add the field where you want it and the sort on the 4th field:

$ awk -F'\t' -v OFS='\t' '{ cnt[$0]++ } 
                          END{
                            for(line in cnt){
                              print line,cnt[line]
                            }
                          }' file.csv | sort -nrk4,4 
2022-12-01  YELLOW  2   3
2022-12-01  RED 2   3
2022-12-01  YELLOW  1   2
2022-12-01  RED 1   2
Improve this answer
0
1

Using Raku (formerly known as Perl_6)

What you want to do is Bag elements, in this case the elements are lines:

~$ raku -e '.say for lines.Bag;'  file

Sample Input (tab-separated):

2022-12-01     RED     1     
2022-12-01     RED     1     
2022-12-01     RED     2     
2022-12-01     RED     2     
2022-12-01     RED     2     
2022-12-01     YELLOW     1     
2022-12-01     YELLOW     1     
2022-12-01     YELLOW     2     
2022-12-01     YELLOW     2     
2022-12-01     YELLOW     2

Sample Output:

2022-12-01    RED    2        3
2022-12-01    RED    1        2
2022-12-01    YELLOW    2        3
2022-12-01    YELLOW    1        2

Note: a real problem here could be trailing-whitespace. You can add a trim-trailing call to get rid of whitespace at the right end of each line:

~$ raku -e '.put for lines.map(*.trim-trailing).Bag;' 

#OR

~$ raku -e '.put for lines>>.trim-trailing.Bag;'

https://docs.raku.org/type/Bag
https://raku.org

Improve this answer
1

Using the count-distinct sub-command of Miller (mlr) to count the number of records that have distinct values in the three first fields, assuming the input is tab-delimited and without column headers:

$ mlr --tsv -N count-distinct -f 1,2,3 file
2022-12-01      RED     1       2
2022-12-01      RED     2       3
2022-12-01      YELLOW  1       2
2022-12-01      YELLOW  2       3

The counts are added as a new field at the end of the list of fields. The input does not need to be sorted.

If you want the counts as the first field, then use the reorder sub-command. Note that we may refer to the named field count which was created by the count-distinct operation, even though the header isn't showing in the output:

$ mlr --tsv -N count-distinct -f 1,2,3 then reorder -f count file
2       2022-12-01      RED     1
3       2022-12-01      RED     2
2       2022-12-01      YELLOW  1
3       2022-12-01      YELLOW  2
Improve this answer
0

Counting unique lines is easily done with associative arrays idea of awk command:

$ awk  '{a[$0]++} END {for (i in a) print a[i]"--->",i}' infile

2---> 2022-12-01     YELLOW     2     
3---> 2022-12-01     RED     2     
2---> 2022-12-01     YELLOW     1     
1---> 2022-12-01     YELLOW     2
2---> 2022-12-01     RED     1
Improve this answer
1
  • You may have some trailing whitespace in your test data making 2022-12-01 YELLOW 2 show up twice. Commented Jan 24, 2023 at 9:22

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.