CODEWITHSUNDEEP
rsubset
user12345678
user12345678

Reputation: 101

Subset Data based on separate Dataframe (R)

I know that variants of this question have been asked before and I have tried solutions from (Select rows from a data frame based on values in a vector) and (subset a column in data frame based on another data frame/list) but I haven't been able to make these solutions work. The solutions keep returning a data frame with 0 observations.

My first data frame looks something like this:

> head(test3)
  long    lat     time   precip  GID_0   GID_1   HASC_1
168.75 -46.25 Jan_1979 5.534297   NZL  NZL.14_1  NZ.SO
171.25 -43.75 Jan_1979 4.191629   NZL  NZL.3_1   NZ.CA
146.25 -41.25 Jan_1979 3.139199   AUS  AUS.9_1   AU.TS
173.75 -41.25 Jan_1979 1.770889   NZL  NZL.8_1   NZ.MA
176.25 -38.75 Jan_1979 2.257812   NZL  NZL.17_1  NZ.WK
141.25 -36.25 Jan_1979 1.985313   AUS  AUS.10_1  AU.VI

I have a separate data frame that contains a single column with ID values that looks like this:

> head(africa_iso)
ISO
DZA
AGO
SHN
BEN
BWA
BFA

I'd like to filter the first dataframe so that only observations that match on GID_0 and ISO are remaining (conceptually, the first dataset includes observations for all countries, I'd like to filter this to a dataset with observations from African countries only). I currently have 725,517 observations in the first data frame and I expect to have approximately 200k observations after filtering.

These have been my attempts so far and every time I'm left with a new data frame that has 7 columns and no observations.

Afr <- subset(test3, GID_0 %in% africa_iso$ISO) #attempt 1

Afr <- setDT(test3)[GID_0 %in% africa_iso$ISO] #attempt 2

Afr <- test3[test3$GID_0 %in% africa_iso$ISO,] #attempt 3

Afr <- filter(test3$GID_0 %in% africa_iso$ISO  ) #attempt 4

Afr <- setDT(test3)[GID_0 %chin% africa_iso$ISO] #attempt 5

Afr <- test3[match(test3$GID_0, africa_iso$ISO),] #attempt 6

Afr <-test3[is.element(test3$GID_0, africa_iso$ISO),] #attempt 7

I'm sure this is a trivial problem for someone else, but I would appreciate any help. Thank you.

EDIT:

> str(test3)
Classes ‘data.table’ and 'data.frame':  725517 obs. of  7 variables:
 $ long  : num  169 171 146 174 176 ...
 $ lat   : num  -46.2 -43.8 -41.2 -41.2 -38.8 ...
 $ time  : Factor w/ 477 levels "Jan_1979","Feb_1979",..: 1 1 1 1 1 1 1 1 1        
 $ precip: num  5.53 4.19 3.14 1.77 2.26 ...
 $ ISO   :'data.frame': 725517 obs. of  1 variable:
..$ : chr  "NZL" "NZL" "AUS" "NZL" ...
 $ ISOP  :'data.frame': 725517 obs. of  1 variable:
..$ : chr  "NZL.14_1" "NZL.3_1" "AUS.9_1" "NZL.8_1" ...
 $ HASC  :'data.frame': 725517 obs. of  1 variable:
..$ : chr  "NZ.SO" "NZ.CA" "AU.TS" "NZ.MA" ...
- attr(*, ".internal.selfref")=<externalptr>

And 

> str(africa_iso)
'data.frame':   62 obs. of  1 variable:
 $ ISO: Factor w/ 57 levels "AGO","BDI","BEN",..: 14 1 43 3 5 4 2 8 12 6 ...

Upvotes: 0

Views: 123

Answers (1)

r2evans
r2evans

Reputation: 160397

Several of your columns in test3 are not proper character: they are embedded data.frames, which is complicating your lookup. If you are not doing this intentionally, you can correct this with:

isdf <- sapply(test3, is.data.frame)
test3[isdf] <- lapply(test3[isdf], `[[`, 1)
subset(test3, GID_0 %in% africa_iso$ISO)
#     long    lat     time   precip GID_0    GID_1 HASC_1
# 1 168.75 -46.25 Jan_1979 5.534297   NZL NZL.14_1  NZ.SO
# 2 171.25 -43.75 Jan_1979 4.191629   NZL  NZL.3_1  NZ.CA
# 4 173.75 -41.25 Jan_1979 1.770889   NZL  NZL.8_1  NZ.MA
# 5 176.25 -38.75 Jan_1979 2.257812   NZL NZL.17_1  NZ.WK

I previously altered your africa_iso to include NZL so that there would be a match:

> dput(africa_iso)
structure(list(ISO = structure(c(5L, 1L, 6L, 2L, 4L, 3L), .Label = c("NZL", 
"BEN", "BFA", "BWA", "DZA", "SHN"), class = "factor")), row.names = c(NA, 
-6L), class = "data.frame")

Upvotes: 1

Related Questions