CODEWITHSUNDEEP
rdplyrtidyrreshape2
ds_user
ds_user

Reputation: 2179

Merge immediate next rows together - R

This is how my df looks like,

Region  Dummy value1 value2
Mangonui  NA   NA     NA
Sales     NA   9      6
Kaitaia   NA   NA     NA
Sales     NA   16     1
Whangaroa NA   NA     NA
Sales     NA   2      2

Steps to regenerate this,

 structure(list(Region = c("Mangonui", "Sales", "Kaitaia", 
"Sales", "Whangaroa", "Sales"), Dummy = c(NA, 
NA, NA, NA, NA, NA), Dweling_values = c(NA, "9", NA, "16", NA, 
"2"), Section_values = c(NA, "6", NA, "1", NA, "2")), .Names = c("Region", 
"Dummy", "value1", "value2"), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

How can i merge the two rows together, so that i can get the sales against the region names? So the output should look like,

Region  Dummy value1 value2
Mangonui  NA   9      6
Kaitaia   NA   16     1
Whangaroa NA   2      2

Upvotes: 1

Views: 178

Answers (3)

Joseph Wood
Joseph Wood

Reputation: 7597

Simple rearranging in base R

myNew <- cbind(df$Region[seq.int(1,nrow(df),2)], df[seq.int(2,nrow(df),2), 2:4])
names(myNew) <- names(df)

myNew
     Region Dummy value1 value2
2  Mangonui    NA      9      6
4   Kaitaia    NA     16      1
6 Whangaroa    NA      2      2


UPDATE
By far the most elegant solution is given by @thelatemail

cbind(df[1][c(TRUE,FALSE),,drop=FALSE], df[-1][c(FALSE,TRUE),])
     Region Dummy value1 value2
1  Mangonui    NA      9      6
3   Kaitaia    NA     16      1
5 Whangaroa    NA      2      2

Upvotes: 3

www
www

Reputation: 39154

A solution using dplyr and tidyr. The idea is to use recode to replace Sales with NA, use fill to impute those NA based on the previous rows, and then use filter_at to filter the rows with any non-NA values in other columns.

library(dplyr)
library(tidyr)

dt2 <- dt %>%
  mutate(Region = recode(Region, `Sales` = NA_character_)) %>%
  fill(Region) %>%
  filter_at(vars(-Region), any_vars(!is.na(.)))
dt2
# # A tibble: 3 x 4
#      Region Dummy value1 value2
#       <chr> <lgl>  <chr>  <chr>
# 1  Mangonui    NA      9      6
# 2   Kaitaia    NA     16      1
# 3 Whangaroa    NA      2      2

Upvotes: 2

MrMr
MrMr

Reputation: 108

First you need to find and scold whoever gave you the data in this form. Tell them you won't be friends with them if they keep doing this. Then, just use some simple base R functions:

# generate indices for the sales and region rows
sales_rows <- seq(2, nrow(df), by = 2)
region_rows <- seq(1, nrow(df), by = 2)

# subset to create the df you really want
sales_df <- df[sales_rows, ]
# use just the names from the region rows
regions <- df[region_rows, "Region"]
sales_df$Region <- regions

# > sales_df
#      Region Dummy value1 value2
# 2  Mangonui    NA      9      6
# 4   Kaitaia    NA     16      1
# 6 Whangaroa    NA      2      2

Upvotes: 2

Related Questions