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 ...
.
.
.
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
...