jeudi 21 janvier 2021

Extract ID for first 10 values matching based on other variable

I have created a dataset with UK crime data based on coordinates. I have variables including ID, street_name, outcome_status_category etc. I need to find the first 10 IDs, for which the outcome status was “Investigation complete; no suspect identified”. I must utilize a WHILE-loop and an IF-statement. Further, the output should be a regular numeric or integer vector (not a list)that contains exactly 10 IDs.

This is what I had so far:

crimes[is.na(x = crimes)] <- "0"
j <- 1
while (j < 11) {
  for (i in 1:length(crimes)) {
    if ((crimes$outcome_status_category[i] == "Investigation complete; no suspect identified")) {
      first10(i) = id[i]
      j = j+1
      print(first10)
    }
    }
  }

The first line is to replace the NA values so the IF function can return a TRUE/FALSE. Right now I cannot even evaluate if it works because it takes so long to execute, so I figure there must be a better way. I am very beginner so I am struggling to come up with solutions using WHILE and IF.This is what the data looks like, there are over 5000 rows

Here is the first 50 entries:

structure(list(category = c("anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour", 
"anti-social-behaviour", "anti-social-behaviour", "anti-social-behaviour"
), id = c(80393439L, 80392445L, 80391180L, 80392457L, 80391199L, 
80392467L, 80392924L, 80392589L, 80392601L, 80392644L, 80392666L, 
80392806L, 80391218L, 80391341L, 80390827L, 80392808L, 80392813L, 
80393063L, 80391347L, 80393067L, 80393082L, 80393096L, 80392847L, 
80392850L, 80392898L, 80392911L, 80393013L, 80393033L, 80393035L, 
80393344L, 80393292L, 80393294L, 80390885L, 80390890L, 80390839L, 
80391227L, 80390704L, 80391239L, 80390911L, 80393306L, 80393318L, 
80393326L, 80393376L, 80393382L, 80391713L, 80393446L, 80393468L, 
80393476L, 80393489L, 80393492L), month = c("2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01", 
"2020-01", "2020-01", "2020-01", "2020-01", "2020-01", "2020-01"
), latitude = c("53.794505", "53.795494", "53.797982", "53.800183", 
"53.800938", "53.795991", "53.803257", "53.797570", "53.801495", 
"53.809655", "53.798537", "53.805975", "53.803269", "53.793626", 
"53.788273", "53.788421", "53.804314", "53.803882", "53.797728", 
"53.804477", "53.798164", "53.795910", "53.796001", "53.790341", 
"53.798051", "53.800183", "53.796964", "53.798188", "53.785659", 
"53.788421", "53.795973", "53.791452", "53.799623", "53.803257", 
"53.803269", "53.797032", "53.802451", "53.796014", "53.801451", 
"53.802451", "53.794587", "53.806544", "53.806782", "53.806782", 
"53.794760", "53.795991", "53.801018", "53.795494", "53.797032", 
"53.797545"), longitude = c("-1.563658", "-1.539480", "-1.541457", 
"-1.543665", "-1.543626", "-1.540218", "-1.548308", "-1.541765", 
"-1.536347", "-1.545688", "-1.548009", "-1.561338", "-1.528174", 
"-1.540093", "-1.538618", "-1.542214", "-1.547386", "-1.544794", 
"-1.538560", "-1.564724", "-1.539648", "-1.542527", "-1.542845", 
"-1.553182", "-1.547772", "-1.543665", "-1.550334", "-1.534289", 
"-1.550758", "-1.542214", "-1.566953", "-1.547296", "-1.535700", 
"-1.548308", "-1.528174", "-1.532844", "-1.541909", "-1.541691", 
"-1.539005", "-1.541909", "-1.544606", "-1.528836", "-1.539235", 
"-1.539235", "-1.526524", "-1.540218", "-1.543306", "-1.539480", 
"-1.532844", "-1.554290"), street_id = c(1296121L, 1295922L, 
1295936L, 1296197L, 1296039L, 1295937L, 1296049L, 1295938L, 1295852L, 
1299866L, 1296168L, 1299930L, 1295839L, 1295910L, 1294772L, 1294769L, 
1296031L, 1296048L, 1295905L, 1296086L, 1295891L, 1395742L, 1395741L, 
1294633L, 1296147L, 1296197L, 1296158L, 1295927L, 1294669L, 1294769L, 
1296325L, 1294631L, 1295955L, 1296049L, 1295839L, 1295890L, 1295846L, 
1295908L, 1295862L, 1295846L, 1296138L, 1300105L, 1300120L, 1300120L, 
1295978L, 1295937L, 1296033L, 1295922L, 1295890L, 1296148L), 
    street_name = c("On or near Graingers Way", "On or near Assembly Street", 
    "On or near Shopping Area", "On or near Shopping Area", "On or near Parking Area", 
    "On or near Shopping Area", "On or near Portland Way", "On or near Shopping Area", 
    "On or near Lower Brunswick Street", "On or near Livinia Grove", 
    "On or near Nightclub", "On or near Cross Kelso Road", "On or near Cherry Place", 
    "On or near Dock Street", "On or near Butterley Street", 
    "On or near Kidacre Street", "On or near Pedestrian Subway", 
    "On or near Providence Place", "On or near George Street", 
    "On or near Consort View", "On or near Sidney Street", "On or near Briggate", 
    "On or near Boar Lane", "On or near Union Place", "On or near Russell Street", 
    "On or near Shopping Area", "On or near King Street", "On or near Theatre/concert Hall", 
    "On or near New Princess Street", "On or near Kidacre Street", 
    "On or near Castleton Close", "On or near Victoria Road", 
    "On or near Lady Beck Close", "On or near Portland Way", 
    "On or near Cherry Place", "On or near St Peter's Place", 
    "On or near Tower House Street", "On or near Duncan Street", 
    "On or near Hartley Hill", "On or near Tower House Street", 
    "On or near Parking Area", "On or near Petrol Station", "On or near Oatland Court", 
    "On or near Oatland Court", "On or near Richmond Green Street", 
    "On or near Shopping Area", "On or near Pedestrian Subway", 
    "On or near Assembly Street", "On or near St Peter's Place", 
    "On or near Queen Street"), outcome_status_category = c("0", 
    "0", "0", "0", "0", "Investigation complete; no suspect identified", "0", "0", "Investigation complete; no suspect identified", "0", "0", "0", "0", 
    "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "Investigation complete; no suspect identified", 
    "0", "0", "Investigation complete; no suspect identified", "0", "0", "Investigation complete; no suspect identified", "0", "0", "0", "0", "0", "0", 
    "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", 
    "0")), row.names = c(NA, -50L), class = c("tbl_df", "tbl", 
"data.frame"))

Aucun commentaire:

Enregistrer un commentaire