CODEWITHSUNDEEP
rreshapedcast
Raghavan vmvs
Raghavan vmvs

Reputation: 1265

cast data in wide format while retaining the alignment of variables

I have created the following dataframe in R

df<-data.frame("ID"= c("A", "A", "A", "A", "B", "B", "B"))
df$X_F= c(1,5,6, 7, 1, 7, 8)
df$X_A= c(1,5,6, 7, 1, 7, 8)

The above dataframe is in long format

  ID X_F X_A
1  A   1   1
2  A   5   5
3  A   6   6
4  A   7   7
5  B   1   1
6  B   7   7
7  B   8   8

I want to convert the dataframe to wide format as. I have imported the reshape library and datatable libraries for the same

library(DT)
library(reshape2)
library(data.table)

Now I have run the following code

df2<-dcast(melt(setDT(df), id.var = "ID"), rowid(ID, variable) ~ 
                 paste(ID, variable, sep="__"))[, ID := NULL][]

This yields the following dataframe

     A__X_A A__X_F B__X_A B__X_F
       1      1      1      1
       5      5      7      7
       6      6      8      8
       7      7     NA     NA

How do I obtain the following dataframe

     A__X_A A__X_F B__X_A B__X_F
      1      1      1      1
      5      5      NA     NA
      6      6      NA     NA
      7      7       7      7
      NA     NA      8      8

How do I obtain this result. I request someone to take a look

Upvotes: 1

Views: 51

Answers (2)

dario
dario

Reputation: 6485

Alternative dplyr/tidyr solution:

df<-data.frame("ID"= c("A", "A", "A", "A", "B", "B", "B"))
df$X_F= c(1,5,6, 7, 1, 7, 8)
df$X_A= c(1,5,6, 7, 1, 7, 8)


library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(cols = -ID) %>% 
  mutate(newID = value) %>% 
  pivot_wider(id_cols = newID, names_from = c(ID, name), values_from = value, values_fn = {first}) %>% 
  select(-newID)

Returns:

  A_X_F A_X_A B_X_F B_X_A
  <dbl> <dbl> <dbl> <dbl>
1     1     1     1     1
2     5     5    NA    NA
3     6     6    NA    NA
4     7     7     7     7
5    NA    NA     8     8

Upvotes: 1

ThomasIsCoding
ThomasIsCoding

Reputation: 101247

Try the data.table code below

dcast(
  melt(setDT(df), id.var = "ID")[
    ,
    q := match(value, sort(unique(unlist(df[, .(X_F, X_A)])))),
    ID
  ],
  q ~ ID + variable
)

which gives

   q A_X_F A_X_A B_X_F B_X_A
1: 1     1     1     1     1
2: 2     5     5    NA    NA
3: 3     6     6    NA    NA
4: 4     7     7     7     7
5: 5    NA    NA     8     8

Upvotes: 2

Related Questions