Combining Pivoted Rows in R by Common Value

Combining pivoted rows in R by common value

Actually, if you are able to get back to the data before the pivot, tidyr::spread will do a beautiful job.

Name <- c("Jack", "Jack","Sally", "Sally", "Adam", "Adam")
Visit <- c("week1", "week1", "week5", "week5", "week2", "week2")
Itenary <- rep(c("Arrival", "Departure"), 3)
Time <- c("8:00", "8:30", "9:00", "9:30", "2:00", "2:30")

df <- data.frame(Name, Visit, Itenary, Time)

df

Name Visit Itenary Time
1 Jack week1 Arrival 8:00
2 Jack week1 Departure 8:30
3 Sally week5 Arrival 9:00
4 Sally week5 Departure 9:30
5 Adam week2 Arrival 2:00
6 Adam week2 Departure 2:30

df %>%
spread(key = Itenary, value = Time)

Name Visit Arrival Departure
1 Adam week2 2:00 2:30
2 Jack week1 8:00 8:30
3 Sally week5 9:00 9:30

Combining rows by index in R

We cangroup_by the 'd', 'init_cont', 'family' and then do a summarise_all to remove all the NA elements in the columns 1:3

library(dplyr)
df1 %>%
group_by_at(names(.)[1:3]) %>%
summarise_all(na.omit)
#Or
#summarise_all(funs(.[!is.na(.)]))
# A tibble: 3 x 6
# Groups: d, init_cont [?]
# d init_cont family `1` `2` `3`
# <int> <chr> <chr> <int> <int> <int>
#1 1 I C 1 4 3
#2 2 I D 2 1 4
#3 3 K C 3 4 1

Combine rows by group with differing NAs in each row

Is this what you want ? zoo+dplyr also check the link here

df %>%
group_by(groupid) %>%
mutate_all(funs(na.locf(., na.rm = FALSE, fromLast = FALSE)))%>%filter(row_number()==n())

# A tibble: 1 x 5
# Groups: groupid [1]
groupid col1 col2 col3 col4
<int> <int> <chr> <int> <int>
1 1 0 n 2 2

EDIT1

without the filter , will give back whole dataframe.

    df %>%
group_by(groupid) %>%
mutate_all(funs(na.locf(., na.rm = FALSE, fromLast = FALSE)))

# A tibble: 2 x 5
# Groups: groupid [1]
groupid col1 col2 col3 col4
<int> <int> <chr> <int> <int>
1 1 0 n NA 2
2 1 0 n 2 2

filter here, just slice the last one, na.locf will carry on the previous not NA value, which mean the last row in your group is what you want.

Also base on @ thelatemail recommended. you can do the following , give back the same answer.

df %>% group_by(groupid) %>% summarise_all(funs(.[!is.na(.)][1]))

EDIT2

Assuming you have conflict and you want to show them all.

df <- read.table(text="groupid  col1  col2  col3  col4
1 0 n NA 2
1 1 NA 2 2",
header=TRUE,stringsAsFactors=FALSE)
df
groupid col1 col2 col3 col4
1 1 0 n NA 2
2 1 1(#)<NA> 2 2(#)
df %>%
group_by(groupid) %>%
summarise_all(funs(toString(unique(na.omit(.)))))#unique for duplicated like col4
groupid col1 col2 col3 col4
<int> <chr> <chr> <chr> <chr>
1 1 0, 1 n 2 2

dplyr merge dataframes by common row values in R

We can use inner_join

library(dplyr)
inner_join(DF1, DF2)

How to pivot table in r with multiple (or duplicate) text data?

We need a sequence column to make the rows unique and then use pivot_wider. In the wide format, then we do summarise_at to paste the elements

library(dplyr)
library(data.table)
library(tidyr)
library(stringr)
df %>%
mutate(rn = rowid(item)) %>%
pivot_wider(names_from = item, values_from = value) %>%
select(-rn) %>%
group_by(id) %>%
summarise_at(vars(-group_cols()), ~ str_c(unique(.), collapse = ", "))
# A tibble: 1 x 5
# id Job DOB organization info
# <int> <chr> <chr> <chr> <chr>
#1 1 Assistant, Project manager 27395 ABC, CDE Inspire others

Or another option is to make use of values_fn from pivot_wider

df %>% 
pivot_wider(names_from = item, values_from = value,
values_fn = list(value = ~ toString(unique(.))))
# A tibble: 1 x 5
# id Job DOB organization info
# <int> <chr> <chr> <chr> <chr>
#1 1 Assistant, Project manager 27395 ABC, CDE Inspire others

R pivot_longer combining several columns

We can use pivot_longer

library(dplyr)
library(tidyr)
library(stringr)
df %>%
pivot_longer(cols = matches('Species'), names_to = 'NearorFar',
values_to = 'Species') %>%
mutate(NearorFar = str_remove(NearorFar, "_.*")) %>%
pivot_longer(cols = starts_with('Count'), names_to = NULL,
values_to = 'Count', values_drop_na = TRUE) %>%
distinct

Pivoting replicate values in a column to wide format without aggregating using R

For each Day and Treatment create a unique ID column and cast the data to wide format.

library(dplyr)
library(tidyr)

tx %>%
group_by(Day, Treatment) %>%
mutate(row = row_number()) %>%
pivot_wider(names_from = Treatment, values_from = Values) %>%
ungroup %>%
select(-row)

# Day M1 M2 M3
# <dbl> <dbl> <dbl> <dbl>
#1 3 2 5 7
#2 3 3 7 9
#3 5 3 5 7
#4 5 3 5 2


Related Topics



Leave a reply



Submit