How to Convert Long to Wide Format With Counts

Easy way to convert long to wide format with counts

You can accomplish this with a simple table() statement. You can play with setting factor levels to get your responses the way you want.

sample.data$Decision <- factor(x = sample.data$Decision,
                               levels = c("Referred","Approved","Declined"))

table(Case = sample.data$Case,sample.data$Decision)

Case Referred Approved Declined
   1        3        1        0
   2        1        0        1
   3        2        0        1
   4        0        1        0
   5        0        0        1

Count from long to wide format

In case you need it as a data.frame, here's an option with data.table

library(data.table)
setDT(df)

dcast(df, id ~ text, fun.aggregate = length)
#    id arrange stock
# 1:  1       1     2
# 2:  2       2     0

R Long to wide with count and sum

Here's one way using dplyr and tidyr libraries. We first calculate sum of Amt values for each ID, get the data in long format, count number of rows and get it back in wide format.

library(dplyr)  
library(tidyr)

df %>%
  group_by(ID) %>%
  mutate(Amt = sum(Amt)) %>%
  pivot_longer(cols = c(Method, Source)) %>%
  count(ID, value, Amt, name) %>%
  pivot_wider(names_from = c(name, value), values_from = n, values_fill = 0)


#     ID   Amt Method_A Method_C Source_X Source_Y Source_Z Method_B Method_D Method_E
#  <int> <int>    <int>    <int>    <int>    <int>    <int>    <int>    <int>    <int>
#1     1    60        2        1        1        1        1        0        0        0
#2     2    25        0        0        0        1        1        1        1        0
#3     3    40        0        1        2        0        0        0        1        0
#4     4    20        0        0        0        0        2        0        0        2

Is there a tidy way to convert long to wide by counting the number of category?

Just group by id and summarise, i.e.

library(dplyr)

dd %>% 
 group_by(id) %>% 
 summarise(names = toString(names), 
           counts = n())

Reshaping data from long to wide with both sums and counts

From data.table v1.9.6, it is possible to cast multiple value.var columns and also cast by providing multiple fun.aggregate functions. See below:

library(data.table)

df <- data.table(df)
dcast(df, id ~ type, fun = list(length, sum), value.var = c("val"))
   id val_length_A val_length_B val_length_C val_sum_A val_sum_B val_sum_C
1:  1            2            1            0         1         2         0
2:  2            1            1            1         0         0         4

Convert wide data frame with counts to long format in R

If we need to replicate, uncount on the 'value' column after pivoting to 'long' format

library(dplyr)
library(tidyr)
dataset1 %>% 
    pivot_longer(cols = -alcolevel, names_to = 'Y' ) %>%
    uncount(value)  %>%
    as_tibble
# A tibble: 32,573 x 2
#   alcolevel    Y  
#       <dbl> <chr>  
# 1         0 present
# 2         0 present
# 3         0 present
# 4         0 present
# 5         0 present
# 6         0 present
# 7         0 present
# 8         0 present
# 9         0 present
#10         0 present
# … with 32,563 more rows

Long to wide with a unique ID based on the count of the transformed variable

Here's a tidyverse version that is nearly a one-liner:

library(tidyr)
pivot_longer(select(df1, -id_2), starts_with("s02_"),
             names_to = "n", names_prefix = "s02_", values_to = "s02")
# # A tibble: 12 x 3
#     id_1 n       s02
#    <dbl> <chr> <dbl>
#  1     1 0         1
#  2     1 1         2
#  3     1 2         3
#  4     2 0         1
#  5     2 1         2
#  6     2 2         3
#  7     2 0         4
#  8     2 1         5
#  9     2 2         6
# 10     2 0         7
# 11     2 1         8
# 12     2 2         9

Note: this n is 0-based, because its value is derived from the column names s02_0, s02_1, and s02_2; this can be fixed with a little post-processing (e.g., convert to integer, add one, optionally convert back to character), such as

library(dplyr)
pivot_longer(select(df1, -id_2), starts_with("s02_"),
             names_to = "n", names_prefix = "s02_", values_to = "s02") %>%
  mutate(n = as.integer(n) + 1L)
# # A tibble: 12 x 3
#     id_1     n   s02
#    <dbl> <int> <dbl>
#  1     1     1     1
#  2     1     2     2
#  3     1     3     3
#  4     2     1     1
#  5     2     2     2
#  6     2     3     3
#  7     2     1     4
#  8     2     2     5
#  9     2     3     6
# 10     2     1     7
# 11     2     2     8
# 12     2     3     9

---

Related Topics