I am very new to R but need to use it occasionally for my job. I have a .csv file that I need data from the first 14 rows (March through Sept) from only column 6 (Header is SNWD) to transpose horizontally with 14 new column names. I know how to read in the .csv file, just need help with the actual transpose code.
Current .csv format:
STN,NAME,MO,DAY,YEAR,SNWD
1234,STATION A,3,1,1919,2
1234,STATION A,3,15,1919,3
1234,STATION A,4,1,1919,1
1234,STATION A,4,15,1919,0
1234,STATION A,5,1,1919,6
1234,STATION A,5,15,1919,0
1234,STATION A,6,1,1919,4
1234,STATION A,6,15,1919,0.5
Need the output to look like:
March-1,March-15,April-1,April-15,May-1,May-15,June-1,June-15,July-1,July-15,Aug-1,Aug-15
2,3,1,0,6,0,4,0.5, , , , , ,
Would appreciate any help. Thanks -K-
We can use
library(dplyr)
library(tidyr)
library(data.table)
library(lubridate)
dat %>%
unite(DATE, YEAR, MO, DAY, sep="-") %>%
mutate(DATE = format(ymd(DATE), "%b-%d"), rn = rowid(STN, NAME, DATE)) %>%
pivot_wider(names_from = DATE, values_from = SNWD)
# A tibble: 1 x 11
# STN NAME rn `Mar-01` `Mar-15` `Apr-01` `Apr-15` `May-01` `May-15` `Jun-01` `Jun-15`
# <int> <chr> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 1234 STATION A 1 2 3 1 0 6 0 4 0.5
dat <- structure(list(STN = c(1234L, 1234L, 1234L, 1234L, 1234L, 1234L,
1234L, 1234L), NAME = c("STATION A", "STATION A", "STATION A",
"STATION A", "STATION A", "STATION A", "STATION A", "STATION A"
), MO = c(3L, 3L, 4L, 4L, 5L, 5L, 6L, 6L), DAY = c(1L, 15L, 1L,
15L, 1L, 15L, 1L, 15L), YEAR = c(1919L, 1919L, 1919L, 1919L,
1919L, 1919L, 1919L, 1919L), SNWD = c(2, 3, 1, 0, 6, 0, 4, 0.5
)), class = "data.frame", row.names = c(NA, -8L))