0

I have the following dataframe:

df = structure(list(Date...1 = structure(c(1577836800, 1577923200, 
1578009600, 1578268800, 1578355200, 1578441600), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), FCGDP2021.2020 = c(1.2, 1.2, 1.2, 
1.2, 1.2, 1.2), Date...3 = structure(c(1546300800, 1546387200, 
1546473600, 1546560000, 1546819200, 1546905600), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), FCGDP2020.2019 = c(1.5, 1.5, 1.5, 
1.5, 1.5, 1.5), Date...5 = structure(c(1514764800, 1514851200, 
1514937600, 1515024000, 1515110400, 1515369600), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), FCGDP2019.2018 = c(1.8, 1.8, 1.8, 
1.8, 1.8, 1.8)), row.names = c(NA, 6L), class = "data.frame")


   Date...1 FCGDP2021.2020   Date...3 FCGDP2020.2019
1 2020-01-01            1.2 2019-01-01            1.5
2 2020-01-02            1.2 2019-01-02            1.5
3 2020-01-03            1.2 2019-01-03            1.5
4 2020-01-06            1.2 2019-01-04            1.5
5 2020-01-07            1.2 2019-01-07            1.5
6 2020-01-08            1.2 2019-01-08            1.5
    Date...5 FCGDP2019.2018 
1 2018-01-01            1.8      
2 2018-01-02            1.8      
3 2018-01-03            1.8      
4 2018-01-04            1.8      
5 2018-01-05            1.8      
6 2018-01-08            1.8

What I want to get is this:

    Date         Data   
1 2020-01-01     1.2            
2 2020-01-02     1.2             
3 2020-01-03     1.2             
4 2020-01-06     1.2             
5 2020-01-07     1.2            
6 2020-01-08     1.2             
7 2019-01-01     1.5
8 2019-01-02     1.5
9 2019-01-03     1.5
10 2019-01-04    1.5
11 2019-01-07    1.5
12 2019-01-08    1.5
13 2018-01-01    1.8        
14 2018-01-02    1.8      
15 2018-01-03    1.8      
16 2018-01-04    1.8      
17 2018-01-05    1.8      
18 2018-01-08    1.8

Combining by row the columns that starts with "Date" and "FC" into two single columns. I tried, without success, melt and pivot_longer.

Can anyone help me with this?

Thanks!

Rollo99
  • 1,601
  • 7
  • 15

1 Answers1

1

With pivot_longer you can try :

tidyr::pivot_longer(df, 
                    cols = everything(), 
                    names_to = '.value', 
                    names_pattern = '(Date|FCGDP)')

# A tibble: 18 x 2
#   Date                FCGDP
#   <dttm>              <dbl>
# 1 2020-01-01 00:00:00   1.2
# 2 2019-01-01 00:00:00   1.5
# 3 2018-01-01 00:00:00   1.8
# 4 2020-01-02 00:00:00   1.2
# 5 2019-01-02 00:00:00   1.5
# 6 2018-01-02 00:00:00   1.8
# 7 2020-01-03 00:00:00   1.2
# 8 2019-01-03 00:00:00   1.5
# 9 2018-01-03 00:00:00   1.8
#10 2020-01-06 00:00:00   1.2
#11 2019-01-04 00:00:00   1.5
#12 2018-01-04 00:00:00   1.8
#13 2020-01-07 00:00:00   1.2
#14 2019-01-07 00:00:00   1.5
#15 2018-01-05 00:00:00   1.8
#16 2020-01-08 00:00:00   1.2
#17 2019-01-08 00:00:00   1.5
#18 2018-01-08 00:00:00   1.8

If the same type of data are in alternate columns you can also do :

data.frame(Date = do.call(c, df[c(TRUE, FALSE)]), 
           Data = unlist(df[c(FALSE, TRUE)]), row.names = NULL)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213