1

I’m trying to reshape my data from long to wide formula using the dcast function from .

The objective is to use different variables in the value.var parameter but R doesn't let me use more than one value in it.

Is there any other way I could fix it? I've looked at other similar questions but I haven't been able to find a similar examples.

Here is my current dataset:

+---------+------+--------+--------------+------------+
| Country | Year | Growth | Unemployment | Population |
+---------+------+--------+--------------+------------+
| A       | 2015 |      2 |          8.3 |         40 |
| B       | 2015 |      3 |          9.2 |         32 |
| C       | 2015 |    2.5 |          9.1 |         30 |
| D       | 2015 |    1.5 |          6.1 |         27 |
| A       | 2016 |      4 |          8.1 |         42 |
| B       | 2016 |    3.5 |            9 |       32.5 |
| C       | 2016 |    3.7 |            9 |         31 |
| D       | 2016 |    3.1 |          5.3 |         29 |
| A       | 2017 |    4.5 |          8.1 |       42.5 |
| B       | 2017 |    4.4 |          8.4 |         33 |
| C       | 2017 |    4.3 |          8.5 |         30 |
| D       | 2017 |    4.2 |          5.2 |         30 |
+---------+------+--------+--------------+------------+

My objective is to pass year column to the rest of the columns (growth, unemployment and population). I’m using the below dcast function.

data_wide <- dcast(world, country  ~ year,
     value.var=c("Growth","Unemployment","Population"))

Ideal outcome

+---------+-------------+-------------------+-----------------+-------------+-------------------+-----------------+
| Country | Growth_2015 | Unemployment_2015 | Population_2015 | Growth_2016 | Unemployment_2016 | Population_2016 |
+---------+-------------+-------------------+-----------------+-------------+-------------------+-----------------+
| A       |           2 |               8.3 |              40 |           4 |               8.1 |              42 |
| B       |           3 |               9.2 |              32 |         3.5 |                 9 |            32.5 |
| C       |         2.5 |               9.1 |              30 |         3.7 |                 9 |              31 |
| D       |         1.5 |               6.1 |              27 |         3.1 |               5.3 |              29 |
+---------+-------------+-------------------+-----------------+-------------+-------------------+-----------------+
Jaap
  • 81,064
  • 34
  • 182
  • 193
Juanma
  • 195
  • 2
  • 4
  • 12
  • 3
    Do not post only images. Also post the output of `dput(X)` where X is your input data frame (or a sufficiently cut down version of it if it is large). Without that no one can try out your data without typing it all in manually and one cannot be absolutely sure of the classes of the columns. – G. Grothendieck May 14 '17 at 13:27
  • My first guess would be to try `reshape(world, direction = "wide", timevar = "Year", idvar = "Country")` – KenHBS May 14 '17 at 13:52
  • Hi @Juan. You can actually edit your question, remove your images and copy-paste the values instead. Also, as stated in previous comments, it would be very useful if you added the result of `dput(X)`. It makes it easier to anybody that wants to provide an answer that works. Thanks! – lrnzcig May 14 '17 at 14:51

2 Answers2

7

If you're not married to a dcast solution, I personally find tidyr easier.

library(tidyr)
df <- df %>% 
     gather(key, value, -Country, -Year) %>%  
     unite(new.col, c(key, Year)) %>%   
     spread(new.col, value)

Result

  Country Growth_2015 Growth_2016 Growth_2017 Population_2015 Population_2016 Population_2017 Unemployment_2015 Unemployment_2016 Unemployment_2017
1       A         2.0         4.0         4.5              40            42.0            42.5               8.3               8.1               8.1
2       B         3.0         3.5         4.4              32            32.5            33.0               9.2               9.0               8.4
3       C         2.5         3.7         4.3              30            31.0            30.0               9.1               9.0               8.5
4       D         1.5         3.1         4.2              27            29.0            30.0               6.1               5.3               5.2

This works by

Stacking all values into one column...

Combining variable name and year columns into a single column...

