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
Can't Change Fonts in Ggplot/Geom_Text
Monitoring for Changes in File(S) in Real Time
How to Print R Variables in Middle of String
Change Stringsasfactors Settings for Data.Frame
How to Display Verbatim Inline R Code with Backticks Using Rmarkdown
R Library for Discrete Markov Chain Simulation
Ggmap with Geom_Map Superimposed
Using Trycatch and Rvest to Deal with 404 and Other Crawling Errors
Justification of Multiple Legends in Ggmap/Ggplot2
Combining Pivoted Rows in R by Common Value
Easier Way to Plot the Cumulative Frequency Distribution in Ggplot
What Algorithm I Need to Find N-Grams
Parallel Execution of Random Forest in R
Colorize Parts of the Title in a Plot
Warning Message: "Missing Values in Resampled Performance Measures" in Caret Train() Using Rpart
R: Split Variable Column into Multiple (Unbalanced) Columns by Comma
How to Expand an Ellipsis (...) Argument Without Evaluating It in R