使用R提取一行中标识符的数据，并将其放入新列中

Question

我有一些使用ArcGIS获取的数据，我希望通过分水岭标识符(例如HUC_8=1404106)构建一个数据库。数据包含流域标识符(HUC_8)、流域面积、土壤类型和土壤面积。流域标识符列出的次数与土壤类型一样多。我希望创建一个基于流域的数据库(标识符只在列中显示一次)，并在不同的列中按类型提取土壤区域。我附加了一个子集的数据，希望它是明确的。我对R有点陌生，但我觉得这可以用for循环来完成。知道如何做到这一点是非常有帮助的，因为我在地理信息系统中做了很多工作，但我想在R中进行更多的分析。

HUC_8   WatershedArea   Soil    SoilArea    A_Area  B_Area  C_Area  D_Area  Null_Area
14040106    461104.4883 B       96590.33424                 
14040106    461104.4883 C       86282.93487                 
14040106    461104.4883 D       24945.9992                  
14050007    921494.3621 Null    2.861388                    
14050007    921494.3621 A       87214.28385                 
14050007    921494.3621 B       131417.8659                 
14050007    921494.3621 C       268324.5125                 
14050007    921494.3621 D       314131.5806                 
14060001    627348.8316 Null    8119.375083                 
14060001    627348.8316 A       5315.511117                 
14060001    627348.8316 B       286915.9001                 
14060001    627348.8316 C       114357.5251                 
14060001    627348.8316 D       163671.7545             

Answer 1

本质上，这听起来像是你想要重塑你的数据从长格式到宽格式。reshape2库在这里可以派上用场

#sample data
dd<-read.table(text="HUC_8   WatershedArea   Soil    SoilArea
14040106    461104.4883 B       96590.33424                 
14040106    461104.4883 C       86282.93487                 
14040106    461104.4883 D       24945.9992                  
14050007    921494.3621 Null    2.861388                    
14050007    921494.3621 A       87214.28385                 
14050007    921494.3621 B       131417.8659                 
14050007    921494.3621 C       268324.5125                 
14050007    921494.3621 D       314131.5806                 
14060001    627348.8316 Null    8119.375083                 
14060001    627348.8316 A       5315.511117                 
14060001    627348.8316 B       286915.9001                 
14060001    627348.8316 C       114357.5251                 
14060001    627348.8316 D       163671.7545", header=T)

现在转换数据

library(reshape2)

wide <- dcast(dd, HUC_8+ WatershedArea ~ Soil)

#change default column names
soils <- levels(dd$Soil)
names(wide)[match(soils, names(wide))] <- paste(soils,"Area",sep="_")

，这导致了wide数据格式，它看起来像

     HUC_8 WatershedArea    A_Area    B_Area    C_Area   D_Area   Null_Area
1 14040106      461104.5        NA  96590.33  86282.93  24946.0          NA
2 14050007      921494.4 87214.284 131417.87 268324.51 314131.6    2.861388
3 14060001      627348.8  5315.511 286915.90 114357.53 163671.8 8119.375083

Answer 2

你可以试试

lst <- Map(function(x,y) ifelse(df$Soil==x,y, NA),
                     sort(unique(df$Soil)), list(df$SoilArea))
names(lst) <- paste(names(lst), 'Area', sep="_")
df[names(lst)]  <- lst

head(df,3)
#      HUC_8 WatershedArea Soil SoilArea A_Area   B_Area   C_Area D_Area
#1 14040106      461104.5    B 96590.33     NA 96590.33       NA     NA
#2 14040106      461104.5    C 86282.93     NA       NA 86282.93     NA
#3 14040106      461104.5    D 24946.00     NA       NA       NA  24946
#   Null_Area
#1        NA
#2        NA
#3        NA

更新

如果您想从reshape从long到wide，也可以使用base R reshape

df1 <- reshape(df, idvar=c('HUC_8', 'WatershedArea'),
                          timevar='Soil', direction='wide')

colnames(df1)[-(1:2)] <- paste0(gsub('.*\\.', '',
                           colnames(df1)[-(1:2)]), '_Area')
df1[,c(1:2,7,3:6)]
#     HUC_8 WatershedArea    A_Area    B_Area    C_Area   D_Area   Null_Area
#1 14040106      461104.5        NA  96590.33  86282.93  24946.0          NA
#4 14050007      921494.4 87214.284 131417.87 268324.51 314131.6    2.861388
#9 14060001      627348.8  5315.511 286915.90 114357.53 163671.8 8119.375083

数据

df <- structure(list(HUC_8 = c(14040106L, 14040106L, 14040106L, 14050007L, 
14050007L, 14050007L, 14050007L, 14050007L, 14060001L, 14060001L, 
14060001L, 14060001L, 14060001L), WatershedArea = c(461104.4883, 
461104.4883, 461104.4883, 921494.3621, 921494.3621, 921494.3621, 
921494.3621, 921494.3621, 627348.8316, 627348.8316, 627348.8316, 
627348.8316, 627348.8316), Soil = c("B", "C", "D", "Null", "A", 
"B", "C", "D", "Null", "A", "B", "C", "D"), SoilArea = c(96590.33424, 
86282.93487, 24945.9992, 2.861388, 87214.28385, 131417.8659, 
268324.5125, 314131.5806, 8119.375083, 5315.511117, 286915.9001, 
114357.5251, 163671.7545)), .Names = c("HUC_8", "WatershedArea", 
"Soil", "SoilArea"), class = "data.frame", row.names = c(NA, 
-13L))