Colums to rows based on unique ID

user_9623670 0
Viewed 105

My data is currently formatted as such:

    ID  PC1     PC2     PC3     PC4
    5   8970    864     
    6   2800    2812    2801    284

What I would like is a separate row for each data point, linked to the unique ID so that:

    ID  PC
    5   8970
    5   864
    6   2800
    6   2812
    6   2801
    6   284

I realise this is a very basic question, but in looking for similar questions I can only find ways to do this the other way around!

transpose r
3 Answers

Maybe you can try reshape like below

dfout <- setNames(reshape(df,
                          direction = "long",
                          idvar = "ID",
                          varying = list(grep("^PC",names(df))))[-2],
                  c("ID","PC"))

dfout <- `row.names<-`(subset(dfout[order(dfout$ID),],!is.na(PC)),NULL)

such that

> dfout
  ID   PC
1  5 8970
2  5  864
3  6 2800
4  6 2812
5  6 2801
6  6  284

DATA

df <- structure(list(ID = 5:6, PC1 = c(8970L, 2800L), PC2 = c(864L, 
2812L), PC3 = c(NA, 2801L), PC4 = c(NA, 284L)), class = "data.frame", row.names = c(NA, 
-2L))
user_121587570 

library(dplyr)
library(tidyr)

df <- data.frame(
  ID = c(5,6), 
  PC1 = c(8970, 2800), 
  PC2 = c(864, 2812), 
  PC3 = c(NA, 2801), 
  PC4 = c(NA, 284)
)



df %>% 
  tidyr::pivot_longer(-ID, names_to = "PC_code", values_to = "value") %>% 
  dplyr::filter(!is.na(value)) %>% 
  dplyr::select(-PC_code)

user_62249260

It's better to fill blanks with NA's you can do this easy by:

library(dplyr)
df <- df %>% mutate_all(na_if,"")

My naive solution, but clear:

library(reshape)
Input = (
  'ID  PC1     PC2     PC3     PC4
5   8970    864     NA     NA    
6   2800    2812    2801    284')
df = read.table(textConnection(Input), header = T)
df
res <- melt(df,id='ID')
res$variable <- NULL
res <- res[complete.cases(res),]
res <- res[order(res$ID),]
colnames(res)[2] <- 'PC'
res
  ID   PC
1  5 8970
3  5  864
2  6 2800
4  6 2812
6  6 2801
8  6  284
user_50822940
Related