1

I have a CSV file with data like this (almost 100000 lines). I need to count how many times does a specific word with a specific date appears in the file for a range of dates. For example from 15/03/2020 to 16/04/2020 I need to count how many times the word "Sinaloa" appears which means count all "Sinaloa" with 15/03/2020, 16/03/2020, 17/03/2020, ... , 16/04/2020

I tried using grep but it only counts the first and last date.

Edit: Let's take "Ciudad de Mexico" and a date such as 25/04/2020. I need to count all the "Ciudad de Mexico" from 15/03/2020 to 25/04/2020. In this case the desire output is 5. But the problem is that the final date in this case is an input from the user, so it if changes to 01/05/2020 the output should be 6.

"167386","Baja California","F",54,"01/04/2020","confirmado"
"0d01b5","Sinaloa","F",60,"13/04/2020","confirmado"
"1beec8","Ciudad de México","M",47,"16/04/2020","confirmado"
"15fcd7","Ciudad de México","M",46,"16/04/2020","confirmado"
"0a5675","Sinaloa","F",34,"19/05/2020","confirmado"
"0e9e95","Ciudad de México","F",31,"25/04/2020","confirmado"
"07fa63","Ciudad de México","M",37,"01/05/2020","confirmado"
"0693ef","Ciudad de México","F",48,"20/03/2020","confirmado"
"19afc8","Baja California","F",45,"06/04/2020","confirmado"
"093740","Baja California","M",81,"19/04/2020","confirmado"
"1b3c74","México","M",57,"16/04/2020","confirmado"
"025cb1","Baja California","M",51,"29/04/2020","confirmado"
"15764f","México","M",73,"05/05/2020","confirmado"
"07c084","Tabasco","F",52,"23/04/2020","confirmado"
"1b9e29","Ciudad de México","F",47,"11/04/2020","confirmado"
1
0

There's a nice specific CSV parser, more user friendly than to parse real CSV, csvkit

You have 0 match:

 Input file

Added needed headers:

"c1",    "c2",             "c3","c4", "c5",     "c6"
"167386","Baja California","F",54,"01/04/2020","confirmado"
"0d01b5","Sinaloa","F",60,"13/04/2020","confirmado"
"1beec8","Ciudad de México","M",47,"16/04/2020","confirmado"
"15fcd7","Ciudad de México","M",46,"16/04/2020","confirmado"
"0a5675","Sinaloa","F",34,"19/05/2020","confirmado"
"0e9e95","Ciudad de México","F",31,"25/04/2020","confirmado"
"07fa63","Ciudad de México","M",37,"01/05/2020","confirmado"
"0693ef","Ciudad de México","F",48,"20/03/2020","confirmado"
"19afc8","Baja California","F",45,"06/04/2020","confirmado"
"093740","Baja California","M",81,"19/04/2020","confirmado"
"1b3c74","México","M",57,"16/04/2020","confirmado"
"025cb1","Baja California","M",51,"29/04/2020","confirmado"
"15764f","México","M",73,"05/05/2020","confirmado"
"07c084","Tabasco","F",52,"23/04/2020","confirmado"
"1b9e29","Ciudad de México","F",47,"11/04/2020","confirmado"

Command:

$ csvgrep -c2 -r '^Sinaloa$' file | grep -c '1[567]/04/2020'

 Output

0
3
  • Sorry I did not explain myself properly. What I am doing is asking the user for a state and a date for example for the state the user types "Sinaloa" an for the date the user typer 15/06/2020. My initial date is 15/03/2020.So I need to count all the "Sinaloa" s all the Sinaloa 15/03/2020 Sinaloa 16/03/2020 Sinaloa 17/03/2020 Sinaloa 18/03/2020 . . . Sinaloa 31/03/2020 Sinaloa 01/04/2020 Until the last one that is Sinaloa 16/06/2020 – Mario Jun 10 '20 at 2:40
  • Thank you in advandce c: – Mario Jun 10 '20 at 2:40
  • This is unclear for me, please add expected output in your original post – Gilles Quenot Jun 10 '20 at 2:45
0

You can iterate over dates and grep the pattern

