CODEWITHSUNDEEP
rdplyrtidyrpurrr
lethalSinger
lethalSinger

Reputation: 616

Split, reshape, bind stacked wide data using tidyverse in single pipe

I have a single dataframe which has stacked wide data for multiple districts and villages, where there are votes recorded for political parties. There are different parties standing in each district:

df_in <- data.frame(
  X1 = c(rep("District1", 3), rep("District2", 3)),
  X2 = c(rep(c("", "Village1", "Village2"), 2)),
  X3 = c("Party1", "30", "11", "Party1", "2", "59"),
  X4 = c("Party2", "55", "42", "Party2", "66", "44"),
  X5 = c("", "", "", "Party3", "32", "13"),
  X6 = c("", "", "", "Party4", "99", "75")
)

I would like to end up with a long dataset of votes recorded for each party in each village/district:

df_out <- data.frame(
  X1 = c(rep("District1", 4), rep("District2", 8)),
  X2 = c("Village1", "Village1", "Village2", "Village2", "Village1", "Village1", "Village1", "Village1", "Village2", "Village2", "Village2", "Village2"),
  X3 = c(
    rep(c("Party1", "Party2"), 2), 
    rep(c("Party1", "Party2", "Party3", "Party4"), 2)
    ),
  X4 = c(30, 55, 11, 42, 2, 66, 32, 99, 59, 44, 13, 75)
)

I would like to do get from the input to the output in a single pipe. I've been working on something like the following set up, but without success so far:

df_out <- df_in %>% 
  split(.$X1) %>% 
  map() %>% 
  gather() %>% 
  bind_rows()

Is this on the right lines?

Upvotes: 3

Views: 99

Answers (3)

DuckPyjamas
DuckPyjamas

Reputation: 1659

Even shorter and with proper column names.

library(tidyr)
library(dplyr)
library(magrittr)

df_in %>%
    mutate_all(as.character) %>%  # Or set stringsAsFactors = FALSE
    set_names(c("district", "village", paste0("Party", 1:4))) %>% 
    filter(nchar(village) > 0) %>% 
    gather(party, votes, -district, -village) %>% 
    mutate(votes = as.integer(votes) %>% replace_na(0)) %>% 
    arrange(district, village, party) %>% 
    filter(votes > 0)

##     district  village  party votes
## 1  District1 Village1 Party1    30
## 2  District1 Village1 Party2    55
## 3  District1 Village2 Party1    11
## 4  District1 Village2 Party2    42
## 5  District2 Village1 Party1     2
## 6  District2 Village1 Party2    66
## 7  District2 Village1 Party3    32
## 8  District2 Village1 Party4    99
## 9  District2 Village2 Party1    59
## 10 District2 Village2 Party2    44
## 11 District2 Village2 Party3    13
## 12 District2 Village2 Party4    75

Upvotes: 1

akrun
akrun

Reputation: 887148

We can also do this with

library(dplyr)
library(tidyr)
library(hablar)
df_in %>% 
   # group by 'X1'
   group_by(X1) %>%
   # remove the first row
   slice(-1) %>% 
   # ungroup
   ungroup %>%
   # rename the column names with 'Party'
   rename_at(vars(X3:X6), ~ paste0("Party", 1:4)) %>%   
   # change the type of columns    
   retype %>%
   # gather into long format
   gather(X3, X4, Party1:Party4, na.rm = TRUE) %>%
   # arrange if needed
   arrange(X1, X2)
# A tibble: 12 x 4
#   X1        X2       X3        X4
#   <chr>     <chr>    <chr>  <int>
# 1 District1 Village1 Party1    30
# 2 District1 Village1 Party2    55
# 3 District1 Village2 Party1    11
# 4 District1 Village2 Party2    42
# 5 District2 Village1 Party1     2
# 6 District2 Village1 Party2    66
# 7 District2 Village1 Party3    32
# 8 District2 Village1 Party4    99
# 9 District2 Village2 Party1    59
#10 District2 Village2 Party2    44
#11 District2 Village2 Party3    13
#12 District2 Village2 Party4    75

Upvotes: 2

A. Suliman
A. Suliman

Reputation: 13125

 library(tidyverse)
 df_in %>%   
  split(.$X1) %>% 
  map(. %>% gather(key,val,X3:X6) %>% 
        group_by(key) %>% mutate(key1=first(val)) %>% filter(row_number() %in% 2:n() & val!="") %>% 
        ungroup() %>% rename(X4=val, X3=key1) %>% select(X1,X2,X3,X4)) %>% 
 bind_rows()

Upvotes: 1

Related Questions