Aggregating data and showing in another column

[praveenbushipaka]

Hi, Im new to R. I have a problem in aggregating data
This is bugging me from 2days.

I have data like

   Account.ID asset_name
      6yS        A
      6yS        B
      6yS        B
      6yS        C
      6yU        D
      876        C

From here I want to make more columns of like dummies. But I want only one row each ID.

My output should look like this

   Account.ID   Flag_A  Flag_B  Flag_C  Flag_D
     6yS                   1         2          1            0     
     6yU                   0         0          0            1
     876                   0         0          1             0

I tried aggregating but they make it into another table, which I do not want to merge again, because I will be losing information.

Please help me out. Thank y'll in advance.

[dromano]

Hi @praveenbushipaka, I have two questions for you: Are you familiar with dplyr or tidyverse packages? And could you add your data by using ``` (triple backticks) in a post like this?

```
[paste output of dput([your table]) here
```

[andresrcs]

Since you are new here I'm going to use a little trick to read your sample data even if it is not copy/paste friendly but next time please post a REPRoducible EXample (reprex) like the one below.

As dromano says, you can use tidyverse functions, see this example.

library(tidyverse)

sample_df <- data.frame(
  stringsAsFactors = FALSE,
        Account.ID = c("6yS", "6yS", "6yS", "6yS", "6yU", "876"),
        asset_name = c("A", "B", "B", "C", "D", "C")
)

sample_df %>% 
    mutate(dummy = 1) %>% 
    pivot_wider(names_from = asset_name,
                names_prefix = "Flag_",
                values_from = dummy,
                values_fn = list(dummy = sum)) %>% 
    mutate_if(is.double, ~replace_na(.x, 0))
#> # A tibble: 3 x 5
#>   Account.ID Flag_A Flag_B Flag_C Flag_D
#>   <chr>       <dbl>  <dbl>  <dbl>  <dbl>
#> 1 6yS             1      2      1      0
#> 2 6yU             0      0      0      1
#> 3 876             0      0      1      0

^{Created on 2020-03-03 by the reprex package (v0.3.0.9001)}