CODEWITHSUNDEEP
rformattingdata-management
woodduck
woodduck

Reputation: 55

need to reorganize data

I am new to R and have a data formatting question. I need to transform this:

Poly Tran Strat Surv MALLP MALLS MALLG MALLF GADWP GADWS GADWG GADWF
AL    1    M      y     1    2    0      0     1     4     0     0
ARL   1    M      y     0    0    0      0     0     0     20    0
AM    1    M      y     0    0    0      0     0     0     0     0
AM    2    M      y     1    0    0      0     0     0     0     5

to this:

Poly Tran Strat Surv  Spp   Num   Status
AL    1    M      y   mall   1      p
AL    1    M      y   mall   2      s
AL    1    M      y   gadw   1      p
AL    1    M      y   gadw   4      s
ARL   1    M      y   gadw   20     g
AM    2    M      y   mall   1      p
AM    2    M      y   gadw   5      f

I need some help!
Thankyou.

Upvotes: 3

Views: 91

Answers (4)

Arun
Arun

Reputation: 118889

Here's using data.table:

dt.m = melt(dt, id=1:4, variable.name="Spp", value.name="Num")[Num != 0L]
dt.m[, c("Spp", "Status") := list(substring(Spp, 1, 4), substring(Spp, 5, 5))]

Alternatively using read.fwf:

dt.m = melt(dt, id=1:4, variable.name="Spp", value.name="Num", variable.factor = FALSE)
dt.m[Num != 0L][, c("Spp", "Status") := read.fwf(textConnection(Spp), widths=c(4,1))]

Upvotes: 0

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193687

For fun, here's a base R approach. I've replaced "0" with NA, so that na.omit could be used to "shrink" the dataset down after stacking the values. From there, it's a little bit of basic gsubbing to get the "spp" and "status" values.

within(na.omit(
  cbind(dat[1:4], 
        stack(replace(dat[-c(1:4)], dat[-c(1:4)] == 0, NA)))), {
          ind <- tolower(ind)
          spp <- gsub("(mall|gadw).*", "\\1", ind)
          status <- gsub("mall|gadw", "", ind)
          rm(ind)
        })
#    Poly Tran Strat Surv values status  spp
# 1    AL    1     M    y      1      p mall
# 4    AM    2     M    y      1      p mall
# 5    AL    1     M    y      2      s mall
# 17   AL    1     M    y      1      p gadw
# 21   AL    1     M    y      4      s gadw
# 26  ARL    1     M    y     20      g gadw
# 32   AM    2     M    y      5      f gadw

Ordering is pretty straightforward, as demonstrated in Dominic's and Steven's answers.

Upvotes: 2

Dominic Comtois
Dominic Comtois

Reputation: 10421

A solution using base R (except for reshape2::melt):

dat2 <- reshape2::melt(dat, id.vars=c("Poly","Tran","Strat","Surv"))
dat2 <- subset(dat2, subset = value > 0)
dat2$variable <- as.character(dat2$variable)
dat2$Status <- with(dat2, substr(tolower(variable), nchar(variable), nchar(variable)))
dat2$variable <- with(dat2, substr(tolower(variable), 1, nchar(variable)-1))
dat2 <- dat2[order(dat2$Tran),]
colnames(dat2) <- c("Poly", "Tran", "Strat", "Surv", "Spp", "Num", "Status")
rownames(dat2) <- NULL

Results

> dat2

  Poly Tran Strat Surv  Spp Num Status
1   AL    1     M    y mall   1      p
2   AL    1     M    y mall   2      s
3   AL    1     M    y gadw   1      p
4   AL    1     M    y gadw   4      s
5  ARL    1     M    y gadw  20      g
6   AM    2     M    y mall   1      p
7   AM    2     M    y gadw   5      f

Data

dat <- read.csv(text = "Poly,Tran,Strat,Surv,MALLP,MALLS,MALLG,MALLF,GADWP,GADWS,GADWG,GADWF
AL,1,M,y,1,2,0,0,1,4,0,0
ARL,1,M,y,0,0,0,0,0,0,20,0
AM,1,M,y,0,0,0,0,0,0,0,0
AM,2,M,y,1,0,0,0,0,0,0,5")

Upvotes: 2

Steven Beaupr&#233;
Steven Beaupr&#233;

Reputation: 21641

Using dplyr and tidyr you could do:

library(tidyr)
library(dplyr)

df %>% gather(Spp, Num, -Poly, -Tran, -Strat, -Surv) %>%
  mutate(Status = tolower(substr(Spp, 5, 5)),
         Spp = tolower(substr(Spp, 1, 4))) %>%
  filter(!Num == 0) %>%
  arrange(Tran)

Which gives:

#  Poly Tran Strat Surv  Spp Num Status
#1   AL    1     M    y mall   1      p
#2   AL    1     M    y mall   2      s
#3   AL    1     M    y gadw   1      p
#4   AL    1     M    y gadw   4      s
#5  ARL    1     M    y gadw  20      g
#6   AM    2     M    y mall   1      p
#7   AM    2     M    y gadw   5      f

Upvotes: 3

Related Questions