How to turn a table with columns with days and rows with months into a regular time series?

Question

I have an excel data table that was organized in a strange way. Columns are months and rows are days. In the file, after 31 days, resumes the account of the day. In this case, begins another year. I wanted to know how I can turn this data into a regular time series. Knowing that the series starts in 1980, it has how to organize?

Thanks in advance.

dice

read.table(text = "DIA;JAN;FEV;MAR;ABR;MAI;JUN;JUL;AGO;SET;OUT;NOV;DEZ
1;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
2;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
3;NA;NA;.0;.0;.0;.0;4.4;.0;.0;.0;.0;.0  
4;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
5;NA;NA;.0;.0;.0;.0;.0;1.9;.0;.0;.0;.0  
6;NA;NA;.0;.0;.0;3.4;15.4;.0;.0;.0;.0;.0    
7;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
8;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
9;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
10;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
11;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
12;NA;NA;.0;1.9;.0;.0;.0;.0;.0;.0;.0;.0 
13;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
14;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
15;NA;NA;.0;.0;.0;7.0;.0;.0;.0;.0;.0;.0 
16;NA;NA;.0;.0;.0;.0;2.2;.0;.0;.0;.0;.0 
17;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
18;NA;NA;.0;.0;.0;.0;1.3;.0;.0;.0;.0;.0 
19;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
20;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
21;NA;NA;.0;.0;2.5;.0;1.7;.0;.0;.0;.0;.0    
22;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
23;NA;NA;.0;.0;.0;6.8;.0;.0;.0;.0;17.7;.0   
24;NA;NA;.0;.0;.0;2.4;.0;3.1;.0;.0;.0;.0    
25;NA;NA;.0;.0;.0;2.1;.0;4.0;.0;.0;.0;.0    
26;NA;NA;.0;.0;.0;.0;.0;2.6;.0;.0;.0;.0 
27;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
28;NA;NA;.0;.0;1.8;.0;.0;.0;.0;.0;.0;.0 
29;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
30;NA;NA;12.5;.0;.0;.0;.0;.0;.0;.0;.0;.0    
31;NA;NA;.0;NA;.0;NA;.0;.0;NA;.0;NA;.0  
1;25.8;.0;.0;.0;.0;.0;7.2;.0;.0;.0;.0;.0    
2;6.5;17.2;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0    
3;.0;.0;60.0;3.5;.0;.0;.0;.0;.0;.0;3.5;.0
4;1.8;.0;10.1;.0;.0;11.5;.0;.0;.0;.0;.0;.0  
5;.0;.0;10.6;.0;.0;.0;.0;.0;.0;.0;.0;.0 
6;.0;.0;21.7;.0;.0;.0;.0;.0;.0;.0;.0;.0 
7;.0;.0;28.8;3.4;.0;.0;.0;.0;.0;.0;.0;.0    
8;.0;6.8;59.8;.0;4.6;.0;.0;.0;.0;.0;.0;.0   
9;.0;.0;13.6;.0;.0;.0;.0;.0;.0;.0;.0;.0 
10;.0;.0;19.1;.0;.0;.0;.0;6.5;.0;.0;.0;.0", header = TRUE, sep = ";")

Answer 1

First let’s create an object dados with the raw table data.

dados <- read.table(text = "DIA;JAN;FEV;MAR;ABR;MAI;JUN;JUL;AGO;SET;OUT;NOV;DEZ
1;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
2;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
3;NA;NA;.0;.0;.0;.0;4.4;.0;.0;.0;.0;.0  
4;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
5;NA;NA;.0;.0;.0;.0;.0;1.9;.0;.0;.0;.0  
6;NA;NA;.0;.0;.0;3.4;15.4;.0;.0;.0;.0;.0    
7;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
8;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
9;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0   
10;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
11;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
12;NA;NA;.0;1.9;.0;.0;.0;.0;.0;.0;.0;.0 
13;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
14;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
15;NA;NA;.0;.0;.0;7.0;.0;.0;.0;.0;.0;.0 
16;NA;NA;.0;.0;.0;.0;2.2;.0;.0;.0;.0;.0 
17;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
18;NA;NA;.0;.0;.0;.0;1.3;.0;.0;.0;.0;.0 
19;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
20;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
21;NA;NA;.0;.0;2.5;.0;1.7;.0;.0;.0;.0;.0    
22;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
23;NA;NA;.0;.0;.0;6.8;.0;.0;.0;.0;17.7;.0   
24;NA;NA;.0;.0;.0;2.4;.0;3.1;.0;.0;.0;.0    
25;NA;NA;.0;.0;.0;2.1;.0;4.0;.0;.0;.0;.0    
26;NA;NA;.0;.0;.0;.0;.0;2.6;.0;.0;.0;.0 
27;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
28;NA;NA;.0;.0;1.8;.0;.0;.0;.0;.0;.0;.0 
29;NA;NA;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0  
30;NA;NA;12.5;.0;.0;.0;.0;.0;.0;.0;.0;.0    
31;NA;NA;.0;NA;.0;NA;.0;.0;NA;.0;NA;.0  
1;25.8;.0;.0;.0;.0;.0;7.2;.0;.0;.0;.0;.0    
2;6.5;17.2;.0;.0;.0;.0;.0;.0;.0;.0;.0;.0    
3;.0;.0;60.0;3.5;.0;.0;.0;.0;.0;.0;3.5;.0
4;1.8;.0;10.1;.0;.0;11.5;.0;.0;.0;.0;.0;.0  
5;.0;.0;10.6;.0;.0;.0;.0;.0;.0;.0;.0;.0 
6;.0;.0;21.7;.0;.0;.0;.0;.0;.0;.0;.0;.0 
7;.0;.0;28.8;3.4;.0;.0;.0;.0;.0;.0;.0;.0    
8;.0;6.8;59.8;.0;4.6;.0;.0;.0;.0;.0;.0;.0   
9;.0;.0;13.6;.0;.0;.0;.0;.0;.0;.0;.0;.0 
10;.0;.0;19.1;.0;.0;.0;.0;6.5;.0;.0;.0;.0", header = TRUE, sep = ";")

And then we can proceed to manipulate the data to achieve the desired goal.

library(tidyverse)
dados_organizados <- dados %>% 
  gather(mes, valor, -DIA) %>% 
  group_by(DIA, mes) %>% 
  mutate(ano = seq_along(valor))

The solution went through two stages:

1. Change the format of the wide for long (see more here);
2. Create a variable with the year

Part 1 doesn’t have much to explain because it’s a standard transformation. The function documentation and the question linked above should help if you need more information about this step.

As for part 2, I created a new variable that counts the number of times a set of days and months appear, as well as the year.

The code below can be used to view year 2 information, which matches the original table information.

dados_organizados %>% 
  filter(ano == 2)
#> # A tibble: 120 x 4
#> # Groups:   DIA, mes [120]
#>      DIA mes   valor   ano
#>    <int> <chr> <dbl> <int>
#>  1     1 JAN    25.8     2
#>  2     2 JAN     6.5     2
#>  3     3 JAN     0       2
#>  4     4 JAN     1.8     2
#>  5     5 JAN     0       2
#>  6     6 JAN     0       2
#>  7     7 JAN     0       2
#>  8     8 JAN     0       2
#>  9     9 JAN     0       2
#> 10    10 JAN     0       2
#> # … with 110 more rows

^{Created on 2020-04-23 by the reprex package (v0.3.0)}