Aggregate and Reshape from Long to Wide

Aggregate by multiple columns and reshape from long to wide

Use dcast or even acast from reshape2() package

dcast(dat,Id~Description,mean)
Id Cat Dog
1 10 14.25 14.25
2 11 15.25 15.25

Base R might be abit longer:

 reshape(aggregate(.~Id+Description,dat,mean),direction = "wide",v.names  = "Value",idvar = "Id",timevar = "Description")
Id Value.Cat Value.Dog
1 10 14.25 14.25
2 11 15.25 15.25

Aggregate and reshape from long to wide

Your data are already in a long format that can be used easily by "reshape2", like this:

library(reshape)
dcast(df, bdate ~ sex + age + diag, value.var = "admissions")
# bdate Female_35-64_card Female_35-64_cere Female_65-74_card Female_65-74_cere
# 1 1987-01-01 1 6 1 6
# 2 1987-01-02 4 4 0 6
# 3 1987-01-03 2 6 4 5
# Male_35-64_card Male_35-64_cere Male_65-74_card Male_65-74_cere
# 1 0 6 0 1
# 2 0 5 0 2
# 3 1 6 0 4

I don't see any aggregation in your sample output, but if aggregation is required, you can achieve this with the fun.aggregate function within dcast.

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

long to wide format aggregate R tidyverse

Not really sure how you get the 3 count for GENEa and READSb, but assuming you want the count, you can try the following:


library(tidyverse)

df <- tibble(
READS = rep(c("READa", "READb", "READc"), each = 3),
GENE = rep(c("GENEa", "GENEb", "GENEc"), each = 3),
COMMENT = rep(c("CommentA", "CommentA", "CommentA"), each = 3)
)
df
#> # A tibble: 9 x 3
#> READS GENE COMMENT
#> <chr> <chr> <chr>
#> 1 READa GENEa CommentA
#> 2 READa GENEa CommentA
#> 3 READa GENEa CommentA
#> 4 READb GENEb CommentA
#> 5 READb GENEb CommentA
#> 6 READb GENEb CommentA
#> 7 READc GENEc CommentA
#> 8 READc GENEc CommentA
#> 9 READc GENEc CommentA

df %>%
count(READS, GENE) %>%
pivot_wider(
names_from = GENE, values_from = n,
values_fill = list(n = 0)
)
#> # A tibble: 3 x 4
#> READS GENEa GENEb GENEc
#> <chr> <int> <int> <int>
#> 1 READa 3 0 0
#> 2 READb 0 3 0
#> 3 READc 0 0 3

Created on 2019-12-13 by the reprex package (v0.3.0)

Reshape DF from long to wide in R using Reshape2 without an aggregation function

We can use dcast from data.table, which can take multiple value.var columns. Convert the 'data.frame' to 'data.table' (setDT(df)), use the dcast with formula and value.var specified.

library(data.table)
dcast(setDT(df), id~gid, value.var=names(df)[2:6])

NOTE: The data.table method would be faster compared to the reshape2

How to reshape data from long to wide format

Using reshape function:

reshape(dat1, idvar = "name", timevar = "numbers", direction = "wide")

Reshape from long to wide while summing

Using dplyr and tidyr

library(dplyr)
library(tidyr)

df %>%
group_by(rankP, defaulted) %>%
summarize(amount = sum(amount)) %>%
spread(defaulted, amount)

#Source: local data table [3 x 3]
#Groups:

# rankP 0 1
#1 1 157850 593450
#2 2 93950 224475
#3 3 30975 292625

As @akrun mentioned,
using xtabs simply

xtabs(amount~rankP+defaulted, df)


Related Topics



Leave a reply



Submit