How to reshape vertical table

user_16153131 0
Viewed 58

I would like to reshape the table with patient information. I have tried xtabs from base R and acast from reshape2, but couldn't get the results. My table looks something like this:

var1 id quantity price
Patient name 1 0021 7.0 167.3
Drugs NA 3.0 15.8
Medical products NA 1.0 150.0
Solutions NA 3.0 4.5
Patient name 2 0154 11.0 792.0
Drugs NA 7.0 37.4
Surgical materials NA 4.0 754.6

As you can see some patients might have surgery some don't, in this situation it is not even mentioned under the patient's name. And the number which is next to id 0021 and 0154 is the total number of quantity and total price for that patient, this information is not needed in the new table. I think totals can be easily calculated later. The final table should look like this:

ID Drugs price Medical products price Solutions price Surgical materials price Drugs quantity (etc.)
0021 15.8 150.0 4.5 0 3.0
0154 37.4 0 0 754.6 7.0

So, the function has to fill missed variables with 0, and have to be vertical.

reshape r
2 Answers

You can fill the id values and get the data in wide format.

library(dplyr)
library(tidyr)

df %>%
  fill(id) %>%
  pivot_wider(names_from = var1, values_from = c(quantity, price), values_fill = 0)
user_39629140 
library(dplyr)
library(tidyr)

df %>% 
  fill(id) %>% 
  filter(var1 %in% c("Drugs", "Medical products", "Solutions", "Surgical materials")) %>% 
  pivot_wider(names_from = var1, 
              values_from = c(price, quantity), 
              names_glue = "{var1} {.value}",
              values_fill = 0) %>% 
  mutate(id = sprintf("%04d", id))

This gives us:

# A tibble: 2 x 9
  id    `Drugs price` `Medical products p~ `Solutions price` `Surgical materials ~ `Drugs quantity` `Medical products q~ `Solutions quant~ `Surgical materials ~
  <chr>         <dbl>                <dbl>             <dbl>                 <dbl>            <dbl>                <dbl>             <dbl>                 <dbl>
1 0021           15.8                  150               4.5                    0                 3                    1                 3                     0
2 0154           37.4                    0               0                    755.                7                    0                 0                     4
user_129679380
Related