CODEWITHSUNDEEP
r
imprela
imprela

Reputation: 83

R reshaping data and summarizing information

I have a dataset containing sales per person for multiple years. Sample here:

yr_2008 <- data.frame(agent = c("agent1", "agent4", "agent1", "agent1", "agent1", "agent4"), sales = c(100, 200, 300, 130, 200, 400), year = 2008)
yr_2009 <- data.frame(agent = c("agent1", "agent3", "agent4", "agent1", "agent3", "agent4", "agent1", "agent3", "agent4"), sales = c(200, 500, 200, 200, 100, 100, 200, 300, 200), year = 2009)
yr_2010 <- data.frame(agent = c("agent1", "agent4", "agent2", "agent2", "agent2", "agent4"), sales = c(130, 300, 100, 200, 100, 200), year = 2010)
sales <- rbind(yr_2008, yr_2009, yr_2010)

What is the appropriate way to generate summaries per person for each year? For example, I want to see for each year the number of times a person made a sale, and how much. If a person wasn't there that year, then just have NA. For example, a 2008, I want to have this as an output

 sales_output <- data.frame(agent = c("agent1", "agent2", "agent3", "agent4"),
                       yr08_transaction = c(3, NA, NA, 2),
                       yr08_sales = c(730, NA, NA, 600))

I also want to have all this information in one table only such as the following

Extension:

sales_output <- data.frame(agent = c("agent1", "agent2", "agent3", "agent4"),
                       yr08_transaction = c(3, NA, NA, 2),
                       yr08_sales = c(730, NA, NA, 600),
                       yr09_transaction = c(3, 0, 3, 3), 
                       yr09_sales = c(600, 0, 900, 500),
                       yr10_transaction = c(1, 3, 0, 2),
                       yr10_sales = c(130, 400, 0, 500))

sales_output
agent yr08_transaction yr08_sales yr09_transaction yr09_sales yr10_transaction yr10_sales
1 agent1                3        730                3        600                1        130
2 agent2               NA         NA                0          0                3        400
3 agent3               NA         NA                3        900                0          0
4 agent4                2        600                3        500                2        500

Thanks!

Upvotes: 1

Views: 55

Answers (3)

camille
camille

Reputation: 16842

Here's a dplyr workflow. If you take this data and group it by year & agent, you can calculate the sums of sales and number of entries per agent per year. To get this into a wide format, use gather to first make it more long, getting both sales and transactions into a single column, unite the year with measure, so you have entries like "2009_sales", then spread to get it back to wide. spread also fills missing values in with NA.

library(tidyverse)

yr_2008 <- data.frame(agent = c("agent1", "agent4", "agent1", "agent1", "agent1", "agent4"), sales = c(100, 200, 300, 130, 200, 400), year = 2008)
yr_2009 <- data.frame(agent = c("agent1", "agent3", "agent4", "agent1", "agent3", "agent4", "agent1", "agent3", "agent4"), sales = c(200, 500, 200, 200, 100, 100, 200, 300, 200), year = 2009)
yr_2010 <- data.frame(agent = c("agent1", "agent4", "agent2", "agent2", "agent2", "agent4"), sales = c(130, 300, 100, 200, 100, 200), year = 2010)
sales <- rbind(yr_2008, yr_2009, yr_2010)

sales_summary <- sales %>%
    group_by(year, agent) %>%
    summarise(sales = sum(sales), transactions = n()) %>%
    gather(key = type, value = value, sales, transactions) %>%
    unite("yr", year, type) %>%
    spread(key = yr, value = value, sep = "")

sales_summary
#> # A tibble: 4 x 7
#>   agent  yr2008_sales yr2008_transactions yr2009_sales yr2009_transactions
#>   <fct>         <dbl>               <dbl>        <dbl>               <dbl>
#> 1 agent1          730                   4          600                   3
#> 2 agent4          600                   2          500                   3
#> 3 agent3           NA                  NA          900                   3
#> 4 agent2           NA                  NA           NA                  NA
#> # ... with 2 more variables: yr2010_sales <dbl>, yr2010_transactions <dbl>

Created on 2018-05-13 by the reprex package (v0.2.0).

Upvotes: 1

akrun
akrun

Reputation: 887163

Here is an option with data.table. Summarise to get the number of observations and sum of 'sales' grouped by 'agent' and 'year'and dcast to 'wide' format

library(data.table)
dcast(setDT(sales)[, .(transaction = .N, Sumsales = sum(sales)), by = .(agent, year)],
     agent ~ substr(year, 3, 4), value.var = c('transaction', 'Sumsales'))

Upvotes: 1

BENY
BENY

Reputation: 323276

By using dplyr with right_join

sales$agent <- as.character(sales$agent)

sales %>% filter(year==2008) %>% group_by(agent) %>% 
  summarise(yr08_transaction=n(),yr08_sales=sum(sales)) %>% 
  right_join(sales[!duplicated(sales$agent),c('agent','year')],by="agent") %>%
  arrange(agent) %>% select(-year)

# A tibble: 4 x 3
agent yr08_transaction yr08_sales
<chr>            <int>      <dbl>
1 agent1                4        730
2 agent2               NA         NA
3 agent3               NA         NA
4 agent4                2        600

Upvotes: 0

Related Questions