Advice on a loop function to subset data according to variables

Question

I have a dataset that has 453 variables (columns) and 119 observations (rows). It is comprised of 118 health observations for different countries over a number of years. For example, 10 of the 453 variables contain health data from Australia over a 10 year period; 8 of the 453 variables contain health data from Bangladesh over a 8 year period.

I want to subset these 453 variables into their own country-based data frames. The country name and year is in row 1 (e.g. Australia_2013, Australia_2014 etc). Seeing as though there are > 40 countries in this dataset, I would like to create a loop for this.

From what I've read so far, I think I should create a vector list of country names and then write a loop function that subsets data according to the vector list. All of the examples I can find are for subsetting based on rows however.

Can anyone point me in the right direction, or share example code for this?

Much thanks in anticipation

Answer 1

here is a dplyr answer, for version >= 1.0.

I created a small example, and we nest into the data column the different columns. Then since nest_by already created a rowwise grouped we can subset each data for the columns that starts with the country name. We need to convert this to a character. Finally, if needed you can pull the list-column subset to get a list of tibbles that contains the relevant columns. Of note, I think working with in a tidy format (long and not double info encoded (country and year) would be easier.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df <- data.frame(country = c("A", "B", "C"),
                 A_1 = 1:3,
                 A_2 = 3:5,
                 B_1 = 2:4,
                 C_3 = 1:3)
df
#>   country A_1 A_2 B_1 C_3
#> 1       A   1   3   2   1
#> 2       B   2   4   3   2
#> 3       C   3   5   4   3
nest_by(df, country) %>% 
  mutate(subset = list(select(data, starts_with(as.character(country))))) %>% 
  pull(subset)
#> [[1]]
#> # A tibble: 1 x 2
#>     A_1   A_2
#>   <int> <int>
#> 1     1     3
#> 
#> [[2]]
#> # A tibble: 1 x 1
#>     B_1
#>   <int>
#> 1     3
#> 
#> [[3]]
#> # A tibble: 1 x 1
#>     C_3
#>   <int>
#> 1     3

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

Answer 2

Based on your description, I assume your data looks something like this:

country_year <- c("Australia_2013", "Australia_2014", "Bangladesh_2013")
health <- matrix(nrow = 3, ncol = 3, data = runif(9))
dataset <- data.frame(rbind(country_year, health), row.names = NULL, stringsAsFactors = FALSE)

dataset
#                 X1                X2                 X3
#1    Australia_2013    Australia_2014    Bangladesh_2013
#2 0.665947273839265 0.677187719382346  0.716064820764586
#3 0.499680359382182 0.514755881391466  0.178317369660363
#4 0.730102791683748 0.666969108628109 0.0719663293566555

First, move your row 1 (e.g., Australia_2013, Australia_2014 etc.) to the column names, and then apply the loop to create country-based data frames.

library(dplyr)

# move header
dataset2 <- dataset %>% 
    `colnames<-`(dataset[1,]) %>%  # uses row 1 as column names
    slice(-1) %>% # removes row 1 from data
    mutate_all(type.convert) # converts data to appropriate type

# apply loop
for(country in unique(gsub("_\\d+", "", colnames(dataset2)))) {
    assign(country, select(dataset2, starts_with(country))) # makes subsets
}

Regarding the loop,

gsub("_\\d+", "", colnames(dataset2)) extracts the country names by replacing "_[year]" with nothing (i.e., removing it), and the unique() function that is applied extracts one of each country name.

assign(country, select(dataset2, starts_with(country))) creates a variable named after the country and this country variable only contains the columns from dataset2 that start with the country name.

Edit: Responding to Comment

The question in the comment was asking how to add row-wise summaries (e.g., rowSums(), rowMeans()) as new columns in the country-based data frames, while using this for-loop.

Here is one solution that requires minimal changes:

for(country in unique(gsub("_\\d+", "", colnames(dataset2)))) {
    assign(country, 
        select(dataset2, starts_with(country)) %>% # makes subsets
            mutate( # creates new columns
                rowSums = rowSums(select(., starts_with(country))),
                rowMeans = rowMeans(select(., starts_with(country)))
            )
    )
}

mutate() adds new columns to a dataset.

select(., starts_with(country)) selects columns that start with the country name from the current object (represented as . in the function).

Answer 3

First of all, the data structure is not optimal, having 1st row as string means, numbers in all other rows (in each column) are also coded by R as strings. But that's not part of the question.

You cannot create a series of data frames, but you can if they are part of a list (that's what lists are in R for!), with one list element holding single country.

Pure base R approach, solution with a working example:

# example dataset df
data("mtcars")
df <- mtcars
df <- rbind(paste0(sample(letters, ncol(df), replace = TRUE), "_2014"), df)
str(df)

# solution
countries <- substr(df[1, ], 1, nchar(df[1, ]) - 5)
unique_countries <- unique(countries)
df <- rbind.data.frame(countries, df, stringsAsFactors = FALSE)

list_df_per_country <- list()

for (i in seq_along(unique_countries)) {
  list_df_per_country[[i]] <- df[which(df[1, ] == unique_countries[i])]
}

To use the code above, just save your dataframe as df, i.e. df <- your_dataframe, and run lines below the # solution, one by one.