Search code examples
rstore

Not all values storing in a loop

I want to store values in "yy" but my code below stores only one row (last value). Please see the output below. Can somebody help to store all the values in "yy"

Thanks in advance. I am a beginner to R. 

arrPol <- as.matrix(unique(TN_97_Lau_Cot[,6]))
arrYear <- as.matrix(unique(TN_97_Lau_Cot[,1]))

for (ij in length(arrPol)){
    for (ik in length(arrYear)) {
     newPolicy <- subset(TN_97_Lau_Cot, POLICY == as.character(arrPol[ij]) & as.numeric(arrYear[ik]))
     yy <- newPolicy[which.min(newPolicy$min_dist),]
  }
}

Output:

YEAR DIVISION STATE COUNTY CROP POLICY STATE_ABB LRPP min_dist
1: 2016        8    41     97   21 699609        TN    0      2.6

Here is a image of "TN_97_Lau_Cot" matrix.

enter image description here

Solution

  • No loops required. There could be an easier way to do it, but two set-based steps are better than two loops. These are the two ways I would try and do it:

    base

    # Perform an aggregate and merge it to your data.frame.
TN_97_Lau_Cot_Agg <- merge(
  x = TN_97_Lau_Cot, 
  y = aggregate(min_dist ~ YEAR + POLICY, data = TN_97_Lau_Cot, min),
  by = c("YEAR","POLICY"),
  all.x = TRUE
)

# Subset the values that you want.
TN_97_Lau_Cot_Final <- unique(subset(TN_97_Lau_Cot_Agg, min_dist.x == min_dist.y))

    data.table

    library(data.table)

# Convert your data.frame to a data.table.
TN_97_Lau_Cot <- data.table(TN_97_Lau_Cot)

# Perform a "window" function that calculates the min value for each year without reducing the rows.
TN_97_Lau_Cot[, minDistAggregate:=min(min_dist), by = c("YEAR","POLICY")]

# Find the policy numbers that match the minimum distance for that year.
TN_97_Lau_Cot_Final <- unique(TN_97_Lau_Cot[min_dist==minDistAggregate, -10, with=FALSE])