extract comma separated values from R dataframe

user_13199674 0
Viewed 469

One of the column in following dataframe has comma separated value:

Dataframe1:

Id        date                   price     batch          resp
uv-1      2020-01-10 15:13:16    1000      Q              ES,RT,AL
uv-2      2020-01-11 17:13:16    5000      W              ES,AL
uv-3      2020-01-12 18:13:16    2000      E              ES,RT
uv-4      2020-01-13 12:13:16    3000      R              ES,RT
uv-5      2020-01-14 13:13:16    1600      T              RT,AL
uv-6      2020-01-15 13:13:16    1600      T              ES,AL
uv-7      2020-01-17 11:13:16    1300      Y              ES,RT,AL

I need extract the count of resp value on month basis as follows.

                   Jan-20
batch    ES        RT      AL      Total
Q        1         1       1       1
%        100%      100%    100%    14.29%
W        1         0       1       1
%        100%      0.00%    100%   14.29% 
E        1         1       0       1
%        100%      100%    0.00%   14.29%   
R        1         1       0       1
%        100%      100%    0.00%   14.29%
T        1         1       2       2
%        50%       50%     100%    28.57%
Y        1         1       1       1
%        100%      100%    100%    14.29%
Total    6         5       5       7
Total(%) 85.71%    71.43%  71.43%  100%
dplyr dataframe r
1 Answers

With dplyr, tidyr, and lubridate, we can create two summarised data frames, one for the batches and one for the totals, and then combine them using bind_rows.

library(lubridate)
library(dplyr)
library(tidyr)

Now create the two data frames. The first groups on month and batch, the second only on month:

df_batch <- df %>%
  mutate(date = as.POSIXct(date), resp=strsplit(resp, ",")) %>%
  unnest(resp) %>%
  group_by(month=month(date), batch) %>%
  count(resp) %>%
  mutate(Total=max(n), p=100*n/Total) %>% 
  pivot_wider(names_from=resp, values_from=c(n,p), values_fill=list(n=0, p=0)) %>%
  ungroup() %>%
  mutate(p_Total=100*Total/sum(Total)) %>%
  select(month,batch,starts_with("n"),Total,starts_with("p"))

df_totals <- df %>% 
  mutate(date = as.POSIXct(date), resp=strsplit(resp, ",")) %>%
  group_by(month=month(date)) %>%
  mutate(Total=n()) %>%
  unnest(resp) %>%
  count(Total, resp) %>%
  mutate(p=100*n/Total) %>%
  pivot_wider(names_from=resp, values_from=c(n, p)) %>%
  mutate(batch="Total", p_Total=100)

bind_rows(df_batch, df_totals)

# A tibble: 7 x 10
  month batch  n_ES  n_RT  n_AL Total  p_ES  p_RT  p_AL p_Total
  <dbl> <chr> <int> <int> <int> <int> <dbl> <dbl> <dbl>   <dbl>
1     1 E         1     1     0     1 100   100     0      14.3
2     1 Q         1     1     1     1 100   100   100      14.3
3     1 R         1     1     0     1 100   100     0      14.3
4     1 T         1     1     2     2  50    50   100      28.6
5     1 W         1     0     1     1 100     0   100      14.3
6     1 Y         1     1     1     1 100   100   100      14.3
7     1 Total     6     5     5     7  85.7  71.4  71.4   100

It's not exactly in your format provided, but the results are exactly the same and it should work for multiple months.

Data:

structure(list(Id = c("uv-1", "uv-2", "uv-3", "uv-4", "uv-5", 
"uv-6", "uv-7"), date = c("2020-01-10 15:13:16", "2020-01-11 17:13:16", 
"2020-01-12 18:13:16", "2020-01-13 12:13:16", "2020-01-14 13:13:16", 
"2020-01-15 13:13:16", "2020-01-17 11:13:16"), price = c(1000L, 
5000L, 2000L, 3000L, 1600L, 1600L, 1300L), batch = c("Q", "W", 
"E", "R", "T", "T", "Y"), resp = c("ES,RT,AL", "ES,AL", "ES,RT", 
"ES,RT", "RT,AL", "ES,AL", "ES,RT,AL")), class = "data.frame", row.names = c(NA, 
-7L))
user_75145270
Related