Not all values storing in a loop

Question

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.

You need to create `yy` ahead of time and identify where in `yy` each value should be stored, i.e. `yy[ij,ik]`. — K.Daisey, Feb 24 '17 at 16:56
Can you give us some sample data from the variables you are working with? There may be ways of vectorising or using `lapply` depending on what you're trying to do. — Oliver Frost, Feb 24 '17 at 17:01
I agree but for each ij there are multiple rows/values as shown in the outoput from which "min" is estimated and should be stored. — user3408139, Feb 24 '17 at 17:01
We can't do anything with an image. See this: http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Oliver Frost, Feb 24 '17 at 17:44
@Oliver: Please download the data from this link (CSV file). https://bft.usu.edu/jnfbk — user3408139, Feb 24 '17 at 17:57
@ Oliver. I need to find the minimum distance (min_dist) from col 9 for each year and unique policy number in col 6. Hope you understood my question. — user3408139, Feb 24 '17 at 17:59
So for each year, and each unique policy number, you want to know the minimum value of `min_dist`? — Oliver Frost, Feb 24 '17 at 21:30
Or are you expecting the minimum value for `min_dist` for each year and the policy number it came from? — Oliver Frost, Feb 24 '17 at 21:36
@Oliver: Yes, you are correct. I can manually estimate the rows in Excel and tell but is there a way to estimate it in R. — user3408139, Feb 24 '17 at 21:42
So you're expecting the minimum value for min_dist for each year and the policy number it came from? — Oliver Frost, Feb 24 '17 at 21:46
@OLiver: That is correct. I need the corresponding values of other columns as well as shown in the "Output" in my question. — user3408139, Feb 24 '17 at 21:52

Oliver Frost · Accepted Answer · 2017-02-24T22:46:46.110

0

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])

edited Feb 24 '17 at 22:46

answered Feb 24 '17 at 22:16

Oliver Frost

827
5
18

If you want to remove duplicate rows, use `unique()` – Oliver Frost Feb 24 '17 at 22:34
Thanks for your prompt reply. But, that's not the answer I was looking for. I want to know the "min_dist" for each policy number in each year. Hope it helps. – user3408139 Feb 24 '17 at 22:35
That wasn't what you said when I asked "So you're expecting the minimum value for min_dist for each year and the policy number it came from?" But I have edited my original answer. If that isn't the answer you're looking for then you are going to have to create an example output. – Oliver Frost Feb 24 '17 at 22:47
Thanks so much. This is the answer I was looking for. Many thanks. – user3408139 Feb 24 '17 at 22:54
No problem. Mark it as correct if you're happy with it. – Oliver Frost Feb 24 '17 at 22:55
Click the tick to the left of this answer. – Oliver Frost Feb 27 '17 at 16:58

Not all values storing in a loop

1 Answers1