Create multiple dataframes based on unique values in a column

Question

Hi I am trying to subset the data to state specific data frames and subsequently convert them to XTS objects.

Here is the code I am using.

library(tidyverse)

us <- read_csv(url("https://covidtracking.com/api/v1/states/daily.csv")) # pulling detailed state level data from covidtracking project website
us <- select(us, date, state, cases = positive, hosp = hospitalizedCumulative, icu = inIcuCumulative, death) # selecting columns of interest
head(us)

AK <- filter(us, state == "AK") 
AK <- xts(AK[,c(-1,-2)], order.by = strptime(AK$date, format = "%Y%m%d")) %>% na.fill(0)

Instead of doing this process manually for all 50 states one by one, is there an easier way to do this automatically by doing something like this?

state <- unique(us$state)

x <- function(x) {
  x <- filter(us, state == x)
  x <- xts(x[,c(-1,-2)], order.by = strptime(x$date, format = "%Y%m%d")) %>% na.fill(0)
}

Running the function gives me nothing or an error.

Parfait · Accepted Answer · 2020-07-08 15:37:10Z

0

Consider by (object-oriented wrapper to tapply) which subsets a data frame by one or more factors in a column and (unlike split) passes subsets into a defined method. The return of by is a list of whatever method outputs equal to the number of unique levels of factor(s).

build_xts <- function(x) {
  xts(x[,c(-1,-2)], order.by = strptime(x$date, format = "%Y%m%d")) %>% na.fill(0)
}

xts_list <- by(us, us$state, build_xts)

xts_list$AK
...
xts_list$WY

answered Jul 8, 2020 at 15:37

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Duck · Accepted Answer · 2020-07-08 15:47:23Z

0

You can try this:

#List
List <- split(us,us$state)
#Process
process <- function(x)
{
  x[is.na(x)]<-0
  y <- xts::xts(x[,c(-1,-2)], order.by = strptime(x$date, format = "%Y%m%d"))
  return(y)
}

List2 <- lapply(List,process)

answered Jul 8, 2020 at 15:47

Duck

39.6k14 gold badges47 silver badges88 bronze badges

Comments

user8701090 · Accepted Answer · 2020-07-08 20:57:23Z

This approach creates a column of xts objects created from each state's data. It's kind of like group_by and summarize but the output is a list of objects instead of an atomic vector. I chose to use tidyverse verbs for subsetting, but you could use [[ as well.

You could also look into tibbletime/tsibble to stay in the tidyverse. Also check out the purrr package for more information on the map family of functions.

library(tidyverse)
library(lubridate)
library(xts)

# pulling detailed state level data from covidtracking project website
us <- read_csv(url("https://covidtracking.com/api/v1/states/daily.csv")) %>%
    select(date,
           state,
           cases = positive,
           hosp = hospitalizedCumulative,
           icu = inIcuCumulative,
           death) 

# Create a function that will take a tibble and return an XTS object
build_xts <- function(df) {
    out <- xts(select(df, -c(1:2)),
               order.by = pull(df, "date"))
    return(out)   
}

us %>% 
    mutate(date = strptime(date, format = "%Y%m%d")) %>% 
    # Create a list-column with the relevant data per state
    nest(data = c(date, cases, hosp, icu, death)) %>% 
    
    # Apply the "build_xts" function to each one
    mutate(time_obj = map(
        .x = data,
        .f = build_xts))

#> # A tibble: 56 x 3
#>    state data               time_obj       
#>    <chr> <list>             <list>         
#>  1 AK    <tibble [124 x 5]> <xts [124 x 3]>
#>  2 AL    <tibble [123 x 5]> <xts [123 x 3]>
#>  3 AR    <tibble [124 x 5]> <xts [124 x 3]>
#>  4 AS    <tibble [114 x 5]> <xts [114 x 3]>
#>  5 AZ    <tibble [126 x 5]> <xts [126 x 3]>
#>  6 CA    <tibble [126 x 5]> <xts [126 x 3]>
#>  7 CO    <tibble [125 x 5]> <xts [125 x 3]>
#>  8 CT    <tibble [123 x 5]> <xts [123 x 3]>
#>  9 DC    <tibble [125 x 5]> <xts [125 x 3]>
#> 10 DE    <tibble [124 x 5]> <xts [124 x 3]>
#> # ... with 46 more rows

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

Collectives™ on Stack Overflow

Create multiple dataframes based on unique values in a column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related