title: creating a new variable using dplyr in r
body: <p>I want to create a new variable using dplyr in R </p>
  
  <p>Here is the data - </p>
  
  <pre><code>    year  TICKER   auditor_fkey  pauditor_fkey  AUDTURNOVER
  1  2001    AIR            4            NA          NA
  2  2002    AIR            4             4           0
  3  2003    AIR            4             4           0
  4  2004    AIR            4             4           0
  5  2005    AIR            4             4           0
  6  2006    AIR            4             4           0
  7  2007    AIR            4             4           0
  8  2008    AIR            4             4           0
  9  2009    AIR            4             4           0
  10 2010    AIR            4             4           0
  11 2011    AIR            4             4           0
  12 2012    AIR            4             4           0
  13 2013    AIR            4             4           0
  14 2014    AIR            4             4           0
  15 2015    AIR            4             4           0
  16 2016    AIR            4             4           0
  17 2017    AIR            4             4           0
  18 2000    ABT            5            NA          NA
  19 2001    ABT            5             5           0
  20 2002    ABT            3             5           1
  21 2003    ABT            3             3           0
  22 2004    ABT            3             3           0
  23 2005    ABT            3             3           0
  24 2006    ABT            3             3           0
  25 2007    ABT            3             3           0
  26 2008    ABT            3             3           0
  27 2009    ABT            3             3           0
  28 2010    ABT            3             3           0
  29 2011    ABT            3             3           0
  30 2012    ABT            3             3           0
  31 2013    ABT            3             3           0
  32 2014    ABT            2             3           1
  33 2015    ABT            2             2           0
  34 2016    ABT            2             2           0
  35 2017    ABT            2             2           0
  36 2007   WDDD          598            NA           0
  37 2008   WDDD          598           598           0
  38 2009   WDDD          598           598           0
  39 2010   WDDD          598           598           0
  40 2011   WDDD          598           598           0
  41 2012   WDDD          598           598           0
  42 2013   WDDD          598           598           0
  43 2014   WDDD          598           598           0
  44 2015   WDDD        27105           598           1
  45 2016   WDDD        27105         27105           0
  46 2017   WDDD        27105         27105           0
  47 2001   ACET            4            NA          NA
  48 2002   ACET            4             4           0
  49 2003   ACET            4             4           0
  50 2004   ACET            4             4           0
  51 2005   ACET            4             4           0
  52 2006   ACET            7             4           1
  53 2007   ACET            7             7           0
  54 2008   ACET            7             7           0
  55 2009   ACET            7             7           0
  56 2010   ACET        11761             7           1
  57 2011   ACET        11761         11761           0
  58 2012   ACET        11761         11761           0
  59 2013   ACET        11761         11761           0
  60 2014   ACET        11761         11761           0
  61 2015   ACET        11761         11761           0
  62 2016   ACET        11761         11761           0
  63 2017   ACET        11761         11761           0
  64 2000    ACU            2            NA          NA
  65 2001    ACU            2             2           0
  66 2002    ACU            2             2           0
  67 2003    ACU            2             2           0
  68 2004    ACU            2             2           0
  69 2005    ACU            2             2           0
  70 2006    ACU            2             2           0
  71 2007    ACU            2             2           0
  72 2008    ACU         1687             2           1
  73 2008    ACU            2          1687           1
  74 2009    ACU         1687             2           1
  75 2010    ACU         8256          1687           1
  76 2011    ACU         8256          8256           0
  77 2012    ACU         8256          8256           0
  78 2013    ACU         8256          8256           0
  79 2014    ACU         8256          8256           0
  80 2015    ACU         8256          8256           0
  81 2016    ACU         8256          8256           0
  82 2017    ACU         8256          8256           0
  </code></pre>
  
  <p>I created the "pauditor_fkey" variable, using the following code</p>
  
  <pre><code>my_data &lt;- my_data%&gt;%
    group_by(TICKER) %&gt;% 
    mutate(pauditor_fkey = lag (auditor_fkey))
  </code></pre>
  
  <p>Here,
   year = a year;
   TICKER = identifier of a company;
   auditor_fkey = identified of the auditor who audited the company in a year (e.g., auditor "4"  audited company "AIR" in "2001);
   pauditor_fkey = the auditor in the previous year;
   AUDTURNOVER = if in a particular year, a auditor changes I code them 1, otherwise 0.</p>
  
  <p>Now, I want to create a new variable called <strong>AUDITOR_TENURE</strong>, using the above variables</p>
  
  <p>After running the code, the data should look like this - </p>
  
  <pre><code>   year  TICKER   auditor_fkey   pauditor_fkey  AUDTURNOVER  AUDITOR_TENURE
  1  2001    AIR            4            NA          NA              1
  2  2002    AIR            4             4           0              2
  3  2003    AIR            4             4           0              3
  4  2004    AIR            4             4           0              4
  5  2005    AIR            4             4           0              5
  6  2006    AIR            4             4           0              6
  7  2007    AIR            4             4           0              7
  8  2008    AIR            4             4           0              8
  9  2009    AIR            4             4           0              9
  10 2010    AIR            4             4           0             10
  11 2011    AIR            4             4           0             11
  12 2012    AIR            4             4           0             12
  13 2013    AIR            4             4           0             13
  14 2014    AIR            4             4           0             14
  15 2015    AIR            4             4           0             15
  16 2016    AIR            4             4           0             16
  17 2017    AIR            4             4           0             17
  18 2000    ABT            5            NA          NA              1
  19 2001    ABT            5             5           0              2
  20 2002    ABT            3             5           1              1
  21 2003    ABT            3             3           0              2
  22 2004    ABT            3             3           0              3
  23 2005    ABT            3             3           0              4
  24 2006    ABT            3             3           0              5
  25 2007    ABT            3             3           0              6
  26 2008    ABT            3             3           0              7
  27 2009    ABT            3             3           0              8
  28 2010    ABT            3             3           0              9
  29 2011    ABT            3             3           0             10
  30 2012    ABT            3             3           0             11
  31 2013    ABT            3             3           0             12
  32 2014    ABT            2             3           1              1
  33 2015    ABT            2             2           0              2
  34 2016    ABT            2             2           0              3
  35 2017    ABT            2             2           0              4
  36 2007   WDDD          598            NA           0              1
  37 2008   WDDD          598           598           0              2
  38 2009   WDDD          598           598           0              3
  39 2010   WDDD          598           598           0              4
  40 2011   WDDD          598           598           0              5
  41 2012   WDDD          598           598           0              6
  42 2013   WDDD          598           598           0              7
  43 2014   WDDD          598           598           0              8
  44 2015   WDDD        27105           598           1              1
  45 2016   WDDD        27105         27105           0              2
  46 2017   WDDD        27105         27105           0              3
  47 2001   ACET            4            NA          NA              1
  48 2002   ACET            4             4           0              2
  49 2003   ACET            4             4           0              3
  50 2004   ACET            4             4           0              4
  51 2005   ACET            4             4           0              5
  52 2006   ACET            7             4           1              1
  53 2007   ACET            7             7           0              2
  54 2008   ACET            7             7           0              3
  55 2009   ACET            7             7           0              4
  56 2010   ACET        11761             7           1              1
  57 2011   ACET        11761         11761           0              2
  58 2012   ACET        11761         11761           0              3
  59 2013   ACET        11761         11761           0              4
  60 2014   ACET        11761         11761           0              5
  61 2015   ACET        11761         11761           0              6
  62 2016   ACET        11761         11761           0              7
  63 2017   ACET        11761         11761           0              8
  64 2000    ACU            2            NA          NA              1
  65 2001    ACU            2             2           0              2
  66 2002    ACU            2             2           0              3
  67 2003    ACU            2             2           0              4
  68 2004    ACU            2             2           0              5
  69 2005    ACU            2             2           0              6
  70 2006    ACU            2             2           0              7
  71 2007    ACU            2             2           0              8
  72 2008    ACU         1687             2           1              1
  73 2008    ACU            2          1687           1              1
  74 2009    ACU         1687             2           1              1
  75 2010    ACU         8256          1687           1              1
  76 2011    ACU         8256          8256           0              2
  77 2012    ACU         8256          8256           0              3
  78 2013    ACU         8256          8256           0              4
  79 2014    ACU         8256          8256           0              5
  80 2015    ACU         8256          8256           0              6
  81 2016    ACU         8256          8256           0              7
  82 2017    ACU         8256          8256           0              8
  </code></pre>
  
  <p>if the <strong>AUDITOR_TENURE</strong>  variable is inspected, it is seen that 
  for TICKER == AIR, there was no change in the auditor; so, the <strong>AUDITOR_TENURE</strong>
  increases. For TICKER == ABT, it is seen that for years 2000 and 2001, there was no change of the auditor; so the <strong>AUDITOR_TENURE</strong> is 1 and 2 respectively for those years for that auditor. However, in 2002, there was a change in auditor for ABT and that auditor continues to work until 2013 and therefore for them new number is generated for <strong>AUDITOR_TENURE</strong> variable. Then in 2015, they change auditor again and the auditor continues work until 2017 and their tenure is calculated accordingly. </p>
  
  <p>I am actually looking for R code for this problem. </p>
  
  <p>I appreciate your help. </p>