Pulling out values that match row and column name in another dataframe in R

user_18582776 0
Viewed 60

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.

data-cleaning dataframe statistics r
2 Answers

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
user_37322710

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

user_185496520
Related