How to create a new dataframe with summarized data in R?

Question

The given dataset contains transfers between bank accounts. Every row contains the bank_account and the transfer_amount. The purpose is to calculate per bank account the total transfer_amount and to put this in a new dataframe. This new dataframe should only consist of unique bank_accounts (not the same as in the old one in which each bank account can appear multiple times) and the sum of their transferred amount.

Example: the lowest table is what the result should look like

Welcome to StackOverflow. Please take a look at these tips on how to produce a [minimum, complete, and verifiable example](http://stackoverflow.com/help/mcve), as well as this post on [creating a great example in R](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). Perhaps the following tips on [asking a good question](http://stackoverflow.com/help/how-to-ask) may also be worth a read. — lmo, Jan 31 '17 at 16:06

score 0 · Answer 1 · edited Jan 31 '17 at 17:20

Assuming that the dataset is called data, the column that contains the account name is called account, and the column containing the transfer is called transfer:

u <- unique(data$account) # get all unique bank account numbers
data.new <- data.frame(matrix(NA, nrow=length(u), ncol=2)) #create a new dataset to collect the results
for (i in 1:length(u)) { #open a loop, for every unique back account
  data.new[i, 1] <- u[i] #record the bank account number in the first column of the new dataframe
  data.new[i, 2] <- sum(data[data$account == u[i], "transfer"]) #sum all transfers related to this account and assign to the new dataframe
} #close the loop

score 0 · Answer 2 · answered Jan 31 '17 at 17:14

You can do this with aggregate. Lets take df as your input data shown in the picture.

df <-data.frame(account_number = c(1,2,3,2,3), amount = c(50,23,12,34,56))

aggregate(amount ~ account_number, df, sum)

    account_number amount
1              1     50
2              2     57
3              3     68

How to create a new dataframe with summarized data in R?

2 Answers2