Warm tip: This article is reproduced from stackoverflow.com, please click
grouping id merge r reduce

Merge rows with the same ID but with overlapping variables

发布于 2020-04-15 10:08:25

I have data in r that has over 6000 observations and 96 variables.

The data relates to groups of individuals and their activities etc. If a group returned the Group ID number was recorded again and a new observation was made. I need to merge the rows by ID so that the # of individuals take the highest number recorded, but the activities etc are a combination of both observations.

The data contains, #of individuals, activities, impacts, time of arrival etc. The issue is that some of the observations were split across 2 lines, so there may be activities which were recorded for the same group in another line. The Group ID for both observations is the same, but one may have the #of individuals recorded and some activity records or impacts, but the second may be incomplete and only have Group ID and then Impacts (which are additional to those in the 1st record). The #of individuals in the group never changes, so I need some way to combine them so that activities are additive, but #visitors takes the value that is highest, time of arrival needs to be the earliest recorded and time of departure needs to be the later of the 2 observations.

Does anyone know how to merge observations based on Group ID but vary the merging protocol based on the variable.

enter image description here

Questioner
Andrew Torsney
Viewed
51
分享
Em Laskey 2020-02-04 21:58

I'm not sure if this actually is what you want, but to combine rows of a data frame based on multiple conditions you can use the dplyr package and its summarise()function. I generated some data to use in R directly, you would have to modify the code according to your needs.

# generate data
ID<-rep(1:20,2)
visitors<-sample(1:50, 40, replace=TRUE)
impact<-sample(rep(c("a", "b", "c", "d", "e"), 8))
arrival<-sample(rep(8:15, 5))
departure <- sample(rep(16:23, 5))

df<-data.frame(ID, visitors, impact, arrival, departure)
df$impact<-as.character(df$impact)

# summarise rows with identical ID
df_summary <- df %>%
  group_by(ID) %>%
  summarise(visitors = max(visitors), arrival = min(arrival), 
            departure = max(departure), impact = paste0(impact, collapse =", "))

Hope this helps!

Andrew Torsney 2020-02-05 22:15:10

This is exactly what I wanted and worked perfectly for my data. I really really appreciate the help.

Em Laskey 2020-02-05 23:04:41

Glad I could help! Could you maybe accept the answer if you're happy with it? Thanks!

Andrew Torsney 2020-02-11 17:39:02

Sorry this was the 1st question I have ever asked, so I didnt realise I had to accept the answer. That is accepted now.

Related issues

1
Filter in rows of all columns with specific conditions
2
ggplot2 both axis labels inside plot area
3
Error: could not find function ... in R
4
Create loading messages that will change based on loading time of plot in a shiny app
5
cut.default error in heatmap generation R
6
Problem with apply function in r: it's applied only in the first column
7
R to create a tally of previous events within a sliding window time period
8
combine many columns from one dataframe into another dataframe using setDT
9
Display download button in a tab based on actions in other tabs of a shiny dashboard
10
Parsing dates in R with weird format