How to "wrap" rows in R?

user_14397434 0
Viewed 55

I currently have a data set that has all information within one single row (or column if I transpose). The very first items in the data are actually column names:

Country | Population | Country Column One | Country Column 2 | USA | 400 million | USA Column 1 | USA Column 2 | Canada | 38 Million | Canada Column 1 | Canada Column 2 | etc..

I notice that I can just "wrap" and have everything start at a new row once it reaches a new country. How would I go about that? Is there a more efficient way?

data-wrangling dataframe r string
3 Answers
d <- t(matrix(scan(text=string, sep='|', what = "", strip.white = TRUE), 4))

colnames(d) <- d[1,]
data.frame(d[-1,])

  Country  Population Country.Column.One Country.Column.2
1     USA 400 million       USA Column 1     USA Column 2
2  Canada  38 Million    Canada Column 1  Canada Column 2

string <- "Country | Population | Country Column One | Country Column 2 | USA | 400 million | USA Column 1 | USA Column 2 | Canada | 38 Million | Canada Column 1 | Canada Column 2 "
user_83802720

Here is a base R option using read.table + gsub

> read.table(text = gsub("(([^|]+\\|){3}[^|]+)\\|", "\\1\n", s),sep = "|",header = TRUE)
   Country    Population Country.Column.One  Country.Column.2
1     USA   400 million       USA Column 1      USA Column 2
2  Canada    38 Million    Canada Column 1   Canada Column 2

given

s <- "Country | Population | Country Column One | Country Column 2 | USA | 400 million | USA Column 1 | USA Column 2 | Canada | 38 Million | Canada Column 1 | Canada Column 2 "
user_121587570

Here is a custom approach:

  1. We create a tibble separate the rows and pull it as vector

  2. with split we create a list

  3. then we use bind_rows and do pivoting.

library(tidyverse)

my_vec <- as_tibble(string) %>% 
  separate_rows("value", sep = " \\| ") %>%
  pull(value) 

my_list <- split(my_vec, ceiling(seq_along(my_vec) / 4))


bind_rows(my_list) %>% 
  pivot_longer(-`1`) %>% 
  pivot_wider(names_from = `1`, values_from = value) %>% 
  select(-name)

 Country Population  `Country Column One` `Country Column 2`
  <chr>   <chr>       <chr>                <chr>             
1 USA     400 million USA Column 1         "USA Column 2"    
2 Canada  38 Million  Canada Column 1      "Canada Column 2 "
user_133216470
Related