How to reshape the data, with headers in two rows, from wide to long using R

Question

The following dataset is what I have now:

I want to get R code to get the following shape:

Note that: I have 20 stations and some stations do not have the WD attribute.

Regards,

Ahmad

Does [This](https://stackoverflow.com/q/2185252/4752675) answer your question? — G5W, Mar 28 '21 at 18:57

score 0 · Answer 1 · answered Mar 28 '21 at 18:56

0

You should try looking into the pivot_wider() function and documentation there from the tidyverse package. The use case should match exactly what you are describing here.

answered Mar 28 '21 at 18:56

Carey Caginalp

432
2
5

I have two level of header, how can I specify the code to understand the first header row and second header row? – Ahmad Alsaber Mar 28 '21 at 19:06
1

Hmm that is more complicated - I think you might have to combine them and take them apart later. I.e. make into one header row with "Station1Temperat", Station1RH", etc. and use that as your new header row. Then you can use the regex options to identify which columns are Station 1 or 2 and split apart later on. I'll let you know if I think of a more elegant solution. – Carey Caginalp Mar 28 '21 at 19:17

score 0 · Accepted Answer · answered Mar 29 '21 at 10:49

Let assume we have the following data with two rows header:

c1 <- c('','','Alice','Alice','Bob','Bob')
c2 <- c('','','Cold','Flu','Cold','Flu')
c3 <- c('2014','Fall','X','','','X')
c4 <- c('2014','Winter','','X','','')
c5 <- c('2015','Fall','X','X','','X')
c6 <- c('2015','Winter','','','X','')
df <- data.frame(c1,c2,c3,c4,c5,c6)

I'd first combine the two columns into one, then melt/gather and finally separate

library(dplyr) 
library(tidyr)

insert your df here

col.names <- c("Person", "Type", tail(paste(t(df[1,]),t(df[2,]),sep="."),n=-2))

df <- df[-c(1,2),]

df <- setNames(df,col.names)

df %>%
  gather(Season, Value, -Person, -Type) %>%
  mutate(Season = as.character(Season)) %>%
  separate(Season,c("Year","Season"))

Sg2403 · Answer 3 · 2021-03-30T08:29:58.867

0

You can try using reshape2 package


Df_long <- melt(df, id.vars=c("Date", "Time"), measure.vars=c("TEMPERAT","RH","WS","WD","SOL_RAD","UV A","UV B"), variable.name="Values",value.name="Station") ```

edited Mar 30 '21 at 08:29

answered Mar 29 '21 at 11:50

Sg2403

1
2

Please wrap your code between two ``` (three backticks) , one at the beginning and one at the end for better readability. – Anoushiravan R Mar 29 '21 at 19:55

How to reshape the data, with headers in two rows, from wide to long using R

3 Answers3