1

I have trouble to reorganize a data frame according to the date and station. Currently I have this:

 STATION    Latitude    Longitude   01/05/2007  01/06/2007  01/07/2007  01/08/2007  01/09/2007   
1001        X1           Y1         12,27       12,33       6,26        2,75        1,15         
1002        X2           Y2         12,14       5,99        3,51        2,98        1,92         
1003        X3           Y3         0           0           0           0           0

And I would like something like this:

    Date        Station Latitude    Longtitude  Values
01/05/2007      1001    X1          Y1          12,27
01/06/2007      1001    X1          Y1          12,33
01/07/2007      1001    X1          Y1          6,26
01/08/2007      1001    X1          Y1          2,75
01/09/2007      1001    X1          Y1          1,15
01/05/2007      1002    X2          Y2          12,14
01/06/2007      1002    X2          Y2          5,99
01/07/2007      1002    X2          Y2          3,51
01/08/2007      1002    X2          Y2          2,98
01/09/2007      1002    X2          Y2          1,92
01/05/2007      1003    X3          Y3          0
01/06/2007      1003    X3          Y3          0
01/07/2007      1003    X3          Y3          0
01/08/2007      1003    X3          Y3          0
01/09/2007      1003    X3          Y3          0

Thanks!!!

EDIT It seems that when there are too much columns, there is a problem in keeping the head correct

DATA SET EXAMPLE

Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
dudpant
  • 25
  • 4

2 Answers2

1

The question is probably a duplicate of Reshaping data.frame from wide to long format.

Here is a tidyverse solution.

library(dplyr)
library(tidyr)

df1 %>%
  pivot_longer(
    cols = 4:8,
    names_to = "Date",
    values_to = "Values"
  ) %>%
  relocate(Date, .before = STATION)

In pivot_longer, argument cols can be changed to the regular expression matching function matches.

cols = matches("^\\d+")
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
  • Thanks a lot! Indeed it look like the Reshaping data.frame from wide to long format. Thanks for the answer, when I try it with a large number of rows I have some trouble. It seems to keep the date in head. – dudpant Sep 08 '21 at 12:46
  • @dudpant Can you post a data set more representative of the problem you still have? One that reproduces the problem or code to create it? – Rui Barradas Sep 08 '21 at 13:07
  • Thanks for your help, I add an edit to the post with csv file to download on filedropper. When I use this data the head are kept and just copied – dudpant Sep 08 '21 at 13:48
0

Here is another solution:

 library(reshape2)
 dummy = data.frame('A'=c(1,2,3),'x'=c(1,2,3),'y'=c(1,2,3),
                    'date1'=c(5,6,7),
                     'date2'=c(8,9,0),'date3'=c(10,11,12))

I used this summy data set.

    A x y   date1 date2 date3
  1 1 1 1     5     8    10
  2 2 2 2     6     9    11
  3 3 3 3     7     0    12

And then using reshape:

 l = reshape(dummy,varying = setdiff(names(dummy),c('A','x','y')),
             times =setdiff(names(dummy),c('A','x','y'))  ,
             direction='long', v.names = "values")

this will be the results:

  A x y  time values id
  1 1 1 date1      5  1
  2 2 2 date1      6  2
  3 3 3 date1      7  3
  1 1 1 date2      8  1
  2 2 2 date2      9  2
  3 3 3 date2      0  3
  1 1 1 date3     10  1
  2 2 2 date3     11  2
  3 3 3 date3     12  3
Pedro
  • 150
  • 10
  • Thanks! It's useful. But I wanted to used it as well on a large number of date. :) – dudpant Sep 08 '21 at 12:45
  • Ok @dudpant then you have 2 options, if your non date columns are not that many, then use setdiff(names('your data frame'),'STATION','Latitude','Longitude') in reshape to specify, 'varying' and 'times' as I have done on my edited answer. Otherwise you can use str_detect to find all the columns with date in them and replace that with varying and times. – Pedro Sep 08 '21 at 13:46