0

I have a lot of tables with climate observations in daily frequency. As you can see all columns of months have 31 days. I wanna organize this data correctly, excluding all false days and transform in a regular time series.

data view: enter image description here

dput(head(data,31)):

structure(list(NA. = 1:31, JAN = c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
), FEV = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), MAR = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 12.5, 0), ABR = c(0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 1.9, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, NA), MAI = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 2.5, 0, 0, 0, 0, 0, 0, 1.8, 0, 0, 0), JUN = c(0, 
0, 0, 0, 0, 3.4, 0, 0, 0, 0, 0, 0, 0, 0, 7, 0, 0, 0, 0, 0, 0, 
0, 6.8, 2.4, 2.1, 0, 0, 0, 0, 0, NA), JUL = c(0, 0, 4.4, 0, 0, 
15.4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2.2, 0, 1.3, 0, 0, 1.7, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0), AGO = c(0, 0, 0, 0, 1.9, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3.1, 4, 2.6, 0, 0, 
0, 0, 0), SET = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NA), OUT = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0), NOV = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 17.7, 0, 0, 0, 0, 0, 0, 
0, NA), DEZ = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), row.names = c(NA, 
31L), class = "data.frame")

I wanna like this example:

enter image description here

original data: https://www.dropbox.com/s/0xizr6fhbriieds/data.csv?dl=0

Please, anyone help-me. Is for my scientific initiation.

Ian Campbell
  • 23,484
  • 14
  • 36
  • 57
Rodrigo Junior
  • 56
  • 7

1 Answers1

0

It's not totally clear from your question how you want to filter the data, but to convert to long form and remove NAs, one approach would be with dplyr, tidyr and lubridate.

I'm using data.table to load your data because for some reason it's semicolon separated even though the file extension is .csv.

You could probably get away with not renaming the months since your local probably accepts Spanish month abbreviations. 

libary(data.table)
data <- fread("data.csv")
names(data) <- c("Day",month.abb)

library(dplyr)
library(tidyr)
library(lubridate)
Year <- 2000
data %>% 
  pivot_longer(-Day,names_to = "Month") %>%
  mutate(Date = dmy(paste(Day,Month,Year))) %>%
  filter(!is.na(value)) %>%
  select(Date,value)
#   Date       value
#   <date>     <dbl>
# 1 2000-07-03   4.4
# 2 2000-08-05   1.9
# 3 2000-06-06   3.4
# 4 2000-07-06  15.4
# 5 2000-04-12   1.9
# 6 2000-06-15   7  
# 7 2000-07-16   2.2
# 8 2000-07-18   1.3
# 9 2000-05-21   2.5
#10 2000-07-21   1.7
Ian Campbell
  • 23,484
  • 14
  • 36
  • 57
  • Hey, brother. Do you help-me again? How i can make the year variate? In this code, the year is constant. May i have a time series with year start in 2000 and varying for next year? eg: 2000, 2001, 2002, 2003... – Rodrigo Junior Apr 24 '20 at 01:09
  • How do you know what year the data is from? The starting data in your question doesn't have the year anywhere actually in it. I just chose 2000 because that's what your desired output had. – Ian Campbell Apr 24 '20 at 13:24