Turning the database into R - A kind of PivotTable

0

I have a database as follows:

CNPJ    SIGLA      chave            CENTRAL
3333    CREDSUL    CACHOEIRODIM-ES  CECOOPES
4444    COOPSEFES  VITORIA-ES       CECOOPES
55555   CREDI      VITORIA-ES       CECOOPES
66666   CREDEXTRA  SERRA-ES         CECOOPES
99999   ACENTRA    CRICIUMA-SC      CECRED
77777   ACREDICOOP JOINVILLE-SC     CECRED
11111   CREDCREA   FLORIANOPOLIS-SC CECRED
22222   CATARINA   FLORIANOPOLIS-SC CECRED
1010101 CREDELESC  FLORIANOPOLIS-SC CECRED
88888   CREDIFIESC FLORIANOPOLIS-SC CECRED

I would like to have a final table as follows:

chave            CNPJ1  SIGLA1    CENTRAL1  CNPJ2   SIGLA2  CENTRAL2    CNPJ3   SIGLA3  CENTRAL3    CNPJ4   SIGLA4  CENTRAL4
CACHOEIRODIM-ES  3333   CREDSUL   CECOOPES                                  
VITORIA-ES       4444   COOPSEFES CECOOPES  55555   CREDI-ALIMENTO  CECOOPES                        
SERRA-ES         66666  CREDEXTRA CECOOPES                                  
CRICIUMA-SC      99999  ACENTRA   CECRED                                    
JOINVILLE-SC     77777  ACREDICOOP CECRED                                   
FLORIANOPOLIS-SC 11111  CREDCREA  CECRED    22222   CATARINA CECRED 1010101 CREDELESC   CECRED   88888  CREDIFIESC CECRED

I tried the reshape but I did not quite get what I wanted. Is it possible to do this directly with some package in R?

    
asked by anonymous 08.01.2018 / 13:45

1 answer

1

I was able to do this in two steps. First used the dplyr to create "SIGLAn" and "CENTRALn":

##Criando o contador
relcoop <- relcoop[order(relcoop$chave),] 
relcoopw<-relcoop %>% 
  group_by(chave) %>% 
  mutate(countercentral=paste0("CENTRAL",row_number()))

relcoopw<-relcoopw %>% 
  group_by(chave) %>% 
  mutate(countersigla=paste0("SIGLA",row_number()))

Then we transpose using the spread function of the package tidyr

##Tabela dinamica relcoop
library(tidyr)
relcoopwcentral<-spread(relcoopw,countercentral,CENTRAL)
relcoopwsigla<-spread(relcoopw,countersigla,SIGLA)
relcoopw<-cbind(relcoopwcentral,relcoopwsigla)
    
08.01.2018 / 20:00