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
  • 3
    Please show us the original input rather than human readable ASCII art.
    – l0b0
    Jul 16, 2018 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, 2018 at 1:50
  • What have you tried so far? There are myriad examples of working with CSV on this Stack alone. Jul 16, 2018 at 13:09

4 Answers 4

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
3
  • 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. Jul 16, 2018 at 1:04
  • 3
    @vipertherapper then you should show a representative input sample so that you can get good answers.
    – Jeff Schaller
    Jul 16, 2018 at 1:10
  • @JeffSchaller I edited the original question. Jul 16, 2018 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
6
  • Now I'm getting this output: 1 Celtics,B,E,Win,2p, continuing on for the rest of the rows. Jul 16, 2018 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. Jul 16, 2018 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. Jul 16, 2018 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. Jul 16, 2018 at 1:45
  • @vipertherapper Copy the csv that you have in your question into a new file and then run my command on it. Jul 16, 2018 at 1:48

You must log in to answer this question.

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