reshaping rows of data to two columns

Question

We have data on school districts where the columns are the local-specific information (e.g., free and reduced price lunch %) and the corresponding statewide values.

dat <- tribble(
  ~state.poverty,  ~state.EL,  ~state.disability,  ~state.frpl, ~local.poverty, ~local.frpl, ~local.disability, ~local.EL,
  12.50592, 0.08342419, 0.12321831, 0.4495395, 25.23731, 0.6415712, 0.140739, 0.1469898)

dat
# A tibble: 1 x 8
  state.poverty state.EL state.disability state.frpl local.poverty local.frpl local.disability local.EL
          <dbl>    <dbl>            <dbl>      <dbl>         <dbl>      <dbl>            <dbl>    <dbl>
1          12.5   0.0834            0.123      0.450          25.2      0.642            0.141    0.147

We want to reshape that so that it looks like this.

  demog        state  local
  <chr>        <dbl>  <dbl>
1 poverty    12.5    25.2  
2 EL          0.0834  0.147
3 disability  0.123   0.141
4 frpl        0.450   0.642

It seems like something that pivot_longer should be able to handle, but I haven't had much success so far. Any suggestions?

Does this answer your question? [Gather multiple sets of columns](https://stackoverflow.com/questions/25925556/gather-multiple-sets-of-columns) — camille, Jul 29 '21 at 19:56

score 6 · Accepted Answer · answered Mar 02 '21 at 19:49

We can use pivot_longer

library(dplyr)
library(tidyr)
dat %>% 
  pivot_longer(cols = everything(), 
      names_to = c(".value", "demog"), names_sep = "\\.")

-output

# A tibble: 4 x 3
#  demog        state  local
#  <chr>        <dbl>  <dbl>
#1 poverty    12.5    25.2  
#2 EL          0.0834  0.147
#3 disability  0.123   0.141
#4 frpl        0.450   0.642

score 3 · Answer 2 · answered Mar 02 '21 at 21:09

A base R option using reshape

reshape(
  dat,
  direction = "long",
  varying = 1:ncol(dat)
)

gives

# A tibble: 4 x 4
  time         state  local    id
  <chr>        <dbl>  <dbl> <int>
1 poverty    12.5    25.2       1
2 EL          0.0834  0.642     1
3 disability  0.123   0.141     1
4 frpl        0.450   0.147     1

reshaping rows of data to two columns

2 Answers2