CODEWITHSUNDEEP
rcsvformat
Neeraj
Neeraj

Reputation: 1236

Reshaping Data into panel form

I have data where the object name is a variable name like EPS, Profit etc. (around 25 such distinct objects)

The data is arranged like this :

EPS <- read.table(text = "
Year    Microsoft   Facebook
2001    12          20
2002    15          23
2003    16          19
", header = TRUE)

Profit <- read.table(text = "
Year    Microsoft   Facebook
2001    15          36
2002    19          40
2003    25          45
", header = TRUE)

I want output like this : 

Year Co_Name   EPS Profit
2001 Microsoft 12  15
2002 Microsoft 15  19
2003 Microsoft 16  25
2001 Facebook  20  36
2002 Facebook  23  40
2003 Facebook  19  45

How it can be done? Is there any way to arrange data of all variables as a single object? Data of each variable is imported into R from a csv file like EPS.csv, Profit.csv etc. Is there any way to create loop from importing to arranging data in a desired format?

Upvotes: 1

Views: 144

Answers (2)

akrun
akrun

Reputation: 887501

We can get the datasets in a list. If we already created 'EPS', 'Profit' as objects, use mget to get those in a list, convert to a single data.table with rbindlist, melt to long format and reshape it back to 'wide' with dcast.

library(data.table)#v1.9.6+
DT <- rbindlist(mget(c('EPS', 'Profit')), idcol=TRUE)
DT1 <- dcast(melt(rbindlist(mget(c('EPS', 'Profit')), idcol=TRUE),
           id.var=c('.id', 'Year'), variable.name='Co_Name'), 
           Year+Co_Name~.id, value.var='value')
DT1
#   Year   Co_Name EPS Profit
#1: 2001 Microsoft  12     15
#2: 2001  Facebook  20     36
#3: 2002 Microsoft  15     19
#4: 2002  Facebook  23     40
#5: 2003 Microsoft  16     25
#6: 2003  Facebook  19     45

If we need to arrange it, use order

DT1[order(factor(Co_Name, levels=unique(Co_Name)))]

Upvotes: 3

dickoa
dickoa

Reputation: 18437

Just for fun we can also achieve the same result using dplyr, tidyr and purrr. 

library(dplyr)
library(tidyr)
library(readr)
library(purrr)

list_of_csv <- list.files(path = ".", pattern = ".csv", full.names = TRUE)
file_name <- gsub(".csv", "", basename(list_of_csv))

list_of_csv %>%
  map(~ read_csv(.)) %>%
  map(~ gather(data = ., key = co_name, value = value, -year)) %>%
  reduce(inner_join, by = c("year", "co_name")) %>%
  setNames(., c("year", "co_name", file_name))
## Source: local data frame [6 x 4]

##    year   co_name   eps profit
##   (int)    (fctr) (int)  (int)
## 1  2001 microsoft    12     15
## 2  2002 microsoft    15     19
## 3  2003 microsoft    16     25
## 4  2001  facebook    20     36
## 5  2002  facebook    23     40
## 6  2003  facebook    19     45

Upvotes: 3

Related Questions