Get list of rownames as values after aggregating a dataframe

0 votes

Consider a data frame like this:

    type    Var1    Var2
1    A    hey      hey
2    A    hey      hey
3    B    hello    hey
4    B    hello    hey
5    C    hii      hello
6    C    hii      hello

I used melt/cast to get the below result

type    hey    hello    hii
A        4      0       0
B        2      2       0
C        0      2       2

Refer to my code below:

library(reshape)
melted <- melt(data, id='type')
count <- function(z) { 
  length(na.omit(z))
}
casted_data <- cast(melted, type~value, count)

Here the output generated is not what I want, I do not want to count/sum. Instead, I want to get a list of the original row names.

Below is my desired output:

type    hey    hello    hii
A       1,2      0       0
B       3,4    3,4       0
C        0     5,6      5,6

Where each value of hey, hello, hii is a list of unique row names from original data frame.

Can anyone help?

Apr 17, 2018 in Data Analytics by DataKing99
• 8,250 points
665 views

1 answer to this question.

0 votes

You can use reshape2 library:

library(reshape2)
data <- read.table(text="type    Var1    Var2
1    A    hey      hey
2    A    hey      hey
3    B    hello    hey
4    B    hello    hey
5    C    hii      hello
6    C    hii      hello")

data$id <- rownames(data)

melted <- melt(data, id=c('type','id'),measure.vars=c("Var1","Var2"))

fun <- function(z) paste(unique(z),collapse=",") 

dcast(melted,type~value,fun,value.var="id",fill="0")

#  type hey hello hii
#1    A 1,2    0   0
#2    B 3,4  3,4   0
#3    C   0  5,6  5,6
answered Apr 17, 2018 by Sahiti
• 6,370 points

Related Questions In Data Analytics

0 votes
1 answer
+1 vote
2 answers
0 votes
3 answers

Code snippet to find number of null values in each column in a dataframe in R

colSums(is.na(data_frame_name)) will return you number of nulls ...READ MORE

answered Jul 5, 2019 in Data Analytics by sindhu
17,614 views
0 votes
1 answer

How to replace only set of values in a vector to different values in a dataframe?

If you have a condition using which ...READ MORE

answered Aug 19, 2019 in Data Analytics by anonymous
• 33,030 points
762 views
0 votes
1 answer

Reshape data from long to wide format

You can use the reshape function reshape(data, idvar ...READ MORE

answered Apr 17, 2018 in Data Analytics by nirvana
• 3,130 points
734 views
0 votes
1 answer

Reshape data from long to wide format in R

Use reshape function: reshape(dat1, idvar = "name", timevar = ...READ MORE

answered Jun 14, 2018 in Data Analytics by CodingByHeart77
• 3,750 points
2,725 views
0 votes
1 answer

Reshape dataframe without “timevar” from long to wide format in R

Assuming that the data is in the ...READ MORE

answered Jun 14, 2018 in Data Analytics by CodingByHeart77
• 3,750 points
979 views
0 votes
1 answer

Reshaping/Deduping long data to wide in R

Using pivot_wider and rename library(dplyr) library(tidyr) repl <- c("1st_transaction" = "type_1", "2nd_transaction" = ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,020 points
381 views
+1 vote
1 answer

How to convert a list of dataframes in to a single dataframe using R?

You can use the plyr function: data <- ...READ MORE

answered Apr 14, 2018 in Data Analytics by Sahiti
• 6,370 points
6,993 views
0 votes
1 answer

How to apply list to a function which give data frame as output

If you use  tidyverse, you can use ...READ MORE

answered Apr 11, 2018 in Data Analytics by Sahiti
• 6,370 points
760 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP