Gustavo Gustavo - 2 months ago 7
R Question

R Create a new column from a header and reorder table using loop

I would like to ask for help for extract informations from a header

I have a table with hundreds of rows and 1000 columns (equal) in a file (example below) like this one and I would like to make a loop to extract the dates from a header part (new column) and reorder the values in rows.

R2n_19970919__105056604_2_BF.MER_A123_DAY_00.nc <- c(0.09,0.09,0.08,0.08,0.06,0.07,0.09,0.08,0.08,"NA")
R2n_19970920__105056604_2_BF.MER_A123_DAY_00.nc <- c(0.08,0.08,0.08,0.07,"NA",0.05,0.08,0.08,0.08,"NA")
R2n_19970921__105056604_2_BF.MER_A123_DAY_00.nc <- c(0.07,"NA",0.08,"NA","NA",0.07,0.06,"NA",0.08,"NA")
data <- data.frame(R2n_19970919__105056604_2_BF.MER_A123_DAY_00.nc,R2n_19970920__105056604_2_BF.MER_A123_DAY_00.nc,R2n_19970921__105056604_2_BF.MER_A123_DAY_00.nc)


how to do that best? Help would be much appreciated.

Here is my expected result:

R2n_19970919__105056604_2_BF.MER_A123_DAY_00.nc = 1997/09/19.

Date R2n.nc
1997/09/19 0.09
1997/09/19 0.09
1997/09/19 0.08
1997/09/19 0.08
1997/09/19 0.06
1997/09/19 0.07
1997/09/19 0.09
1997/09/19 0.08
1997/09/19 0.08
1997/09/19 NA
1999/09/20 0.08
1999/09/20 0.08
1999/09/20 0.08
1999/09/20 0.07
1999/09/20 NA
1999/09/20 0.05
1999/09/20 0.08
1999/09/20 0.08
1999/09/20 0.08
1999/09/20 NA
2001/09/21 ...
.
.
.

bVa bVa
Answer

Here is a solution, using tips suggested by @Roman Luštrik :

library(stringr) # str_sub() function
library(reshape2) # melt() function

# Modify columns names (if date information is always at the same position)
names(data) = paste0(str_sub(names(data), 5,8), "-", str_sub(names(data), 9,10), "-",str_sub(names(data), 11, 12))
data$id = seq(1,nrow(data))

# Melt the data
data_melt = melt(data, id = "id")  


> data_melt
   id   variable value
1   1 1997-09-19  0.09
2   2 1997-09-19  0.09
3   3 1997-09-19  0.08
4   4 1997-09-19  0.08
5   5 1997-09-19  0.06
...
Comments