For loop for selecting certain data to form a new data frame

Question

First of all, I am using the ukpolice library in R and extracted data to a new data frame called crimes. Now i am running into a new problem, i am trying to extract certain data to a new empty data frame called df.shoplifting if the category of the crime is equal to "shoplifiting" it needs to add the id, month and street name to the new dataframe. I need to use a loop and if statement togheter.

EDIT: Currently i have this working but it lacks the IF statemtent:

for (i in crimes$category) {
 shoplifting <- subset(crimes, category == "shoplifting", select = c(id, month, street_name))
 names(shoplifting) <- c("ID", "Month", "Street_Name")
}

What i am trying to do:

for (i in crimes$category) {
if(crimes$category == "shoplifting"){
data1 <- subset(crimes, category == i, select = c(id, month, street_name))
  }
}

It does run and create the new data frame data1. But the data that it extracts is wrong and does not only include items with the shoplifting category..

Answer 1

Try:

df.shoplifting <- crimes[which(crimes$category == 'shoplifting'),]

Using a for loop in this instance will work, but when working in R you want to stick to vectorized operations if you can.

This operation subsets the crimes dataframe and selects rows where the category column is equal to shoplifting. It is not necessary to convert the category column into a factor - you can match the string with the == operator.

Note the comma at the end of the which(...) function, inside of the square brackets. The which function returns indices (row numbers) that meet the criteria. The comma after the function tells R that you want all of the rows. If you wanted to select only a few rows you could do:

df.shoplifting <- crimes[which(crimes$category == 'shoplifting'),c("id","Month","Street_Name")]

OR you could call the columns based on their number (I don't have your data so I don't know the numbers...but if the columns id, Month, Street_Name, you could use 1, 2, 3).

df.shoplifting <- crimes[which(crimes$category == 'shoplifting'),c(1,2,3)]

Answer 2

I'll guess, and update if needed based on your question edits.

1. rbind works only on data.frame and matrix objects, not on vectors. If you want to extend a vector (N.B., that is not part of a frame or column/row of a matrix), you can merely extend it with c(somevec, newvals) ... but I think that this is not what you want here.

2. You are iterating through each value of crimes$category, but if one category matches, then you are appending all data within crimes. I suspect you mean to subset crimes when adding. We'll address this in the next bullet.

3. One cannot extend a single column of a multi-column frame in the absence of the others. A data.frame as a restriction that all columns must always have the same length, and extending one column defeats that. (And doing all columns immediately-sequentially does not satisfy that restriction.)

   One way to work around this is to rbind a just-created data.frame:

   # i = "shoplifting"
   newframe <- subset(crimes, category == i, select = c(id, month, street_name))
   names(newframe) <- c("ID", "Month", "Street_Name") # match df.shoplifting names
   df.shoplifting <- rbind(df.shoplifting, newframe)
   

4. I don't have the data, but if crimes$category ever has repeats, you will re-add all of the same-category rows to df.shoplifting. This might be a problem with my assumptions, but is likely not what you really need.

   If you really just need to do it once for a category, then do this without the need for a for loop:

   df.shoplifting <- subset(crimes, category == "shoplifting", select = c(id, month, street_name))
   # optional
   names(df.shoplifting) <- c("ID", "Month", "Street_Name")
   

5. Iteratively adding rows to a frame is a bad idea: while it works okay for smaller datasets, as your data scales, the performance worsens. Why? Because each time you add rows to a data.frame, the entire frame is copied into a new object. It's generally better to form a list of frames and then concatenate them all later (c.f., https://stackoverflow.com/a/24376207/3358227).

   On this note, if you need one frame per category, you can get that simply with:

   df_split(df, df$category)
   

   and then operate on each category as its own frame by working on a specific element within the df_split named list (e.g., df_split[["shoplifting"]]).

6. And lastly, depending on the analysis you're doing, it might still make sense to keep it all together. Both the dplyr and data.table dialects of R making doing calculations on data within groups very intuitive and efficient.