Search code examples
rdataframerowsdata-cleaningcol

When merging data frames I need to restructure my rows and columns in R

I have run a full spatial analysis in R for an ecology study. To do so I have had to run a variety of geo operations in the rgeos package e.g. gUnion, gIntersect, gArea etc etc.

When I finally begin to merge all the data together I have a problem.

I am working with data of farm plots and when I use gintersect to extract the habitat characteristics (one layer is farms and one is land use, both are vector .shp files) the resulting dataframe creates a new row for each habitat despite being for same farm.

I would like to know if it is possible to create new columns for each habitat instead of new rows so that each farm only has one line of data.

example below

set.seed(100)

WhatIHave <- data.frame(ID = c(1, 2, 3, 4, 5, 6),
                  farm = c('a', 'a', 'a', 'a', 'b', 'b'),
                  habitat = c("urban", "pasture","urban", "pasture", "urban", "pasture"))




WhatIWant <- data.frame(ID = c(1, 2, 3),
                        farm = c('a', 'a', 'b'),
                        habitat1 = c("urban", "urban", "urban"),
                        habitat2 = c("pasture", "pasture", "pasture"))


WhatIHave
WhatIWant

Real dataframe below. As you can see there should only be 3 rows (one farm has two fields so one row per field as they are different) and there are many duplicated values as there should only be three rows therefore I need Habitat and Habitat_Area_Hectares to be multiple columns for each row.

enter image description here

Solution

  • I figured it out.

    bufflanduse <- spread(bufflanduse, key=Habitat, value=Ha)

    This extracts the values from the rows and creates a new column (key) and fills it with the data provided (value) in my case it was the area. Now each farm has a list of habitats and the associated areas.