CODEWITHSUNDEEP
rsubsetpanel-data
user3691532
user3691532

Reputation: 77

Subsetting panel data via unique values

I would like to segment panel data according to specific criterion and perform summary statistics on each segment. Data:

  store year    rev space  market
      1 2004 110000  1095     136
      1 2005 110000  1095     136
      1 2006 110000  1095     136
      1 2007 120000  1095     136
      2 2004  35000   800     136
      3 2004  45000  1000     136
      3 2005  45000  1000     136
      3 2006  45000  1000     136
      3 2007  45000  1000     136
      4 2005  17500   320     136
      4 2006  17500   320     136
      4 2007  17500   320     136
      5 2005  45000   580     191
      5 2006  45000   580     191
      5 2007  45000   580     191
      6 2004   7000   345     191
      6 2005   7000   345     191
      6 2006   7000   345     191
      7 2007  10000   500     191

From the example above I want to separate stores into entrants, exits and incumbents. So for instance I would like to find out how many stores have exited the market throughout the period, which should look like:

 store year   rev space  market
      2 2004 35000   800     136
      6 2006  7000   345     191

Have entered the market:

 store year    rev space  market
      4 2005  17500   320     136
      5 2005  45000   580     191
      7 2007  10000   500     191

And remained incumbent throughout the period:

 store year    rev  space  market
      1 2004 110000   1095     136
      1 2005 110000   1095     136
      1 2006 110000   1095     136
      1 2007 120000   1095     136     
      3 2004  45000   1000     136
      3 2005  45000   1000     136
      3 2006  45000   1000     136
      3 2007  45000   1000     136

I'm not experienced enough with R to perform such task, hence inputs would be appreciated.

Upvotes: 1

Views: 80

Answers (1)

thelatemail
thelatemail

Reputation: 93938

Looks like a good excuse to use data.table:

library(data.table)
setDT(dat)

dat[, if(!max(dat$year) %in% year) tail(.SD,1) , by=store]

#   store year   rev space market
#1:     2 2004 35000   800    136
#2:     6 2006  7000   345    191


dat[, if(!min(dat$year) %in% year) head(.SD,1) , by=store]

#   store year   rev space market
#1:     4 2005 17500   320    136
#2:     5 2005 45000   580    191
#3:     7 2007 10000   500    191

dat[, if(min(dat$year) %in% year & max(dat$year) %in% year) .SD , by=store]

#   store year    rev space market
#1:     1 2004 110000  1095    136
#2:     1 2005 110000  1095    136
#3:     1 2006 110000  1095    136
#4:     1 2007 120000  1095    136
#5:     3 2004  45000  1000    136
#6:     3 2005  45000  1000    136
#7:     3 2006  45000  1000    136
#8:     3 2007  45000  1000    136

Upvotes: 3

Related Questions