2

I have a CSV with columns that look like the following:

Team    Other Data  More Data   Result  Time
Knicks      A          F         Loss    2p
Celtics     B          E         Win     2p
Lakers      C          D         Loss    3p
Lakers      D          C         Loss    4p
Knicks      E          B         Win     4p
Lakers      F          A         Win     5p

How can I read the CSV and output the wins and losses of each team?

For example, my desired output would be the following:

1 Loss Knicks
1 Win Knicks
1 Win Celtics
2 Loss Lakers
1 Win Lakers

Right now, I have this code:

#!/bin/bash
while IFS=, read -r team result
do
  echo $team, $result
done < teams.csv

Which produces the following output:

Team, Result   
Knicks, Loss
Celtics, Win
Lakers, Loss
Lakers, Loss
Knicks, Win

How can I count and store the number of occurrences of each result for every team? Ideally I'd like this data sorted by team.

  • 3
    Please show us the original input rather than human readable ASCII art. – l0b0 Jul 16 '18 at 0:51
  • 2
    The first example ... "that it looks like" is not useful to generate good answers. Your script indicates that it's comma-separated. Show the actual (representative) input. – Jeff Schaller Jul 16 '18 at 1:50
  • What have you tried so far? There are myriad examples of working with CSV on this Stack alone. – roaima Jul 16 '18 at 13:09
4

Using arrays in awk

If the fields of the input file are separated by one or more whitespace characters, then you don't have to declare the field separator:

awk 'NR>1 && NF { league[$1][$4]++ } END { for ( team in league ) for ( results in league[team] ) print league[team][results],results,team }' teams.txt

The same code, formatted for the screen:

awk 'NR>1 && NF { league[$1][$4]++ }
     END { for ( team in league )
           for ( results in league[team] )
           print league[team][results],results,team }' teams.txt

Here, league[$1][$4]++ counts the numbers ($4, the fourth field) of wins and losses for each team ($1, the first field) in the league (the input file).

NR>1 means that awk will ignore the header (the first line).

Similarly, NF (shorthand for NF>0) means that awk will only examine lines containing at least one field. In other words, NF skips blank lines.

The NR>1 && NF section examines the input file and creates the array. When that is finished, the END section prints the array.

If the fields of the input file are comma-separated, then add BEGIN { FS="," ; OFS=" " } to set the input (FS) and output (OFS) field separators:

awk 'BEGIN { FS="," ; OFS=" " } NR>1 && NF { league[$1][$4]++ } END { for ( team in league ) for ( results in league[team] ) print league[team][results],results,team }' teams.csv

The same code, formatted for the screen:

awk 'BEGIN { FS="," ; OFS=" " }
         NR>1 && NF { league[$1][$4]++ }
         END { for ( team in league )
               for ( results in league[team] )
               print league[team][results],results,team }' teams.csv

Output:

1 Win Knicks
1 Loss Knicks
1 Win Lakers
2 Loss Lakers
1 Win Celtics

Add | sort -t " " -k 3 -k 2,2 to the end of that code, to sort by team and then by results per team.

Sorted output:

1 Win Celtics
1 Loss Knicks
1 Win Knicks
2 Loss Lakers
1 Win Lakers
2

All you need to do is sort the file and pass it to uniq -c to count unique occurrences:

sort teams.csv | uniq -c

That should produce output like this:

      1 Celtics,Win
      1 Knicks,Loss
      1 Knicks,Win
      2 Lakers,Loss
  • That is nice for getting the output, but in my original question, I said that I wanted to count and store the number of occurrences. If my input file happened to contain other information and I only wanted to read/extract information from the Team and Result column, that solution would not be viable. – dougdimmadome Jul 16 '18 at 1:04
  • 3
    @vipertherapper then you should show a representative input sample so that you can get good answers. – Jeff Schaller Jul 16 '18 at 1:10
  • @JeffSchaller I edited the original question. – dougdimmadome Jul 16 '18 at 1:15
2

With GNU datamash (and awk to rearrange the columns, if that matters to you):

$ datamash -W --header-in groupby 1,4 count 4 < teams.csv | awk '{print $3, $2, $1}'
1 Loss Knicks
1 Win Celtics
2 Loss Lakers
1 Win Knicks
1 Win Lakers

Note that because we don't ask datamash to sort, this only groups results that are already adjacent.

If your data are really comma separated then replace -W by -t,

1

You can just use sort and then pipe it into uniq -c but that will include Team, Result.

To avoid that:

$ awk 'NR>1 {print $4,$1}' team.csv | sort -k2 | uniq -c

To explain each section:

  • awk 'NR>1 {print $4,$1}' - Prints all the lines greater than the first with the result followed by the team as specified with columns 4 and 1 for the team and the result.
  • sort -k2 - Sorts by the team as it becomes the second column after awk operates.
  • uniq -c - Counts the number of unique occurrences

The output sorted by team:

1 Win Celtics
1 Loss Knicks
1 Win Knicks
2 Loss Lakers
1 Win Lakers
  • Now I'm getting this output: 1 Celtics,B,E,Win,2p, continuing on for the rest of the rows. – dougdimmadome Jul 16 '18 at 1:33
  • @vipertherapper I copied your csv file in exactly the way that you have it and used the command in my answer. If it looks like what you've provided and you're using what I have then you should get the same result. – Nasir Riley Jul 16 '18 at 1:35
  • @vipertherapper Are you using the exact command in my answer? The awk part will only print columsn 4 and 1 which are the team and the result. It won't print anything from the other columns. – Nasir Riley Jul 16 '18 at 1:44
  • I am using the exact command, yes. I'm trying to figure out why we're getting different outputs. I even tried recreating my CSV to no avail. – dougdimmadome Jul 16 '18 at 1:45
  • @vipertherapper Copy the csv that you have in your question into a new file and then run my command on it. – Nasir Riley Jul 16 '18 at 1:48

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.