Home > Back-end >  Pulling out values that match row and column name in another dataframe in R
Pulling out values that match row and column name in another dataframe in R

Time:03-26

I am trying to take a data frame that looks like this:

desc<- c("a", "b", "c", "d")

df <- data.frame(matrix(ncol = 3 , nrow = 12))

colnames(df)<- c("Date", "Value", "Desc")
df[1:4,'Date'] <- "2017-01-01"
df[5:8,'Date'] <- "2018-01-01"
df[9:12,'Date'] <- "2019-01-01"

df[1:12,'Desc'] <- desc

df[1:12,'Value'] <- seq(1:12)

head(df)

And change it into one that has the dates as columns and "Desc" as row names, like this:

  2017-01-01 2018-01-01 2019-01-01
a         NA         NA         NA
b         NA         NA         NA
c         NA         NA         NA
d         NA         NA         NA

I have gotten as far as a few if loops to get my DF set up but cannot for the life of me think of how to pull out the value and put it into the new dataframe. Any help would be so appreciated at this point! I am also a student so I apologize if I missed any steps in posting this or did it the wrong way.

My brain couldn't wrap itself around how to match both values and then put it in the right position.

CodePudding user response:

We may use xtabs in base R

xtabs(Value ~ Desc   Date, df)

-output

  Date
Desc 2017-01-01 2018-01-01 2019-01-01
   a          1          5          9
   b          2          6         10
   c          3          7         11
   d          4          8         12

CodePudding user response:

Not sure if this is within the scope of what you are working on but I'd use the tidyverse functions for this since this seems to require pivoting the data frame from long to wide:

install.packages("tidyverse")
library("tidyverse")

df_wide <- df %>%
  mutate(Value = NA) %>%
  pivot_wider(names_from = Date, values_from = Value) 

df_wide
# A tibble: 4 × 4
  Desc  `2017-01-01` `2018-01-01` `2019-01-01`
  <chr> <lgl>        <lgl>        <lgl>       
1 a     NA           NA           NA          
2 b     NA           NA           NA          
3 c     NA           NA           NA          
4 d     NA           NA           NA     

More info about tidyverse functions here: https://www.tidyverse.org

  • Related