Is there an R function for grouping a table by two rows?

Question

My table has three rows and i want to sort my table by two of the rows like in this example/solution: How to reshape data from long to wide format

But the table I use there a multiple similar rows with just another value.

I've tried to use the reshape function and spread function out of this thread (same as above): How to reshape data from long to wide format

df1 <- data.frame("Company" = c(1, 1, 1, 1, 2, 2, 3, 3, 3, 4), "Job" = c(1,4,5,1,5,5,1,2,3,4), "Value" = c(2.3,3,4.1,5.4,7.3,4.2,3.1,4,5,1.1))

df1
   Company Job Value
1        1   1   2.3
2        1   4   3.0
3        1   5   4.1
4        1   1   5.4
5        2   5   7.3
6        2   5   4.2
7        3   1   3.1
8        3   2   4.0
9        3   3   5.0
10       4   4   1.1

I want my table to look like this: As an example, I want to have both values for company 1 and job 1 summed up.

df2 <- data.frame("Company" = c(1, 2, 3, 4), "Job1"=c(7.7, 0, 3.1, 0), "Job2"=c(0, 0, 4, 0), "Job3"=c(0, 0, 5, 0), "Job4"=c(3, 0, 0, 1.1), "Job5"=c(4.1, 0, 0, 0))

df2
  Company Job1 Job2 Job3 Job4 Job5
1       1  7.7    0    0  3.0  4.1
2       2  0.0    0    0  0.0  0.0
3       3  3.1    4    5  0.0  0.0
4       4  0.0    0    0  1.1  0.0

The error messages are:

reshape(df, idvar = "Company", timevar = "Job", direction = "wide")
1: In reshapeWide(data, idvar = idvar, timevar = timevar, varying = varying,  :
  multiple rows match for Job=1: first taken
2: In reshapeWide(data, idvar = idvar, timevar = timevar, varying = varying,  :
  multiple rows match for Job=5: first taken 


spread(df, key = Job, value = Value)
 Each row of output must be identified by a unique combination of keys.
Keys are shared for 4 rows:
* 1, 4
* 5, 6

Could you please provide an example of your original input table (with three rows) and your expected output. See https://stackoverflow.com/a/5963610/882102 — MrGumble, Jul 31 '19 at 06:36

score 0 · Accepted Answer · answered Jul 31 '19 at 07:45

You can try a tidyverse

library(tidyverse)
df1 %>% 
  group_by(Company, Job) %>% 
  summarise(Value=sum(Value)) %>% 
  mutate(Job = paste0("Job", Job)) %>% 
  spread(Job, Value, fill = 0)
# A tibble: 4 x 6
# Groups:   Company [4]
  Company  Job1  Job2  Job3  Job4  Job5
    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1       1   7.7     0     0   3     4.1
2       2   0       0     0   0    11.5
3       3   3.1     4     5   0     0  
4       4   0       0     0   1.1   0

Is there an R function for grouping a table by two rows?

1 Answers1