I have a huge times series dataset (over 500 variables) consisting of monthly observations for several years. See a simplified example of the input data below (in Excel):
Now, what I need to do (in R) is to transpose this input data set so that the variable date ends up as the new variable names and the current variable names end up in the column date. See a simplified example of the desired output data below (in Excel):
Note that is important that the dates in the variable names in the desired end result need to be in descending order (newest closest to the left).
I do manage to transpose the data using the function t(), however, the problem comes after that.
First problem: The variable date is automatically set as rownames after transposing, which is fine, I just run the function rownames_to_column() to fix this. However, is there a way to avoid this maneuver?
Second problem: All the new variable names end up in the first row, but when I run the function row_to_names(1) to change this the dates in the variable names end up weird. The only solution to this that I have made work (almost all the way) is to first convert all variables to character format before running row_to_names(1). After that I need to change my date-variables back to numeric format which I need to do in order to proceed with further calculations. However, when I do this on my real data set (which is the same just much bigger) I get the error message: "NAs introduced by coercion". Why does this happen? I can't seem to find out why. If you find a different approach to do this that is also fine.
# Example data to copy and paste into R for easy reproduction of problem:
df <- data.frame (date = c("2018-11-30", "2018-12-31", "2019-01-31", "2019-02-28", "2019-03-31", "2019-04-30"),
fruit_apples = c(333, 337, 341, 345, 349, 353),
fruit_pears = c(86, 87, 88, 89, 90, 91),
fruit_grapes = c(255, 258, 261, 264, 267, 270),
veggies_tomatoes = c(2024, 2025, 2026, 2027, 2028, 2029),
veggies_carrots = c(92, 95, 98, 101, 104, 107),
veggies_cucumber = c(52, 54, 56, 58, 60, 62),
pets_rabbit = c(25, 26, 27, 28, 29, 30),
pets_cat = c(51, 53, 55, 57, 59, 61),
pets_dog = c(117, 121, 125, 129, 133, 137))
# The code I have been using so far:
df_output <- df %>%
arrange(desc(date)) %>% # after transposing I want the newest date to be closest to the left, hence this step
t() %>%
as.data.frame() %>%
rownames_to_column() %>%
mutate(across(everything(), as.character)) %>%
row_to_names(1) %>%
mutate_at(vars(-date), as.numeric) %>% # causes error message "NAs introduced by coercion"
adorn_totals()