d="2020-03-15"
until [[ "$d" == "2020-04-26" ]]; do 
    pattern=$(date -d "$d" +%d/%m/%Y)
    d=$(date -I -d "$d + 1 day")
    grep "Ciudad de México.*$pattern" file.csv
done

output

"0693ef","Ciudad de México","F",48,"20/03/2020","confirmado"
"1b9e29","Ciudad de México","F",47,"11/04/2020","confirmado"
"1beec8","Ciudad de México","M",47,"16/04/2020","confirmado"
"15fcd7","Ciudad de México","M",46,"16/04/2020","confirmado"
"0e9e95","Ciudad de México","F",31,"25/04/2020","confirmado"
3
  • Thank you this has helped me a lot but when I try to run grep -c It doesn't return the number of lines. Any suggestion? – Mario Jun 10 '20 at 4:40
  • because in each iteration grep searches for one date, if you need the count of lines, you should sum them. – binarysta Jun 10 '20 at 4:44
  • Oh! Thanks!!!!! – Mario Jun 10 '20 at 4:59
0

Loading the variables to awk

awk -F\" -v city="Ciudad de México" -v from="01/04/2020" -v to="16/04/2020" '
  BEGIN{split(to,t,"/"); split(from,f,"/"); 
    to=mktime(t[3]" "t[2]" "t[1]" 0 0 0"); from=mktime(f[3]" "f[2]" "f[1]" 0 0 0")}
  $4~city{split($8,th,"/"); this=mktime(th[3]" "th[2]" "th[1]" 0 0 0");
    c+=(this<=to&&this>=from)}END{print city, c}' file

Ciudad de México 3

Walkthrough

Split on " and load the criteria

awk -F\" -v city="Ciudad de México" -v from="01/04/2020" -v to="16/04/2020" '

Grab the date elements from your datespec variables

  BEGIN{split(to,t,"/"); split(from,f,"/"); 

Convert them to timestamps

    to=mktime(t[3]" "t[2]" "t[1]" 0 0 0"); from=mktime(f[3]" "f[2]" "f[1]" 0 0 0")}

Loop over the main file checking for the city in $4 and grab the timestamp from $8 if there is a match

  $4~city{split($8,th,"/"); this=mktime(th[3]" "th[2]" "th[1]" 0 0 0");

Increment the counter c if the timestamp is in range

    c+=(this<=to&&this>=from)}

....

  END{print city, c}' file
-1

There is a solution with awk:

$ fyear=2020 fmonth=03 fday=15  tyear=2020 tmonth=04 tday=25  # shell vars
$ fr="$fyear $fmonth $fday 00 00 00"
$ to="$tyear $tmonth $tday 00 00 00"

$ awk -F '"?[,]"?' -v fr="$fr" -v to="$to" '
     BEGIN{
               c=0;
               from=mktime(fr);
               upto=mktime(to)
          };
          $2~/Ciudad de México/{
               split($5,t,"/");
               now=mktime(t[3]" "t[2]" "t[1]" 00 00 00");
               if(from<=now&&now<=upto){c++;print}
          }END{
          print c
     }' file

5

That would imply running a separate awk scan of the data for each starting or ending date.

But should be pretty fast.

6
  • Thank you very much! – Mario Jun 11 '20 at 17:39
  • When you have enough reputation you can upvote the answers that you like. But since the question is yours, even now, if any answer seems to be the best for you, you can "accept" ("select" with the green ✓ checkmark) it. @Mario – ImHere Jun 11 '20 at 18:22
  • One question I try to replace upto=mktime("2020 04 25 00 00 00") for upto=mktime("$year $month $day 00 00 00") which are inputs from the user but when I try to run it my output is zero. I tried using "$year" "$month" but is the same problem – Mario Jun 12 '20 at 21:59
  • An awk command usually start with a single quote. Inside single quotes, shell vars are not expanded. Try echo '"$year"'. I'll update this answer to cover your issue. Just a moment. ... – ImHere Jun 12 '20 at 22:09
  • Yes I just saw my error. Thank you – Mario Jun 12 '20 at 22:10

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.