The new column is then spread into wide format

  • Please show the result, thank you. – Uwe May 14 '17 at 16:23
  • Thanks for the reply. I've just run the code and got the following error: Error: is.character(x) is not TRUE. Looking into it now. – Juanma May 14 '17 at 18:12
  • Hmm, sorry @Juan, this seems to work fine, for me, with the data you provided. Do you get the error with this subset? What about if you run only the 'gather' line on your full dataset? –  May 14 '17 at 19:54
  • Hi, no worries. Thanks for your reply. Appreciated. Yes, with gather only, it occurs. Yes, I'm trying with this subset. – Juanma May 14 '17 at 19:57
  • Hi again, Yes. I've managed to get it done, I've changed the syntax slightly. `x<-gather(world,key, value, -Country, -Year) %>% unite(new.col, c(key, Year)) %>% spread(new.col, value)` – Juanma May 14 '17 at 20:05
  • Ah, yes, my example only applied the transformation, and didn't assign it. the equivalent would be `x <- df %>% gather` etc. At least it's working now :) - I'll update the answer too –  May 14 '17 at 20:09
0

The dcast() statement given by the OP works almost perfect with the recent versions of the data.table package as these allow for multiple measure variables to be used with dcast() and melt():

library(data.table)   # CRAN version 1.10.4
setDT(world)   # coerce to data.table
data_wide <- dcast(world, Country ~ Year, 
                   value.var = c("Growth", "Unemployment", "Population"))

data_wide
#   Country Growth_2015 Growth_2016 Growth_2017 Unemployment_2015 Unemployment_2016 Unemployment_2017 Population_2015
#1:       A         2.0         4.0         4.5               8.3               8.1               8.1              40
#2:       B         3.0         3.5         4.4               9.2               9.0               8.4              32
#3:       C         2.5         3.7         4.3               9.1               9.0               8.5              30
#4:       D         1.5         3.1         4.2               6.1               5.3               5.2              27
#   Population_2016 Population_2017
1:            42.0            42.5
2:            32.5            33.0
3:            31.0            30.0
4:            29.0            30.0

This is the same result as the tidyr solution.

However, the OP has requested a specific column order for his ideal solution where the different measure variables of each year are grouped together.

If the proper order of columns is important, there are two ways to achieve this. The first approach is to reorder the columns appropriately using setcolorder():

new_ord <- CJ(world$Year, c("Growth","Unemployment","Population"), 
              sorted = FALSE, unique = TRUE)[, paste(V2, V1, sep = "_")]
setcolorder(data_wide, c("Country", new_ord))

data_wide
#   Country Growth_2015 Unemployment_2015 Population_2015 Growth_2016 Unemployment_2016 Population_2016 Growth_2017
#1:       A         2.0               8.3              40         4.0               8.1            42.0         4.5
#2:       B         3.0               9.2              32         3.5               9.0            32.5         4.4
#3:       C         2.5               9.1              30         3.7               9.0            31.0         4.3
#4:       D         1.5               6.1              27         3.1               5.3            29.0         4.2
#   Unemployment_2017 Population_2017
#1:               8.1            42.5
#2:               8.4            33.0
#3:               8.5            30.0
#4:               5.2            30.0

Note the the cross join function CJ() is used to create the cross product of the vectors.

The other approach to achieve the desired column order is to melt and recast:

molten <- melt(world, id.vars = c("Country", "Year"))
dcast(molten, Country ~ Year + variable)
#   Country 2015_Growth 2015_Unemployment 2015_Population 2016_Growth 2016_Unemployment 2016_Population 2017_Growth
#1:       A         2.0               8.3              40         4.0               8.1            42.0         4.5
#2:       B         3.0               9.2              32         3.5               9.0            32.5         4.4
#3:       C         2.5               9.1              30         3.7               9.0            31.0         4.3
#4:       D         1.5               6.1              27         3.1               5.3            29.0         4.2
#   2017_Unemployment 2017_Population
#1:               8.1            42.5
#2:               8.4            33.0
#3:               8.5            30.0
#4:               5.2            30.0
Community
  • 1
  • 1
Uwe
  • 41,420
  • 11
  • 90
  • 134
  • Thank you so much @Uwe Block, I really appreciate it. I've tried the different methods and it seems to be working fine. I just included setDT in the first formula and it populates the right table. `data_wide <- dcast(setDT(world), Country ~ Year, value.var = c("Growth", "Unemployment", "Population"))`. The molten example works completely fine. – Juanma May 17 '17 at 10:07
  • No worries. It was really useful. I will get back to you if I have any more question. Thanks so much. – Juanma May 17 '17 at 10:47