I have following dataframe in r
volume type_of_soil quantity
2345 slit,coarse sand 324.45
1123.45 fine sand 45
123.44 sand, sandstone, gravel, silt 567
234 marine clay & hard strata 45
232 category d 55
My desired dataframe would be
volume type_of_soil_1 type_of_soil_2 type_of_soil_3 type_of_soil_4 quantity
2345 slit coarse sand NA NA 324.45
1123.45 fine sand NA NA NA 45
123.44 sand sandstone gravel silt 567
234 marine clay hard strata NA NA 45
232 category d NA NA NA 55
I want to seperate comma and & seperated values in type_of_soil column.
I have tried following,but it does not work
max_length <- as.vector(count.fields(textConnection(df$type_of_soil), sep = ","))
max_length <- max(max_length,na.rm = T)
splitdat = do.call("rbind", strsplit(df$type_of_soil, ","))
splitdat = data.frame(apply(splitdat, 2, as.character))
names(splitdat) = paste("type_of_soil_", 1:max_length , sep = "")
