-1

Example data: 

 Sales <- data.frame(Appliance = c("Laptop", "TV", "Fridge","TV", "TV", "TV","Laptop", "Laptop", "Radio"), ID=c( "1", "1", "1", "1", "2", "2", "2", "3", "3"))
IDLocation <-data.frame(ID=1, Location="UK")

I join the two tables using a inner join:

IDLocationSales<-merge(Sales, IDLocation, by.x="ID", by.y="ID")

I then create a table to show the count of IDs from the table created from the inner join.

CountofIDs<-table(IDLocationSales$ID, dnn=("CountOfIDs"))
View(CountofIDs)

The problem is that in the CountofIDs table it includes all the IDs (1-3) when in the actual table that I created as a result of the merge (IDLocationSales), there is only ID no. 1 present. It is only the sales table that has IDs 1-3 in there. How can I create the CountofIDs table that only references the data that is actually in the merged table?

Basil
  • 747
  • 7
  • 18
  • 1
    Since `ID` is a `factor`, even though it only has the value `1` in it, all previously existing levels are preserved. You need to drop the unused levels before making the table if you don't want them to appear. – divibisan Aug 02 '18 at 17:12
  • ....or just make sure your `ID` column in both your data sets is a `numeric` variable and your code should work. – AntoniosK Aug 02 '18 at 17:15

1 Answers1

0

Your ID variable is a factor variable. The merge doesn't drop the levels of the factor. You need to do so explicitly by running the following line of code after the merge but before you call table():

IDLocationSales$ID <- droplevels(IDLocationSales$ID)
DanY
  • 5,920
  • 1
  • 13
  • 33