0

I'm new to R and started two weeks ago. I have a data set which i'm trying to convert into a panel data. See data set below

ID  Empl93   Empl95  Sales93  Sales95
1    20       30       200      150
2    14       40       350       90
4    50       10       100      220
9    29       45       400      560
20   42       23       190      350

I need to convert this to a panel as below

ID   Emply    Sales   Year
1    20       200     1993
1    30       150     1995
2    14       350     1993
2    40       90      1995
4    50       100     1993
4    10       220     1995
9    29       400     1993
9    45       560     1995

The rows are about 1600 and made up of random IDs, I can add the new column "Year" to the data frame. I have also been able to add the duplicate rows using the code below:

newdata <- newdata[rep(seq_len(nrow(newdata)), each=2),]

My problem is how to copy the Empl93, Empl95, Sales93, Sales95 values from first data frame and paste in the correspondent years in the panel. Thank you.

Bayuo Blaise
  • 237
  • 3
  • 9
  • 1
    can you insert the data using say `dput` – kangaroo_cliff Oct 17 '17 at 13:31
  • 1
    Possible duplicate of [How to reshape data from long to wide format?](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) – LAP Oct 17 '17 at 13:33

1 Answers1

3

A solution using dplyr and tidyr.

library(dplyr)
library(tidyr)

dt2 <- dt %>%
  gather(Key, Value, -ID) %>%
  extract(Key, into = c("Type", "Year"), "([A-Za-z]+)([0-9]+)") %>%
  mutate(Type = sub("Empl", "Emply", Type),
         Year = as.integer(paste0("19", Year))) %>%
  spread(Type, Value) %>%
  select(ID, Emply, Sales, Year)
dt2
   ID Emply Sales Year
1   1    20   200 1993
2   1    30   150 1995
3   2    14   350 1993
4   2    40    90 1995
5   4    50   100 1993
6   4    10   220 1995
7   9    29   400 1993
8   9    45   560 1995
9  20    42   190 1993
10 20    23   350 1995

DATA

dt <- read.table(text = "ID  Empl93   Empl95  Sales93  Sales95
1    20       30       200      150
2    14       40       350       90
4    50       10       100      220
9    29       45       400      560
20   42       23       190      350",
                 header = TRUE, stringsAsFactors = FALSE)
www
  • 38,575
  • 12
  • 48
  • 84