CODEWITHSUNDEEP
rdatabase-design
abdel76
abdel76

Reputation: 1

Rearranging a table from one format to another in R

Below is the code I've used to achieve the table in the first picture. Any suggestions on how to rearrange this into the second picture below are greatly welcomed and appreciated.

`library(rLakeAnalyzer)
library(dplyr)

#Platform Data----
urlfile = "http://epscor.uvm.edu/LakeCarmi/CarmiData-LatestPFLData.csv"   #This is where the 
platform data is stored on Epscor servers
carmidataTEMP <- read.csv(url(urlfile), skip=1)  #Reads in data 

#Formats date from character to POSIXct, a more workable date/time 
carmidataTEMP$TIMESTAMP <- strptime(carmidataTEMP$TIMESTAMP, "%Y-%m-%d %H:%M:%S") 
carmidataTEMP$TIMESTAMP<- as.POSIXct(carmidataTEMP$TIMESTAMP)
#Add year column and subset data by year
carmidataTEMP$Year <- carmidataTEMP$TIMESTAMP
carmidataTEMP$Year <- format(carmidataTEMP$Year, format="%Y")
carmidata <- subset(carmidataTEMP, Year==2021) #Change year to select for 2020 data if needed

#Change column names
names(carmidata)[1] <- "Date"
names(carmidata)[6] <- "Temp"
names(carmidata)[16] <- "DO"
names(carmidata)[18] <- "Chl"
names(carmidata)[20] <- "PC"
names(carmidata)[21] <- "Depth"

#Removes minutes/seconds so each depth shows the same time for each hourly profile
carmidata$Date <- format(carmidata$Date, format="%Y-%m-%d %H:00:00")
carmidata$Date <- as.POSIXct(carmidata$Date)

#Selects just Date, Temperature, Depth
carmidata<-carmidata[,c(1,6,21)]`

This is what I currently have coded

img

This is what I want to Achieve using Rstudio. Notice that the Depth in this table runs along the x-axis and is divided into measurements of 0.5m until it reaches 8.5m.

img

Upvotes: 0

Views: 39

Answers (1)

Ian Campbell
Ian Campbell

Reputation: 24878

This should get you started. There are multiple temperatures within the same 0.5 range, so I decided to take the mean. You can change values_fn to whatever you want.

library(dplyr);library(tidyr)
carmidata %>% 
  mutate(bin = paste0("wrt_",seq(from = 0.5, to = 8.5,by = 0.5))[findInterval(Depth,seq(from = 0, to = 8.5, by = 0.5))]) %>%
  select(-Depth) %>%
  pivot_wider(names_from =bin, values_from = Temp, values_fn = mean) %>%
  select(Date,paste0("wrt_",seq(from = 0.5, to = 8.5,by = 0.5)))
# A tibble: 1,005 x 18
   Date                wrt_0.5 wrt_1 wrt_1.5 wrt_2 wrt_2.5 wrt_3 wrt_3.5 wrt_4 wrt_4.5 wrt_5 wrt_5.5 wrt_6 wrt_6.5 wrt_7 wrt_7.5 wrt_8 wrt_8.5
   <dttm>                <dbl> <dbl>   <dbl> <dbl>   <dbl> <dbl>   <dbl> <dbl>   <dbl> <dbl>   <dbl> <dbl>   <dbl> <dbl>   <dbl> <dbl>   <dbl>
 1 2021-05-06 12:00:00      NA  10.1    NA    10.1    10.1  10.1    10.1  NA      10.1  10.1    NA    10.1    10.1  NA     10.1  10.1    10.1 
 2 2021-05-06 13:00:00      NA  10.2    10.2  10.2    10.2  10.2    10.1  10.2    10.1  10.1    10.1  10.1    10.1  10.1   10.1  10.1    10.1 
 3 2021-05-06 14:00:00      NA  10.2    10.2  10.2    10.2  10.2    10.2  10.2    10.2  10.2    10.2  10.2    10.2  10.2   10.2  10.2    10.2 
 4 2021-05-06 15:00:00      NA  10.4    10.4  10.4    10.4  10.3    10.3  10.3    10.3  10.3    10.3  10.3    10.3  10.3   10.3  10.3    10.2 
 5 2021-05-06 16:00:00      NA  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4   10.3  10.3    10.3 
 6 2021-05-06 17:00:00      NA  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4   10.4  10.3    10.3 
 7 2021-05-06 18:00:00      NA  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4   10.4  10.4     9.95
 8 2021-05-06 19:00:00      NA  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4   10.4  10.1     9.69
 9 2021-05-06 20:00:00      NA  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4   10.4   9.8     9.53
10 2021-05-06 21:00:00      NA  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.4    10.4  10.2    9.82  9.63    9.57
# … with 995 more rows

Upvotes: 1

Related Questions