0

I have been struggling to do a conditional sum between two dataframes. I tried to recreate the data (in the exact form I recreated it in my real work problem), so I now have to run something that could be a very simple sumif between different worksheets in Excel. I am a newbie on R, so any help would be much appreciated! 

x1 <- data.frame("ClientID" = c("75","85","95", "75","85","95", "75","85","95"), "Jan2014" = c(80.25, 96.47, 94.44, 85.29, 97.66, 82.33, 35.44, 55.56, 88.88), "Feb2014" = c(90.25, 46.44, 54.54, 86.29, 87.66, 52.33, 55.44, 44.56, 78.87))
column.names <- c("ClientID", "Jan2014", "Feb2014")        
ClientID <- x1$ClientID
ClientID <- unique(ClientID)
rows.no <- length(ClientID)
column.no <- length(column.names)
x2 <-   data.frame(matrix(nrow=rows.no, ncol = column.no))
colnames(x2) <- column.names
x2$ClientID <- ClientID

#I want to have the totals for each of the Jan2014 and Feb2014 columsn at the x2 data table, based on the x1 table (sumifs equivalent)

x2$Jan2014 <- 
x2$Feb2014 <-
dmel2017
  • 13
  • 1
  • 3
  • 1
    Just do `library(dplyr);x1 %>% group_by(ClientID) %>% summarise_all(sum)` or `aggregate(.~ClientID, df1, sum)` – akrun Sep 13 '17 at 11:01

1 Answers1

2

You can do it this way:

library(dplyr)
x1 %>% group_by(ClientID) %>% summarize_all(sum)

# # A tibble: 3 x 3
#   ClientID Jan2014 Feb2014
# <fctr>   <dbl>   <dbl>
# 1       75  200.98  231.98
# 2       85  249.69  178.66
# 3       95  265.65  185.74

Or more generally (but same result here)

x1 %>% filter(ClientID %in% c(75,85,95)) %>%
  group_by(ClientID) %>%
  select(Jan2014,Feb2014) %>%
  summarize_all(sum)
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
  • Thanks a lot! Does the solution differ significantly if the x1 dataframe has different number of columns (and different column names) from the x1 dataframe? – dmel2017 Sep 13 '17 at 11:16
  • in the first case it summarizes all the columns from `x1` for every client, in the second case you choose your clients on first line and choose the columns you need on 3rd line – moodymudskipper Sep 13 '17 at 11:30