gather multiple with 4 resulting columns "together"

3

Hi,

I have the following situation:

I have a data frame with several columns, a group of them I want to turn into key value. So far so good, but there are 2 groups and they can not kinda repeat themselves. Type, is a group of 3 and another of 3

NOME      A1   A2    A3    B1    B2    B3
batata     6   4     7      2    1      1
maçã       9   4     8      1    2      0

I made the gather 2 times, one for A1 A2 A3 and one for B1 B2 B3. The problem is that the resulting line number is 2 * 3 * 3 = 18, as it takes a result of A and makes a line for each result of B

NOME    keyA    valueA    keyB    valueB
batata   A1       6        B1       2
batata   A1       6        B2       1
batata   A1       6        B3       1
maçã     A1       9        B1       2
maçã     A1       9        B2       1
maçã     A1       9        B3       1

.......... (same process with A2 and A3)

What I need is for each value of A1, I have only the key B1, of A2 only B2, etc. So:

NOME    keyA    valueA    keyB    valueB
batata   A1       6        B1       2
batata   A2       4        B2       1
batata   A3       7        B3       1
maçã     A1       9        B1       1
maçã     A2       4        B2       2
maçã     A3       8        B3       0

Only 6 lines

Can anyone help me? XD

    
asked by anonymous 14.07.2017 / 22:50

2 answers

2

Here's a solution:

library(tidyverse)

x <- data.frame(
  NOME = c("batata", "maça"),
  A1 = c(6, 9),
  A2 = c(4, 4),
  A3 = c(7, 8),
  B1 = c(2, 1),
  B2 = c(1, 2),
  B3 = c(1, 0)
)


x %>%
  gather(keyA, valueA, starts_with("A")) %>%
  gather(keyB, valueB, starts_with("B")) %>%
  filter(parse_number(keyA) == parse_number(keyB))

The function parse_number takes only the numerical part of a variable, so you can use it to compare the keyA and keyB columns to get only what you need.

    
15.07.2017 / 14:35
0

Based on the result obtained from your gather function, I compared the columns to get the new data.frame . Note that I create a logical comparison vector of the numbers contained in the columns keyA and keyB and apply this vector to select the lines of data.frame .

produtos <- data.frame(nome = c('batata','batata','batata', 
                            'maça', 'maça','maça'), 
                   keyA = c('A1','A1','A1','A2','A2','A2'), 
                   valueA = c(6,6,6,9,9,9), 
                   keyB = c('B1','B2','B3','B1','B2','B3'), 
                   valueB = c(2,1,1,2,1,1))

library(stringr)
produtos[str_extract(produtos[,2], '\d') == str_extract(produtos[,4], '\d'), ]
    
15.07.2017 / 05:22