Create table via R (clusters)

Question

I would like to make an table for my problem via r. The code is bellow:

#database
df<-structure(list(Latitude = c(-23.8, -23.8, -23.9, -23.9, -23.9,  -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, 
+ -23.9, -23.9, -23.9, -23.9, -23.9), Longitude = c(-49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.7, 
+ -49.7, -49.7, -49.7, -49.7, -49.6, -49.6, -49.6, -49.6), Waste = c(526, 350, 526, 469, 285, 175, 175, 350, 350, 175, 350, 175, 175, 364, 
+ 175, 175, 350, 45.5, 54.6)), class = "data.frame", row.names = c(NA, -19L))
   
Q1<-matrix(quantile(df$Waste, probs = 0.25))
df_Q1<-subset(df,Waste>Q1[1])
#cluster
d<-dist(df_Q1)
fit.average<-hclust(d,method="average")
clusters<-cutree(fit.average,k=4)
df_Q1$cluster<-clusters
     
dc<-aggregate(df_Q1[,"Waste"],list(cluster=clusters),sum)
colnames(dc)<-c("cluster","Sum_Waste")
dd<-aggregate(df_Q1[,"Waste"],list(cluster=clusters),mean)
colnames(dd)<-c("cluster","Mean_Waste")

Thanks!

New table

Ben · Accepted Answer · 2020-04-19T00:59:49.520

The Sum_Waste and Mean_Waste can be merged with your df_Q1 data frame for the final table.

I was not sure what output you were considering, but one method with kableExtra will merge cells with identical values in designated columns. Hope this is helpful.

Edit: Added properties to table. Rows are now sorted by cluster and properties.

library(kableExtra)

#database
df<-structure(list(Latitude = c(-23.8, -23.8, -23.9, -23.9, -23.9,  -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, -23.9, 
                                + -23.9, -23.9, -23.9, -23.9, -23.9), Longitude = c(-49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.6, -49.7, 
                                                                                    + -49.7, -49.7, -49.7, -49.7, -49.6, -49.6, -49.6, -49.6), Waste = c(526, 350, 526, 469, 285, 175, 175, 350, 350, 175, 350, 175, 175, 364, 
                                                                                                                                                         + 175, 175, 350, 45.5, 54.6)), class = "data.frame", row.names = c(NA, -19L))

Q1<-matrix(quantile(df$Waste, probs = 0.25))
df_Q1<-subset(df,Waste>Q1[1])
df_Q1

#cluster
d<-dist(df_Q1)
fit.average<-hclust(d,method="average")
clusters<-cutree(fit.average,k=4)
df_Q1$cluster<-clusters
df_Q1$properties<-names(clusters)

#calculate sum waste
dc<-aggregate(df_Q1[,"Waste"],list(cluster=clusters),sum)
colnames(dc)<-c("cluster","Sum_Waste")
head(dc)

#calculate mean waste
dd<-aggregate(df_Q1[,"Waste"],list(cluster=clusters),mean)
colnames(dd)<-c("cluster","Mean_Waste")
head(dd)

#merge everything
df_table <- Reduce(merge, list(df_Q1, dc, dd))

#make table
kable(df_table[order(df_table$cluster, as.numeric(df_table$properties)),c(5,2,3,4,1,6,7)], align = "c", row.names = FALSE) %>%
  kable_styling(full_width = FALSE) %>%
  column_spec(1, bold = TRUE) %>%
  collapse_rows(columns = 5:7, valign = "middle")

Edit (4/18/20)

To aggregate properties in a single row, with comma separation, try:

#sort properties
df_table <- df_table[order(df_table$cluster, as.numeric(df_table$properties)),]

#second table aggregated properties
df_table2 <- aggregate(. ~ cluster + Sum_Waste + Mean_Waste, df_table[,c(1,5,6,7)], toString)

#make table with df_table2
kable(df_table2[order(df_table2$cluster), c(4,1,2,3)], align = "c", row.names = FALSE) %>%
  kable_styling(full_width = FALSE)

Ben, would you know how to add the properties of the respective cluster on a single line in the table?? Thanksss — , Apr 18 '20 at 22:46
Sorry, I expressed myself badly. I inserted the figure to show how I would like to keep the properties together. I want to remove latitude, longitude and waste, and leave it as shown above. It would be possible?? Thanks Ben — , Apr 18 '20 at 23:19
Ben, I'm doing it on a bigger basis, I managed to do it, but the numbers were out of order. I left another figure above just to check. Look cluster 2, it was ..... 14,15,55,54,19 .... How do I leave it in the correct order? — , Apr 19 '20 at 00:50
It worked, thank you very much! Thanks again Ben. Ben, just one question, do you know how to work with Shiny? — , Apr 19 '20 at 01:11
Ben, if you can check it out, I appreciate it (https://stackoverflow.com/questions/61298886/issues-related-to-shiny-from-rstudio). Thanks again. — , Apr 19 '20 at 02:44

Create table via R (clusters)

New table

1 Answers1