Search code examples
rrowsdata-cleaning

R- from columns to rows without taking the header


I have 8 variables per company, with a total of 25 companies. However, i don't need to make any distinction between these companies. If you look at the example: I need to have AH and JUMBO in one column, the same for AHQ1 and JUMBOQ1, and for both Q2s. In this way i don't have 6 columns, but just 3 and twice as much observations in these rows. The title of the column can stay AH, AHQ1, and AHQ2.

Thanks in advance for any tips!!

Example of data:

df <- data.frame("ID" = c(1,1,2,2,2,2), "Year" = c(2012, 2015,2012,2013,2015,2016), 
"AH" = c(1, NA, 1,1,1,1), "AHQ1" = c(8, NA,7,8,9,10),
             "AHQ2" = c(10,NA,7,8,5,2),"JUMBO" = c(NA,NA,1,1,1,NA), 
"JUMBOQ1" = c(NA,NA,8,9,7,NA), "JUMBOQ2"= c(NA,NA,10,9,7,NA))

Solution

  • temp <- cbind(df[1:2], df[6:8])
    names(temp) <- names(df[1:5])
    
    df2 <- rbind(df[1:5], temp)
    
    > df2
       ID Year AH AHQ1 AHQ2
    1   1 2012  1    8   10
    2   1 2015 NA   NA   NA
    3   2 2012  1    7    7
    4   2 2013  1    8    8
    5   2 2015  1    9    5
    6   2 2016  1   10    2
    7   1 2012 NA   NA   NA
    8   1 2015 NA   NA   NA
    9   2 2012  1    8   10
    10  2 2013  1    9    9
    11  2 2015  1    7    7
    12  2 2016 NA   NA   NA
    

    Is this what you are looking for?