0

I have a data frame with hundred columns. The first four variables have named the rest of the date. I do not know how to use pivot_longer for it. Is any way to use those dates as a variables name?

sample
                 Province/State      Country/Region       Lat      Long 4/1/2021 4/11/2021
1                                       Afghanistan  33.93911  67.70995    56517     57160
2                                           Albania  41.15330  20.16830   125506    128393
3                                           Algeria  28.03390   1.65960   117304    118516
4                                           Andorra  42.50630   1.52180    12053     12545
5                                            Angola -11.20270  17.87390    22399     23457
6                               Antigua and Barbuda  17.06080 -61.79640     1147      1198
7                                         Argentina -38.41610 -63.61670  2363251   2532562
8                                           Armenia  40.06910  45.03820   193736    202817
9  Australian Capital Territory           Australia -35.47350 149.01240      123       123
10              New South Wales           Australia -33.86880 151.20930     5296      5339

this what I want:

 Province.State Country.Region      Lat     Long       Date Number
1              NA    Afghanistan 33.93911 67.70995 2021-04-08  56943
2              NA    Afghanistan 33.93911 67.70995 2021-04-09  57019
3              NA    Afghanistan 33.93911 67.70995 2021-04-10  57144
4              NA    Afghanistan 33.93911 67.70995 2021-04-11  57160
5              NA    Afghanistan 33.93911 67.70995 2021-04-12  57242
6              NA        Albania 41.15330 20.16830 2021-04-08 127509
7              NA        Albania 41.15330 20.16830 2021-04-09 127795
8              NA        Albania 41.15330 20.16830 2021-04-10 128155
9              NA        Albania 41.15330 20.16830 2021-04-11 128393
10             NA        Albania 41.15330 20.16830 2021-04-12 128518
11             NA        Algeria 28.03390  1.65960 2021-04-08 118116
12             NA        Algeria 28.03390  1.65960 2021-04-09 118251
13             NA        Algeria 28.03390  1.65960 2021-04-10 118378
14             NA        Algeria 28.03390  1.65960 2021-04-11 118516
15             NA        Algeria 28.03390  1.65960 2021-04-12 118645
16             NA        Andorra 42.50630  1.52180 2021-04-08  12409
17             NA        Andorra 42.50630  1.52180 2021-04-09  12456
18             NA        Andorra 42.50630  1.52180 2021-04-10  12497
19             NA        Andorra 42.50630  1.52180 2021-04-11  12545
20             NA        Andorra 42.50630  1.52180 2021-04-12  12581

This the reproducible of it.

dput(sample)
structure(list(`Province/State` = c("", "", "", "", "", "", "", 
"", "Australian Capital Territory", "New South Wales"), `Country/Region` = c("Afghanistan", 
"Albania", "Algeria", "Andorra", "Angola", "Antigua and Barbuda", 
"Argentina", "Armenia", "Australia", "Australia"), Lat = c(33.93911, 
41.1533, 28.0339, 42.5063, -11.2027, 17.0608, -38.4161, 40.0691, 
-35.4735, -33.8688), Long = c(67.709953, 20.1683, 1.6596, 1.5218, 
17.8739, -61.7964, -63.6167, 45.0382, 149.0124, 151.2093), `4/1/2021` = c(56517L, 
125506L, 117304L, 12053L, 22399L, 1147L, 2363251L, 193736L, 123L, 
5296L), `4/11/2021` = c(57160L, 128393L, 118516L, 12545L, 23457L, 
1198L, 2532562L, 202817L, 123L, 5339L)), row.names = c(NA, 10L
), class = "data.frame")
Kian
  • 149
  • 6
  • 5
    You can do `sample %>% pivot_longer(matches("\\d+/\\d+/\\d{4}"), names_to = "Date", values_to = "Number")`; or `sample %>% pivot_longer(-(1:4), names_to = "Date", values_to = "Number")` – Maurits Evers Apr 16 '21 at 03:35
  • After @Maurits Evers answer you might want to change `Date` to date column by continuing the pipeline `%>% mutate(Date = lubridate::dmy(Date))` – det Apr 16 '21 at 06:05

0 Answers0