'R transform dataframe by parsing columns

Context

I have created a small sample dataframe to explain my problem. The original one is larger, as it has many more columns. But it is formatted in the same way.

df = data.frame(Case1.1.jpeg.text="the",
                Case1.1.jpeg.text.1="big",
                Case1.1.jpeg.text.2="DOG",
                Case1.1.jpeg.text.3="10197",
                
                Case1.2.png.text="framework",
                
                Case1.3.jpg.text="BE",
                Case1.3.jpg.text.1="THE",
                Case1.3.jpg.text.2="Change",
                Case1.3.jpg.text.3="YOUWANTTO",
                Case1.3.jpg.text.4="SEE",
                Case1.3.jpg.text.5="in",
                Case1.3.jpg.text.6="theWORLD",
                
                Case1.4.png.text="09.80.56.60.77")

The dataframe consists of output from a text detection ML model based on a certain number of input images.

The output format makes each word for each image a separate column, thereby creating a very wide dataset.

Desired Output

I am looking to create a cleaner version of it, with one column containing the image name (e.g. Case1.2.png) and the second with the concatenation of all possible words that the model finds in that particular image (the number of words varies from image to image).

result = data.frame(Case=c('Case1.1.jpeg','Case1.2.png','Case1.3.jpg','Case1.4.png'),
                Text=c('thebigDOG10197','framework','BETHEChangeYOUWANTTOSEEintheWORLD','09.80.56.60.77'))

I have tried many approaches based on similar questions found on Stackoverflow, but none seem to give me the exact output I'm looking for.

Any help on this would be greatly appreciated.

r prometheus grafana

Solution 1:^[1]

A possible solution:

library(tidyverse)

df %>% 
  pivot_longer(everything()) %>% 
  mutate(name = str_remove(name, "\\.text\\.*\\d*")) %>% 
  group_by(name) %>% 
  summarise(text = str_c(value, collapse = ""))

#> # A tibble: 4 x 2
#>   name         text                             
#>   <chr>        <chr>                            
#> 1 Case1.1.jpeg thebigDOG10197                   
#> 2 Case1.2.png  framework                        
#> 3 Case1.3.jpg  BETHEChangeYOUWANTTOSEEintheWORLD
#> 4 Case1.4.png  09.80.56.60.77

Solution 2:^[2]

An option in base R is stack the data into a two column data.frame with stack and then do a group by paste with aggregate

aggregate(cbind(Text = values) ~ Case, transform(stack(df), 
  Case = trimws(ind, whitespace = "\\.text.*")), FUN = paste, collapse = "")
          Case                              Text
1 Case1.1.jpeg                    thebigDOG10197
2  Case1.2.png                         framework
3  Case1.3.jpg BETHEChangeYOUWANTTOSEEintheWORLD
4  Case1.4.png                    09.80.56.60.77

Solution 3:^[3]

You can use pivot_longer(everything()), manipulate the "Case" column, group, and paste together:

pivot_longer(df,everything(),names_to="Case") %>%
  mutate(Case = str_remove_all(Case, ".text.*")) %>%
  group_by(Case) %>% summarize(Text=paste(value, collapse=""))

Output:

  Case         Text                             
  <chr>        <chr>                            
1 Case1.1.jpeg thebigDOG10197                   
2 Case1.2.png  framework                        
3 Case1.3.jpg  BETHEChangeYOUWANTTOSEEintheWORLD
4 Case1.4.png  09.80.56.60.77

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	PaulS
Solution 2	akrun
Solution 3	langtang

'R transform dataframe by parsing columns

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]