How can I pivot wider and summarise the duplicates (R)?

Question

I wanted to transpose my table creating new columns by client. The rows would be by idMarket and Section, and the other columns would give the Score of each client in those Markets and Section. I want the summarise in each column if there is a duplicate.

idMarket  idSection idClient  Score  

2          99         23       100   
2          99         56       25     
3          67         23       56     
3          67         56       50
3          67         56       05     
2          99         23       20     

Expected table:

idMarket  idSection  Client23    Client56  

2          99         120          25      
3          67          56          55      

The next code gives me this warning:

pivot_wider(df, c(idMarket, idSection), names_from = idClient, values_from = c(Score))    

Warning message:
Values are not uniquely identified; output will contain list-cols.
* Use `values_fn = list` to suppress this warning.
* Use `values_fn = length` to identify where the duplicates arise
* Use `values_fn = {summary_fun}` to summarise duplicates 

The values appear in list inside each row.

I cant´t group by and summarise before pivot, it just doesn´t work for me.

Thanks!!

Answer 1

The values_fn parameter in pivot_wider is a real power tool. It will accept all basic R summary functions: mean, max, min, median, sum, prod, any, all. Some dplyr functions also work: first, last.

But the real power is that it now accepts anonymous functions. Here is an example for your df:

   pivot_wider(df, c(idMarket, idSection),
               names_from = idClient, 
               values_from = c(Score),
               values_fn = function(x) paste("total Score is", sum(x))

idMarket  idSection   Client23            Client56  
2          99         total Score is 120  total Score is 25      
3          67         total Score is 56   total Score is 55