Convert rows into columns taking the names from one variable

Question

I have a dataset of this kind df:

df <- as.data.frame(matrix(c(1,1,1,1,2,2,
                            2020,2020,2020,2020,2020,2020, 
                            1,2,1,2,3,6, 
                            "Spain","Spain","France","France","Germany","Japan",
                            0,40,5,200,98,300, 
                            1,3,2,19,4,11), ncol = 6))
names(df) <- c('ID', 'year', 'month', 'country', 'buy', 'sell')

I want to transform df. Keeping ID, year and month as reference variables and based on country I am trying to convert the rows into new variables, in the following way df_modified:

df_modified <- as.data.frame(matrix(c(1,1,2,2,
                                      2020,2020,2020,2020, 
                                      1,2,3,6,
                                      0,40,"NA","NA",
                                      5,200,"NA","NA",
                                      1,3,"NA","NA", 
                                      2,19,"NA","NA", 
                                      "NA","NA",98,300, 
                                      "NA","NA",4,11), ncol = 9))
names(df_modified) <- c('ID', 'year', 'month', 'buy_Spain', 'sell_Spain', 'buy_France','sell_France', 'buy_Germany', 'sell_Germany')

I have tried:

library(reshape2)
df_modified <- dcast(df, id+year+month ~ country)

However it does not make the trick.

Note: Numeric variables are numeric in my original dataset (country is character, etc).

Any clue?

Regards

akrun · Accepted Answer · 2020-09-05T18:26:45.853

We can specify the value.var in dcast

library(data.table)
dcast(setDT(df),  ID + year + month ~ country, value.var = c('buy', 'sell'))

Or using pivot_wider

library(tidyr)
library(dplyr)
 df %>%
   pivot_wider(names_from = country, values_from = c(buy, sell), 
        values_fill = list(buy = '0', sell = '0'))
# A tibble: 4 x 11
#  ID    year  month buy_Spain buy_France buy_Germany buy_Japan sell_Spain sell_France sell_Germany sell_Japan
#  <chr> <chr> <chr> <chr>     <chr>      <chr>       <chr>     <chr>      <chr>       <chr>        <chr>     
#1 1     2020  1     0         5          0           0         1          2           0            0         
#2 1     2020  2     40        200        0           0         3          19          0            0         
#3 2     2020  3     0         0          98          0         0          0           4            0         
#4 2     2020  6     0         0          0           300       0          0           0            11

If we need to convert to numeric class, just do type.convert

df %>%
    type.convert(as.is = TRUE) %>%
    pivot_wider(names_from = country, values_from = c(buy, sell), 
        values_fill = list(buy = 0, sell = 0))
# A tibble: 4 x 11
#     ID  year month buy_Spain buy_France buy_Germany buy_Japan sell_Spain sell_France sell_Germany sell_Japan
#  <int> <int> <int>     <int>      <int>       <int>     <int>      <int>       <int>        <int>      <int>
#1     1  2020     1         0          5           0         0          1           2            0          0
#2     1  2020     2        40        200           0         0          3          19            0          0
#3     2  2020     3         0          0          98         0          0           0            4          0
#4     2  2020     6         0          0           0       300          0           0            0         11

Duck · Answer 2 · 2020-09-05T18:22:04.137

Try this approach:

library(tidyverse)
#Reshape
df %>% pivot_wider(names_from = country,values_from=c(buy,sell))

Output:

# A tibble: 4 x 11
  ID    year  month buy_Spain buy_France buy_Germany buy_Japan sell_Spain sell_France sell_Germany
  <fct> <fct> <fct> <fct>     <fct>      <fct>       <fct>     <fct>      <fct>       <fct>       
1 1     2020  1     0         5          NA          NA        1          2           NA          
2 1     2020  2     40        200        NA          NA        3          19          NA          
3 2     2020  3     NA        NA         98          NA        NA         NA          4           
4 2     2020  6     NA        NA         NA          300       NA         NA          NA          
# ... with 1 more variable: sell_Japan <fct>

If you wanna transform to character the factors you can use:

#Reshape 2
df %>% pivot_wider(names_from = country,values_from=c(buy,sell)) %>%
  mutate_at(vars(contains("_")),funs(as.character))

Output:

# A tibble: 4 x 11
  ID    year  month buy_Spain buy_France buy_Germany buy_Japan sell_Spain sell_France sell_Germany
  <fct> <fct> <fct> <chr>     <chr>      <chr>       <chr>     <chr>      <chr>       <chr>       
1 1     2020  1     0         5          NA          NA        1          2           NA          
2 1     2020  2     40        200        NA          NA        3          19          NA          
3 2     2020  3     NA        NA         98          NA        NA         NA          4           
4 2     2020  6     NA        NA         NA          300       NA         NA          NA          
# ... with 1 more variable: sell_Japan <chr>

Or as numeric type:

#Reshape 3
df %>% pivot_wider(names_from = country,values_from=c(buy,sell)) %>%
  mutate_at(vars(contains("_")),funs(as.numeric(as.character(.))))

Output:

# A tibble: 4 x 11
  ID    year  month buy_Spain buy_France buy_Germany buy_Japan sell_Spain sell_France sell_Germany
  <fct> <fct> <fct>     <dbl>      <dbl>       <dbl>     <dbl>      <dbl>       <dbl>        <dbl>
1 1     2020  1             0          5          NA        NA          1           2           NA
2 1     2020  2            40        200          NA        NA          3          19           NA
3 2     2020  3            NA         NA          98        NA         NA          NA            4
4 2     2020  6            NA         NA          NA       300         NA          NA           NA
# ... with 1 more variable: sell_Japan <dbl>

And to replace all NA by zero you can use this:

#Reshape 4
df %>% pivot_wider(names_from = country,values_from=c(buy,sell)) %>%
  mutate_at(vars(contains("_")),funs(as.numeric(as.character(.)))) %>%
  replace(is.na(.),0)

Output:

# A tibble: 4 x 11
  ID    year  month buy_Spain buy_France buy_Germany buy_Japan sell_Spain sell_France sell_Germany
  <fct> <fct> <fct>     <dbl>      <dbl>       <dbl>     <dbl>      <dbl>       <dbl>        <dbl>
1 1     2020  1             0          5           0         0          1           2            0
2 1     2020  2            40        200           0         0          3          19            0
3 2     2020  3             0          0          98         0          0           0            4
4 2     2020  6             0          0           0       300          0           0            0
# ... with 1 more variable: sell_Japan <dbl>

Super clean and elegant. Thank you! – vog Sep 05 '20 at 18:54 — vog, Sep 05 '20 at 18:54

Convert rows into columns taking the names from one variable

2 Answers2