How do I distribute the content of a column to other columns in R?

Question

I’m trying to distribute the contents of one column in another 3 columns.

I have the following Data Frame:

library(tidyr)

df<-data.frame(Coluna=
c('2237-5953',
'(RE) PENSANDO DIREITO',
'B4',
'2469-4312',
'[IN] TRANSITION',
'B2',
'1981-030X',
'19&20 (RIO DE JANEIRO)',
'B1',
'2053-1583',
'2D MATERIALS',
"A2")
  )

df
                   Coluna
1               2237-5953
2   (RE) PENSANDO DIREITO
3                      B4
4               2469-4312
5         [IN] TRANSITION
6                      B2
7               1981-030X
8  19&20 (RIO DE JANEIRO)
9                      B1
10              2053-1583
11           2D MATERIALS
12                     A2

There is a pattern here that is the ISSN number of the journal, its title and its classification in Qualis. Therefore, every three lines, the information repeats.

I thought of creating a new data frame df2 with the corresponding columns:

df2<-data.frame(df, numero="", periodico="", qualis="")

> df2
                   Coluna numero periodico qualis
1               2237-5953                        
2   (RE) PENSANDO DIREITO                        
3                      B4                        
4               2469-4312                        
5         [IN] TRANSITION                        
6                      B2                        
7               1981-030X                        
8  19&20 (RIO DE JANEIRO)                        
9                      B1                        
10              2053-1583                        
11           2D MATERIALS                        
12                     A2                        
> 

That done, I thought to use the function spread( ) to spread the contents of the first column (called "Column") between these 3 newly created columns:

df2 %>% 
  spread(Coluna, c(numero:qualis))

But to no avail.

I imagined that I had transmitted the following command "spread the contents of the column "Column" in columns ranging from "number" to "Qualis"."

However, of course, that is not what R understood. How can I accomplish this task?

Answer 1

As the information is grouped at regular intervals, you can use conditional indexing:

df2 <- data.frame(
  numero = df$Coluna[c(TRUE, FALSE, FALSE)],
  periodico = df$Coluna[c(FALSE, TRUE, FALSE)],
  qualis = df$Coluna[c(FALSE, FALSE, TRUE)]
)

> df2
     numero              periodico qualis
1 2237-5953  (RE) PENSANDO DIREITO     B4
2 2469-4312        [IN] TRANSITION     B2
3 1981-030X 19&20 (RIO DE JANEIRO)     B1
4 2053-1583           2D MATERIALS     A2

If you’re going to use spread or other similar functions, create identification columns first, taking advantage of the regularity:

df$id <- rep(1:(nrow(df)/3), each = 3)
df$col <- c("numero", "periodico", "qualis")

> tidyr::pivot_wider(df, names_from = col, values_from = Coluna)
# A tibble: 4 x 4
    id numero    periodico              qualis
  <int> <fct>     <fct>                  <fct>
1     1 2237-5953 (RE) PENSANDO DIREITO  B4
2     2 2469-4312 [IN] TRANSITION        B2
3     3 1981-030X 19&20 (RIO DE JANEIRO) B1
4     4 2053-1583 2D MATERIALS           A2

> reshape2::dcast(df, id ~ col, value.var = "Coluna")
  id    numero              periodico qualis
1  1 2237-5953  (RE) PENSANDO DIREITO     B4
2  2 2469-4312        [IN] TRANSITION     B2
3  3 1981-030X 19&20 (RIO DE JANEIRO)     B1
4  4 2053-1583           2D MATERIALS     A2