0

I have a following reproducible example data for COVID-19 Confirmed cases. I want to convert the date given in columns to rows by replacing the columns name as "confirmed cases" by each country and Lat. (Sample type is given below)

Lat <- c(15,36,1.2833, 28.1667, 2.5,49.2827,-33.8688,-37.8136,-28.0167,11.55)
country <- c("Thailand","Japan","Singapore","Nepal","Malaysia","Canada","Australia","Australia","Australia","Cambodia")

d1 <- c(2,  2,  0,  0,  0,  0,  0,  0,  0,  0)
d2 <- c(3,  1,  1,  0,  0,  0,  0,  0,  0,  0)
d3 <- c(5,  2,  3,  0,  0,  0,  0,  0,  0,  0)
d4 <- c(7,  2,  3,  1,  3,  0,  0,  0,  0,  0)

confirmed_covid19 <- cbind(Lat,country,d1,d2,d3,d4)

colnames(confirmed_covid19)<- c("Lat","country","1/22/20","1/23/20","1/24/20",  "1/25/20")

This give as follows:

enter image description here

However, I want if we can convert it as

enter image description here

shafi Q
  • 77
  • 8
  • 1
    Does this answer your question? [Reshape multiple value columns to wide format](https://stackoverflow.com/questions/11608167/reshape-multiple-value-columns-to-wide-format). "Data arrangement" can mean a few different things, your question specifically addresses data layout/format. Here is another similar question: [program to pivot data in R](https://stackoverflow.com/questions/36255567/program-to-pivot-data-in-r) – OTStats Mar 18 '20 at 15:24
  • Thanks, it does not answer the question directly however, worth exploring different other ways. Thank you once again! – shafi Q Mar 18 '20 at 16:04
  • 1
    @WolfgangFahl, please don't add meta tags to questions. See: https://meta.stackoverflow.com/questions/394683/request-to-blacklist-the-covid-19-tag – Cerbrus Mar 24 '20 at 15:25
  • Thanks! updated the title. – shafi Q Mar 24 '20 at 23:18

1 Answers1

1

Your question is more focused on data structure. Based off your desired result, you're looking to "pivot" your data. You can do so using dplyr:

library(dplyr)

as.data.frame(confirmed_covid19) %>% 
  pivot_longer(cols = c("1/22/20","1/23/20","1/24/20",  "1/25/20"), names_to = "date", values_to = "confirmed_cases")

# # A tibble: 40 x 4
#    Lat    country   date    confirmed_cases
#    <fct>  <fct>     <chr>   <fct>          
#  1 15     Thailand  1/22/20 2              
#  2 15     Thailand  1/23/20 3              
#  3 15     Thailand  1/24/20 5              
#  4 15     Thailand  1/25/20 7              
#  5 36     Japan     1/22/20 2              
#  6 36     Japan     1/23/20 1              
#  7 36     Japan     1/24/20 2              
#  8 36     Japan     1/25/20 2              
#  9 1.2833 Singapore 1/22/20 0              
# 10 1.2833 Singapore 1/23/20 1
OTStats
  • 1,820
  • 1
  • 13
  • 22
  • Thank you for your answer. Can we do it in a way so that we do not have to write column names manually, such as cols = c("1/22/20", "1/23/20", "1/24/20"). If will be very time consuming if the column is very large such 400. – shafi Q Mar 18 '20 at 15:47