I have a data structure like so:

I want to coalesce the column titles to the day numbers into a new column, then stack the first 70 rows of each column into 1 new column.

daynum = c("day1", "day2", "day3", "day4")
year1 = c(1,2,3,4)
year2 = c(5,6,7,8)
year3 = c(7,8,9,10)

df <- data.frame(daynum, year1, year2, year3)
print(df)

  #create output data outside of loop
summerdata = data.frame()

for (i in names(df[,-1])){
  yeardate = data.frame(paste(i, df$daynum, sep="-"))
  sdata = data.frame(df[[i]])
  sur = sdata[1:70,]
  sy = yeardate[1:70,]
  endsummers = cbind(sy,sur)
  summerdata = rbind(summerdata,endsummers)
}
colnames(endsummers)=c("yeardate","summertemp")
print(endsummers)  

all that returns in the first year1, and it's storing the numbers as text.

output should look like this:

      yeardate       summertemp
 [1,] "year1-day1"  85      
 [2,] "year1-day2"  87     
 [3,] "year1-day3"  79  

CodePudding user response：

library(tidyverse)
df %>%
  pivot_longer(-daynum) %>%
  group_by(name) %>% slice(1:70) %>% ungroup() %>%  # only first 70 rows per year
  unite("daynum", 2:1, sep = "-") %>%
  arrange(daynum)

Result

# A tibble: 12 × 2
   daynum     value
   <chr>      <dbl>
 1 year1-day1     1
 2 year1-day2     2
 3 year1-day3     3
 4 year1-day4     4
 5 year2-day1     5
 6 year2-day2     6
 7 year2-day3     7
 8 year2-day4     8
 9 year3-day1     7
10 year3-day2     8
11 year3-day3     9
12 year3-day4    10

CodePudding user response：

So, there is actually nothing wrong with your loop, you are just changing the names of and printing endsummers, rather than summerdata. Also, to change to numeric, we can just add in as.numeric for sur when using cbind.

#create output data outside of loop
summerdata = data.frame()

for (i in colnames(df[,-1])){
  yeardate = data.frame(paste(i, df$daynum, sep="-"))
  sdata = data.frame(df[[i]])
  sur = sdata[1:70,]
  sy = yeardate[1:70,]
  endsummers = cbind(sy,as.numeric(sur))
  summerdata = rbind(summerdata,endsummers)
}

colnames(summerdata) <- c("yeardate","summertemp")
print(summerdata)

Output

head(summerdata, 15)

     yeardate summertemp
1  year1-day1          1
2  year1-day2          2
3  year1-day3          3
4  year1-day4          4
5  year1-day1          5
6  year1-day2          6
7  year1-day3          7
8  year1-day4          8
9  year1-day1          9
10 year1-day2         10
11 year1-day3         11
12 year1-day4         12
13 year1-day1         13
14 year1-day2         14
15 year1-day3         15

Data

df <- structure(list(daynum = c("day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4", "day1", 
"day2", "day3", "day4", "day1", "day2", "day3", "day4"), year1 = c(1, 
2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 
36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 
52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 
68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 
84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 
100), year2 = c(5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 
34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 
50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 
66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 
82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 
98, 99, 100, 101, 102, 103, 104, 105), year3 = c(10, 11, 12, 
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 
45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 
61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 
77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 
93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 
107, 108, 109, 110)), class = "data.frame", row.names = c(NA, 
-100L))