subtract previous line based on another column

Question

I'm trying to figure out how to subtract previous line based on another column using AWK. Here is the input:

site year count
040130013 1997 34
040130013 1998 55
040130019 2006 79
040130019 2007 135
040130019 2008 151
040130019 2009 172
040130019 2010 179

Here is the desired output:

site year count  app
040130013 1997 34   34
040130013 1998 55   21
040130019 2006 79   79
040130019 2007 135  56
040130019 2008 151  16
040130019 2009 172  21
040130019 2010 179  7

Basically, for the same site (column 1),subtract the previous line using column 3 and append it as the new column. Here is what I tried:

awk '($1 in arr){$4 = $3 - prev3; prev3 = $3; print;}{arr[$1]++}' infile

but it not works.

Answer 1

Answer for original question

With just minor changes to your code:

$ awk 'NR==1{$4="app"} ($1 in arr){$4 = $3 - prev3;} {prev3 = $3; arr[$1]; print}' infile
site year count app
040130013 1997 34
040130013 1998 55 21
040130019 2006 79
040130019 2007 135 56
040130019 2008 151 16
040130019 2009 172 21
040130019 2010 179 7

The key change is that prev3 = $3; arr[$1]; print is done for every line not just those lines for which ($1 in arr).

As steeldriver suggested, we don't need to save the locations in an array:

$ awk 'NR==1{$4="app"} $1==prev1{$4=$3-prev3;} {prev3=$3; prev1=$1; print}' infile
site year count app
040130013 1997 34
040130013 1998 55 21
040130019 2006 79
040130019 2007 135 56
040130019 2008 151 16
040130019 2009 172 21
040130019 2010 179 7

Answer for revised question

how about if I want to copy the third column to fourth column for the first unique site?

$ awk 'NR==1{$4="app"} {$4=$3;} $1==prev1{$4=$3-prev3;} {prev3=$3; prev1=$1; print}' infile
site year count count
040130013 1997 34 34
040130013 1998 55 21
040130019 2006 79 79
040130019 2007 135 56
040130019 2008 151 16
040130019 2009 172 21
040130019 2010 179 7