Use R to extract data for an identifier in a row and put it in a new column

Question

I have some data that I used ArcGIS to get and I want to construct a database by watershed identifier (for example HUC_8=1404106). The data contains the watershed identifier (HUC_8), watershed Area, Soil type, and Soil Area. The watershed identifier is listed as many times as there are soil types. I want to create a database based on watersheds (having the identifier only appear once in the column) and extract the soil area by type in different columns. I attached a subset of the data so that hopefully it is clear. I am somewhat new to R, but I feel that this could be done with a for loop. Knowing how to do this would be extremely helpful, being that I work a lot with GIS, but would like to perform more analysis in 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

Essentially it sounds like you want to reshape your data from long format to wide format. The reshape2 library can come in handy here

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

now transform the data

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="_")

which results in the wide dataframe which looks like

     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

You could try

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

Update

If you wanted to reshape from long to wide, you could also use 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

data

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