Grouping and checking for a condition in R

Question

I have two tables: restaurant_trans and restaurant_master

restaurant_trans has name, date, net_sales

This is a transaction file with sales for 50 restaurant recorded for 30 days each (1500 obs).

restaurant_master has name, go.live.date, franchise

This is a master file with name of the restaurant and 'go.live.date' is the date a particular device was installed in the restaurant.

I want to find the net sales of the restaurant before and after the device was installed. I first want the data to be grouped.

I tried this code for subsetting the data

dummayvar = 0;

for (i in 1:nrow(restaurant_master)){
  for (j in 1:nrow(restaurant_trans)){
    if(restaurant_trans$Restaurant.Name[j]==restaurant_master$Restaurant.Name[i]){
      if(restaurant_trans$Date[j] < restaurant_master$Go.Live.Date[i]){
      append(dummayvar, restaurant_trans$Date)
      }
    }
  }
}

This is giving an error :

"level sets of factors are different"

Please help!!

Set `i` and `j` to some values; test each condition to see if it runs... at least then you'll know what line is giving the error. Probably it's your Restaurant.Name line. If so, just quit using factors and you're golden. — Frank, Nov 24 '15 at 17:46
Like Frank suggested, the variables were factors and hence the error. I converted them to characters. Loop seems to be running now but it's taking a lot of time to load. Any other better alternatives? — user3252148, Nov 24 '15 at 17:53
Frank, it's better to start using RStudio step-by-step debugging )) — Dmitriy, Nov 24 '15 at 17:53
@Dmitriy Yes, maybe so. I don't use RStudio, but also don't find that I need to debug very often. By the way @ before a name will ping a person so they see your chat message. — Frank, Nov 24 '15 at 19:28
[How to make a great R reproducible example?](http://stackoverflow.com/questions/5963269) and [How to join (merge) data frames (inner, outer, left, right)?](http://stackoverflow.com/questions/1299871) — zx8754, Nov 24 '15 at 21:26

score 0 · Answer 1 · answered Nov 24 '15 at 21:18

Consider a merge() instead of nested for loops. Simply merge restaurant netsales and master data frames by name and then subset data frames according to net sales' dates and master's go.live.dates. Finally, aggregate net sales by restaurant name and franchise or individually.

# DATA FRAME EXAMPLES
netsales <- data.frame(name=c('A', 'A', 'A', 'A', 'A',
                              'B', 'B', 'B', 'B', 'B',
                              'C', 'C', 'C', 'C', 'C'),
              date=c('6/1/2015', '6/15/2015', '7/1/2015', '9/1/2015', '11/15/2015', 
                     '6/5/2015', '6/20/2015', '7/15/2015', '8/1/2015', '10/15/2015',
                     '6/10/2015', '7/10/2015', '8/15/2015', '9/20/2015', '9/30/2015'),
              net_sales=c(1500,  600,  1200,  850,  750,  
                          1120,  560,  720,  340,  890,  
                          1150,  410,  300,  250,  900))    
netsales$date <- as.Date(strptime(netsales$date, '%m/%d/%Y'))
str(netsales)    

master <- data.frame(name=c('A', 'B', 'C'),
                     go.live.date=c('7/25/2015', '8/1/2015', '7/1/2015'),
                     franchise=c('R Co.', 'Python, Inc.', 'C# Ltd.'))

master$go.live.date <- as.Date(strptime(master$go.live.date, '%m/%d/%Y'))
str(master)    

# MERGE AND AGGREGATE BEFORE GO LIVE SALES
beforelive <- merge(netsales, master, by='name')
beforelive <- beforelive[beforelive$date < beforelive$go.live.date,]

beforelivesales <- aggregate(net_sales ~ name + franchise, beforelive, FUN=sum)

# MERGE AND AGGREGATE AFTER GO LIVE SALES
afterlive <- merge(netsales, master, by='name')
afterlive <- afterlive[afterlive$date >= afterlive$go.live.date,]

afterlivesales <- aggregate(net_sales ~ name + franchise, afterlive, FUN=sum)

Grouping and checking for a condition in R

1 Answers1