How to turn my data.frame variable values into columns in R?

Asked

Viewed 140 times

2

Suppose I have the following date.:

print(DADOS)

CODIGO  QTDE  MÊS
001     4     1
001     1     1
001     3     2  
001     2     3
001     3     3
001     4     3
001     2     4
001     2     4
001     1     5
001     5     6
001     2     6

But I need to turn the values of the variable MONTH into columnar. That would be the data.frame this way:

print(DADOS_TRANSFORMADOS)

CODIGO  QTDE  MÊS  QTDE  MÊS  QTDE  MÊS  QTDE  MÊS  QTDE  MÊS  QTDE  MÊS
001     4     1    3     2    2      3   2     4    1     5    5     6
001     1     1    NA    2    3      3   2     4    NA    5    2     6
001     NA    1    NA    2    4      3   NA    4    NA    5    NA    6

I tried to split my data.frame into 6 parts filtering for each month and then use the function join to join month to month, but it did not work.

How can I transform the values of my data.frame variable into columns?

r

1 answer

4


You can use the tidyverse to do what you want. First, create a reproducible example:

set.seed(123)

df_1 <- data.frame(
 cod = 1, 
 qtde = sample(x = 1:4, size = 11, replace = TRUE), 
 mes = c(1, 1, 2, 3, 3, 3, 4, 4, 5, 6, 6)
)

Now, the analysis:

library(tidyverse)

df_1 %>% 
  group_by(mes) %>% 
  mutate(n = row_number()) %>% 
  spread(key = mes, value = qtde)


# A tibble: 3 x 8
#    cod     n   `1`   `2`   `3`   `4`   `5`   `6`
#  <dbl> <int> <int> <int> <int> <int> <int> <int>
#1     1     1     3     3     3     2     4     4
#2     1     2     4    NA     3     1    NA     3
#3     1     3    NA    NA     3    NA    NA    NA

You can use the argument fill to fill in the NAs with the digit you want. For example, replace NAs for 0:

df_1 %>% 
  group_by(mes) %>% 
  mutate(n = row_number()) %>% 
  spread(key = mes, value = qtde, fill = 0)

# A tibble: 3 x 8
#    cod     n   `1`   `2`   `3`   `4`   `5`   `6`
#  <dbl> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1     1     1     3     3     3     2     4     4
#2     1     2     4     0     3     1     0     3
#3     1     3     0     0     3     0     0     0
  • 1

    thank you for the answer I will test.

  • Neves, if in my case the database is much larger, for example if in my field 'size' was larger than 100 thousand lines, as could, mount the vector 'mes' without having to write each line?

  • 1

    What do you mean "no need to write each line"?

  • Is that in the example you wrote the variables in the vector, mes = c(1, 1, 2, 3, 3, 3, 4, 4, 5, 6, 6), but after some test I understood that I really did not need and selected with the group_by function the column MONTH directly.

Browser other questions tagged r

You are not signed in. Login or sign up in order to post.