-1
  OECD2012  GDP2012 GDP2012born  countrynew countrybornText permile
1        1 56149.67    50632.44 Switzerland   United States       3
2        1 56149.67    45948.54 Switzerland     Netherlands       2
3        1 56149.67    44829.27 Switzerland         Ireland       1
4        1 56149.67    44551.62 Switzerland         Austria       9
5        1 56149.67    44336.81 Switzerland         Denmark       3
6        1 56149.67    43355.81 Switzerland          Sweden       1

Hi my data looks like this and I need to make it a matrix. So I run the following:

df2 <- df %>% spread(key = countrybornText, value = permile)

My problem is that I want the columns to be ordered by GDP2012born and they show up in alphabetical order when I transpose. Any idea how to keep the order I had in the column countrybornText?

df <- structure(list(OECD2012 = c(1, 1, 1, 1, 1, 1), GDP2012 = c(56149.67, 
      56149.67, 56149.67, 56149.67, 56149.67, 56149.67), GDP2012born = c(50632.44, 
      45948.54, 44829.27, 44551.62, 44336.81, 43355.81), countrynew = structure(c(1L, 
      1L, 1L, 1L, 1L, 1L), .Label = "Switzerland", class = "factor"), 
      countrybornText = structure(c(6L, 4L, 3L, 1L, 2L, 5L), .Label = c("Austria", 
      "Denmark", "Ireland", "Netherlands", "Sweden", "United States"), 
      class = "factor"), permile = c(3, 2, 1, 9, 3, 1)), class = "data.frame", 
      row.names = c(NA, -6L))
Allan Cameron
  • 147,086
  • 7
  • 49
  • 87
Diego Marino
  • 79
  • 1
  • 13
  • 2
    instead posting a picture of your data, please use `dput(head(my_data)))` and post the output here – Roman Feb 18 '20 at 15:23
  • 1
    Hi Diego Marino. See [here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) for information on how to properly add a data sample. – dario Feb 18 '20 at 15:24
  • try using `pivot_wider` with `tidyverse`: `df %>% pivot_wider(id_cols = c(-countrybornText, -permile), names_from = countrybornText, values_from = permile)` – Ben Feb 18 '20 at 15:37

1 Answers1

1

You can make sure that the column is a factor, with the levels in the order that you want:

library(dplyr)
library(tidyr)

df$countrybornText <-factor(as.character(df$countrybornText), as.character(df$countrybornText))
df2 <- df %>% spread(key = countrybornText, value = permile)

df2
#>   OECD2012  GDP2012 GDP2012born  countrynew United States Netherlands Ireland
#> 1        1 56149.67    43355.81 Switzerland            NA          NA      NA
#> 2        1 56149.67    44336.81 Switzerland            NA          NA      NA
#> 3        1 56149.67    44551.62 Switzerland            NA          NA      NA
#> 4        1 56149.67    44829.27 Switzerland            NA          NA       1
#> 5        1 56149.67    45948.54 Switzerland            NA           2      NA
#> 6        1 56149.67    50632.44 Switzerland             3          NA      NA
#>   Austria Denmark Sweden
#> 1      NA      NA      1
#> 2      NA       3     NA
#> 3       9      NA     NA
#> 4      NA      NA     NA
#> 5      NA      NA     NA
#> 6      NA      NA     NA

Created on 2020-02-18 by the reprex package (v0.3.0)

Allan Cameron
  • 147,086
  • 7
  • 49
  • 87
  • Thats exactly what Im looking for but I get this error now: Error in `levels<-`(`*tmp*`, value = as.character(levels)) : factor level [40] is duplicated – Diego Marino Feb 18 '20 at 22:43
  • I think the reason is that in the position 40 I have countrynew=Netherlands and countrybornText=United States. Is that so? – Diego Marino Feb 18 '20 at 22:44
  • 1
    @DiegoMarino yes, I thought the country names were unique. Try `df$countrybornText <-factor(as.character(df$countrybornText), unique(as.character(df$countrybornText)))` – Allan Cameron Feb 18 '20 at 22:59
  • @DiegoMarino great! Please consider marking the answer as accepted to help other users with similar problems – Allan Cameron Feb 19 '20 at 12:03