Sorting and rearranging Excel datatable in R

Question

I have an Excel file with data (5 columns and 200 rows).

Rows are the next:

TransactionId, UserId, transaction_date, transaction_status, amount_USD.

The data.table is like that (it is not full, just to catch the idea):

Screenshot of datatable1

The task is rearrange the table in way it will look like 3 columns (transaction_date, CHARGED, DECLINED), where CHARGED and DECKLINED - are the sum of all amount_USD by the same date with specific status (Charged or Declined).

So, it should look like that:

And also, when I use such code for importing from Excel - is it correct?

> library("xlsx")

> data1 <- read.xlsx("d:/R/1.xlsx", 1)

Answer 1

These questions have been answered before. Here is a post I found in 2 seconds regarding issues with reading .xlsx files into R.

I recommend saving the xlsx file as a .csv and using read.csv. Read the file in like this:

data1 <- read.csv("d:/R/1.csv", stringsAsFactors=FALSE)

R is not going to recognize the appropriate formats for your date and USD columns. You can make sure to change your USD column by having . instead of ,.

Here is what you want to accomplish using reshape() with sample data since you did not provide any in R.

data1 <- data.frame(
  date=c("22.08.2015","11.08.2015","24.08.2015","28.07.2015"),
  transaction_status=c("CHARGED","DECLINED","CHARGED","DECLINED"),
  amount=c(10.96,14.7,10.61,10.96)
)

#convert your date from character to date
data1$date <- as.Date(data1$date, "%d.%m.%Y") 

#put your data in wide format
data1 <- reshape(data1, idvar="date", timevar="transaction_status", direction="wide")

#sort by date
data1 <- data1[order(data1$date),]

Is this output what you are looking for?

> data1
        date amount.CHARGED amount.DECLINED
4 2015-07-28             NA           10.96
2 2015-08-11             NA           14.70
1 2015-08-22          10.96              NA
3 2015-08-24          10.61              NA

Answer 2

It sounds like you want to group by date and transaction type (Charged/Declined).

I'm not sure if you were planning to use R for a specific reason, so I'll show you one way to do it in R and also how to do it in Excel, in case you want to save yourself some effort.

• Using R

If you plan to use R for this task, you may want to save the current Excel file 'filename.xlsx' as 'filename.csv' because R requires some additional libraries to read in Excel native files. They've never worked for me, so I just use .csv files instead.
The dummy_csv file I created is formatted like yours, so just change the code as necessary:

> dummy_csv
  transaction_ID      Date Transaction_type    Amount
1            101 12.1.2016          Charged  $270.29 
2            102  1.6.2015         Declined  $840.25 
3            103 12.1.2016          Charged  $233.26 
4            104  1.7.2016         Declined  $406.04 
5            105  1.7.2016          Charged  $886.73 
6            106 12.1.2016         Declined  $348.44 
7            107  3.8.1015          Charged  $430.09 
8            108  3.7.2015         Declined  $655.17 

Here's the code:

install.packages('dplyr') # Install dplyr if you don't have it.
library(dplyr) # Load dplyr


dummy_csv <- read.csv("~/dummy_csv.csv") # Import dataset 

dummy_csv <- tbl_df(dummy_csv) # Convert to table dataframe via dplyr

dummy_csv$Amount <- sub(pattern = "$", # Get rid of dollar signs
                    replacement = "",
                    x=as.character(dummy_csv$Amount),
                    fixed=TRUE)

dummy_csv <- mutate(dummy_csv,Amount = as.numeric(Amount)) # Make the values numeric

dummy_csv <- dummy_csv %>%
            group_by(Date,Transaction_type) %>%
            summarise('Daily_Total' = sum(Amount))

The output in R will be:

       Date Transaction_type Daily_Total
 (fctr)           (fctr)       (dbl)
1  1.6.2015         Declined      840.25
2  1.7.2016          Charged      886.73
3  1.7.2016         Declined      406.04
4 12.1.2016          Charged      503.55
5 12.1.2016         Declined      348.44
6  3.7.2015         Declined      655.17
7  3.8.1015          Charged      430.09

• Using Excel

You can accomplish the same task in Excel without using R.

1. Highlight the range of data in Excel and click on the Insert menu -> pivot table.

2. In the Pivot Table dialogue box, Drag 'Date' and 'Transaction_Type' into the Rows box. Drag 'Amount' into the Values box, and set the calculation as SUM().

The output will be the same.

Hope this helps!