CODEWITHSUNDEEP
r
Nicholas
Nicholas

Reputation: 3737

Removing duplicated rows on certain columns and aggregating data

I have some data. Here is a dummy dataframe as an example:

Reference = c('A', 'A', 'A', 'B', 'C', 'D', 'E', 'E')
Company = c('Google', 'Google', 'Xbox', 'Nike', 'Apple', 'Samsung', 'Paypal', 'Paypal')
Method = c('Direct', 'Indirect', 'Direct', 'Direct', 'Direct', 'Indirect', 'Direct', 'Indirect')
Payments = c(500, 750, 100, 2000, 1100, 450, 100, 900)
DirectPayment = c(500, 0, 100, 2000, 1100, 0, 100, 0)
IndirectPayment = c(0, 750, 0, 0, 0, 450, 0, 900)

df = data.frame(Reference, Company, Method, Payments, DirectPayment, IndirectPayment)

enter image description here

If you look at REFERENCE A, Google have a DIRECT and an INDIRECT payment; and in REFERENCE E, Paypal have an indirect and direct payment.

I need to get rid of the duplications on REFERENCE and COMPANY. I.e. for Google, I only want ONE line for Reference A with the direct payment in the DirectPayment Column and the indirect payment in the IndirectPayment i.e.:

anged

How do I do this? I have tried pivot_wide but not thats not exactly what I need here.

Thank you

Upvotes: 0

Views: 43

Answers (1)

Piotr K
Piotr K

Reputation: 1025

What about this one?

library(dplyr)
df %>% 
  group_by(Reference, Company) %>% 
  summarise_if(is.numeric, sum, na.rm = TRUE)

It gives:

# A tibble: 6 x 5
# Groups:   Reference [5]
  Reference Company Payments DirectPayment IndirectPayment
  <fct>     <fct>      <dbl>         <dbl>           <dbl>
1 A         Google      1250           500             750
2 A         Xbox         100           100               0
3 B         Nike        2000          2000               0
4 C         Apple       1100          1100               0
5 D         Samsung      450             0             450
6 E         Paypal      1000           100             900

Upvotes: 2

Related Questions