How to create text from dataframe by two categorical columns

1
votes

I am trying to take a filtered database table of text values and create a text report where there are variable number of lines (created by combining several columns for each row in the filtered table) with headings and sub-headings that are created from 2 other columns in the table.

This is to send to an rmarkdown document for creating a word or html doc. I have tried converting to list and using by() with paste to combine columns but cannot get what I want.

df=data.frame(cat1=c("A","A","A","B","B","C","C","C"),
  
  cat2=c("D","D","E","D","F","D","G","G"),
  
  text1=c("text1","text2","text3","text4","text5","text6","text7","text8"),
  
  text2=c("text9","text10","text11","text12","text13","text14","text15","text16"))

I want something like:

A: D:

text1, text9

text2, text10

A: E:

text3, text11

B: D:

text4, text12

B: F:

text5, text13

C: D:

text6, text14

C: G:

text7, text15

text8, text 16

I get complicated lists that I cannot parse.

3
rdataframe

3 Answers

1
votes

You can get that with aggregate and paste

T1 = aggregate(df$text1, list(df$cat1, df$cat2), paste, collapse = ", ")
T2 = aggregate(df$text2, list(df$cat1, df$cat2), paste, collapse = ", ")
T1$x = paste(T1$x, T2$x, sep=", ")
T1
  Group.1 Group.2                            x
1       A       D  text1, text2, text9, text10
2       B       D                text4, text12
3       C       D                text6, text14
4       A       E                text3, text11
5       B       F                text5, text13
6       C       G text7, text8, text15, text16
0
votes

Here is my current solution:

for (i in unique(df$cat1)) {
  cat(i,"\n")
  for (j in unique(df[df$cat1==i,"cat2"])) {
    cat(paste(" ",j),"\n")
    for (k in df$line[df$cat1==i & df$cat2==j])
    cat(paste("   ",k),"\n")
  }
}

Which gives:

A 
  D 
    text1, text9 
    text2, text10 
  E 
    text3, text11 
B 
  D 
    text4, text12 
  F 
    text5, text13 
C 
  D 
    text6, text14 
  G 
    text7, text15 
    text8, text16
0
votes

Use summarise to prepare the data and then a loop with cat to print it in rmarkdown. By using results = "asis" in the chunk options you can include the formatting in the cat command. 

```{r, results = "asis"}

library(dplyr)

newdf <- df %>% 
  group_by(cat1, cat2) %>%
  summarise(mystring = paste(text1, text2, sep = ", ", collapse = "  \n"))

for (i in 1:nrow(newdf)) {
  with(newdf, cat("  \n####**", cat1[i], ": ", cat2[i], ":**", "  \n\n", mystring[i], "  \n", sep = "" )) }

```

enter image description here