How to reduce a data frame into single row with vectors

user_4918863 0
Viewed 317

I have this DF

  email       date      user_ipaddress       other data    
1 x@bla.com 2020-03-24  177.95.75.230         xxxx
2 x@bla.com 2020-04-02  177.139.49.93         yyyy
3 x@bla.com 2020-04-02  177.139.49.93         zzzz

and I want to transform this data to the shape it is going to be stored

the whole problem would be a big data frame with distinct emails and I want to reduce all data for each email in a single row like so

  email       date      user_ipaddress                       other data    
1 x@bla.com 2020-04-02  c('177.95.75.230','177.139.49.93')   c('xxxx','yyyy','zzzz')

actually, if someone could help me only with the case that there is only one email address it would save my life but feel free to help with the whole problem

using 

ipadreessVec<-Reduce(append,x =df$network_userid)

I can get my vector c('177.95.75.230','177.139.49.93') but if i try to make 

newdf$network_userid<-a

I get 

Error in `$<-.data.frame`(`*tmp*`, network_userid, value = c("20562206-f557-48a3-861b-5d1e18524bbb",  : 
  replacement has 3 rows, data has 1

any answer that makes me go a step further will get a approve even if it does not solve everything.

dataframe vector r reduce
4 Answers

We can create a list column grouped by 'email', 'date'

library(dplyr)
DF %>% 
    group_by(email, date) %>%
    summarise_all(list)
# A tibble: 2 x 4
# Groups:   email [1]
#  email     date       user_ipaddress otherdata
#  <chr>     <chr>      <list>         <list>   
#1 x@bla.com 2020-03-24 <chr [1]>      <chr [1]>
#2 x@bla.com 2020-04-02 <chr [2]>      <chr [2]>

Or in the devel version use across with summarise

DF %>%
   group_by(email, date) %>% 
   summarise(across(everything(), list))
# A tibble: 2 x 4
# Groups:   email [1]
#  email     date       user_ipaddress otherdata
#  <chr>     <chr>      <list>         <list>   
#1 x@bla.com 2020-03-24 <chr [1]>      <chr [1]>
#2 x@bla.com 2020-04-02 <chr [2]>      <chr [2]>

data

DF <- structure(list(email = c("x@bla.com", "x@bla.com", "x@bla.com"
), date = c("2020-03-24", "2020-04-02", "2020-04-02"),
 user_ipaddress = c("177.95.75.230", 
"177.139.49.93", "177.139.49.93"),
otherdata = c("xxxx", "yyyy", 
"zzzz")), class = "data.frame", row.names = c("1", "2", "3"))
user_37322710

I might be misunderstanding you, and it seems more likely that you want something like @akrun shows, but interpreting you literally, you might want something using dput:

as.data.frame(lapply(df, function(x) capture.output(dput(unique(x)))))
#>         email                          date                      user_ipaddress
#> 1 "x@bla.com" c("2020-03-24", "2020-04-02") c("177.95.75.230", "177.139.49.93")
#>                       other
#> 1 c("xxxx", "yyyy", "zzzz")
user_125003150 
library('data.table')

by email and date:

setDT(df)[, .(user_ipaddress = paste0(user_ipaddress, collapse = ","),
              other = paste0(other_data, collapse = ",")), by = .(email, date)]

#       email       date              user_ipaddress     other
# 1: x@bla.com 2020-03-24               177.95.75.230      xxxx
# 2: x@bla.com 2020-04-02 177.139.49.93,177.139.49.93 yyyy,zzzz

by email only:

setDT(df)[, .(date = paste0(date, collapse = ","),
              user_ipaddress = paste0(user_ipaddress, collapse = ","),
              other = paste0(other_data, collapse = ",")), by = .(email)]

#        email                             date                            user_ipaddress          other
# 1: x@bla.com 2020-03-24,2020-04-02,2020-04-02 177.95.75.230,177.139.49.93,177.139.49.93 xxxx,yyyy,zzzz

Data:

df <- read.table(text='email       date      user_ipaddress       other_data    
1 x@bla.com 2020-03-24  177.95.75.230         xxxx
                 2 x@bla.com 2020-04-02  177.139.49.93         yyyy
                 3 x@bla.com 2020-04-02  177.139.49.93         zzzz', header = TRUE, stringsAsFactors = FALSE)
user_16917230

Maybe you can try aggregate in base R:

dfout <- aggregate(.~email,df,FUN = function(x) list(unique(levels(x))))

such that

> dfout
      email                   date               user_ipaddress       other data
1 x@bla.com 2020-03-24, 2020-04-02 177.139.49.93, 177.95.75.230 xxxx, yyyy, zzzz

DATA

df <-  structure(list(email = c("x@bla.com", "x@bla.com", "x@bla.com"
), date = c("2020-03-24", "2020-04-02", "2020-04-02"), user_ipaddress = c("177.95.75.230", 
"177.139.49.93", "177.139.49.93"), `other data` = c("xxxx", "yyyy", 
"zzzz")), class = "data.frame", row.names = c(NA, -3L))
user_121587570
Related