CODEWITHSUNDEEP
rrstudiostata
H Park
H Park

Reputation: 223

reshape data wide to long for multiple variables in R

I have a dataset that shows each bank's investment and dollar value associated with this investment. Currently the data looks like this. I have inv and amt variables stretching from 1 to 43.

bankid year location inv1   amt1 inv2 amt2 ... inv43 amt43 
1          1990 NYC      AIG    2000 GM   4000     Ford  6000

but I want the data to look like this

bankid year location inv number amt
1      1990  NYC     AIG  1     2000  
1      1990  NYC     GM   2     4000
...
1      1990  NYC     Ford 43    6000

In Stata, I would use this code

reshape long inv amt, i(bankid location year) j(number)

What would be the equivalent code in R?

Upvotes: 1

Views: 939

Answers (1)

Matthew Lundberg
Matthew Lundberg

Reputation: 42649

reshape can do this. Here I am using the posted subset of your data, where you have time variables 1, 2, and 43:

x <- read.table(header=TRUE, text='bankid year location inv1   amt1 inv2 amt2  inv43 amt43 
1          1990 NYC      AIG    2000 GM   4000     Ford  6000 ')
x
##   bankid year location inv1 amt1 inv2 amt2 inv43 amt43
## 1      1 1990      NYC  AIG 2000   GM 4000  Ford  6000

v <- outer(c('inv', 'amt'), c(1,2,43), FUN=paste0)
v
##      [,1]   [,2]   [,3]   
## [1,] "inv1" "inv2" "inv43"
## [2,] "amt1" "amt2" "amt43"

reshape(x, direction='long', varying=c(v), sep='')
##      bankid year location time  inv  amt id
## 1.1       1 1990      NYC    1  AIG 2000  1
## 1.2       1 1990      NYC    2   GM 4000  1
## 1.43      1 1990      NYC   43 Ford 6000  1

For your full table, the varying argument would be c(outer(c('inv', 'amt'), 1:43, FUN=paste0)) (but that won't work for the small example, as columns are missing).

Here, reshape infers the 'time' variable by inspecting the varying argument and finding common elements (inv and amt) on the left, and other elements on the right (1, 2, and 43). The sep argument says that there is no separator character (default sep character is .).

Upvotes: 2

Related Questions