R and Python are two programming languages that have gained immense popularity among data scientists, statisticians, and researchers. In this blog, we will explore two widely used libraries, data.table in R and pandas in Python, both of which excel in data manipulation and provide versatile functionalities for working with data, focusing on their capabilities for reading, writing, and reshaping data. We will also explain how to graphically demonstrate the time taken by each operation. We will use the atime R package to compare and visualize the asymptotic performance (time and memory usage) of the different functions mentioned above. By comparing the asymptotic performance of these packages in these programming languages, we aim to provide insights and help data scientists make informed choices when it comes to data manipulation and analysis.

Performance testing with `atime`

The atime::atime() function requires the following arguments:

N: A sequence of parameters that control dataset sizes. Peformance tests will measure time/memory usage across the different data sizes, as measured by values of N.
setup: A code expression that will be run for each value in N to create data of various sizes.
expr: A code expression that will be evaluated for each value in N, after data creation, and tested for computational performance.
seconds.limit (optional): A cutoff time; if an expression is slower than this limit for any data size, then no larger data sizes will be measured.

Interfacing Python and R with `reticulate`

This analysis will employ the reticulate package in R, which facilitates interoperability between Python and R by providing a robust set of tools for seamless integration. Leveraging this package enables the efficient exchange of data and functionality between both languages, allowing for a more expansive approach to the analysis. The package includes facilities for:

Calling Python from R, including raw code or sourcing Python scripts and modules.
Translation between R and Python objects; for example, between R and pandas data frames, or between R matrices and NumPy arrays.
Flexible binding to different versions of Python including virtual environments and Conda environments.

The reticulate translation incurs overhead depending on Python code complexity and frequency of R-Python switches. This overhead is typically constant for small scripts, but increases with larger/complex code. The code uses reticulate to benchmark writing a dataframe to CSV using R’s data.table and Python’s pandas, with key overhead factors being Python environment initialization and data transfer between R and Python. Data transfer overhead is relatively constant due to small dataframe size and minimal changes in dataframe size during processing.

https://github.com/DorisAmoakohene/Slides-and-Blogs/blob/main/Screenshot%202024-06-26%20150315.png

Example 1: Writing a CSV File with data.table::fwrite() and pandas::to_csv()

Setup

To reproduce this analysis, you will need the packages loaded in the code chunks shown below, as well as the code to setup a virtual environment using your choice of local Python install.

library(data.table)
library(reshape2)
library(atime)
library(ggplot2)
library(reticulate)
#use_python("C:/Users/amoak/AppData/Local/Programs/Python/Python312/python.exe") #If you want to reproduce, please change to the path of python on your computer.
virtualenv_create("fm-proj")
use_virtualenv("fm-proj", required = F)

file_path = 'data.csv'

fwrite: fast CSV writer

Data.table provides the fwrite() function for writing data to a file while Pandas offers the to_csv() function for writing data to a CSV file.

Comparison code

write.colors <- c(
  "data.table::fwrite" = "#D6604D",
  "pandas.to_csv " = "#BF812D"
)
file_path = 'data.csv'
n.rows <- 100
seconds.limit <- 10
atime.write.vary.cols <- atime::atime(
  N = as.integer(10^seq(2, 10, by = 0.2)),
  setup = {
    set.seed(1)
    input.vec <- rnorm(n.rows * N)
    input.mat <- matrix(input.vec, n.rows, N)
    input.df <- data.frame(input.mat) 
    pd <- import("pandas")
    input_df_pd <- r_to_py(input.df)
  },
  seconds.limit = seconds.limit,
  "data.table::fwrite" = {
    data.table::fwrite(input.df, tempfile(), showProgress = FALSE)
  },
  "pandas.to_csv" = {
    input_df_pd$to_csv(file_path, index = FALSE)
  }
)

refs.write.vary.cols <- atime::references_best(atime.write.vary.cols)

The predict() function is used to generate predictions based on the atime::reference_best() dataset. The resulting plot illustrates the data size, N, that can be processed within a specific time or memory limit.

pred.write.vary.cols <- predict(refs.write.vary.cols)
plot(pred.write.vary.cols)

gg.write.dt.pd <- plot(pred.write.vary.cols) +
  theme(text = element_text(size = 15)) +
  ggtitle(sprintf("Write real numbers to CSV, with pandas in Python \nand data.table in R, %d x N", n.rows)) +
  scale_x_log10("N = number of columns to write") +
  scale_y_log10("Computation time (seconds)\nmedian line, min/max band\nover 10 timings") +
  facet_null() +
  scale_fill_manual(values = write.colors) +
  scale_color_manual(values = write.colors)

print(gg.write.dt.pd)

The plot above shows that in terms of writing data, the data.table package in R outperforms the pandas library in Python and particularly useful when dealing with large datasets.

Example 2: Reading a CSV File with data.table::fread() and pandas.read_csv

fread: fast CSV reader

Data.table provides the fread() function for reading data from a CSV file while Pandas offers the read_csv() function for reading data from a CSV file.

Comparison code

read.colors <- c(
  "data.table::fread" = "#D6604D",
  "pandas.read_csv" = "#BF812D"
)
n.rows <- 100
seconds.limit <- 10
file_path = 'data.csv'
atime.read <- atime::atime(
  N = as.integer(10^seq(2, 15, by = 0.2)),
  setup = {
    set.seed(1)
    input.vec <- rnorm(n.rows*N)
    input.mat <- matrix(input.vec, n.rows, N)
    input.df <- data.frame(input.mat)
    input.csv <- tempfile()
    fwrite(input.df, "data.csv")
    pd <- import("pandas")
    input_df_pd <- pd$DataFrame(input.df)
    pd <- import("pandas")
    reticulate::py_run_string("import pandas as pd")
  },
  seconds.limit = seconds.limit,
  "data.table::fread" = {
    data.table::fread("data.csv", showProgress = FALSE) 
  },
  "pandas.read_csv " = {
    reticulate::py_run_string("pd.read_csv(file_path)")  
  }
)

refs.read.vary.cols <- atime::references_best(atime.read)

pred.read.vary.cols <- predict(refs.read.vary.cols)
plot(pred.read.vary.cols)

gg.read.pd <- plot(pred.read.vary.cols)+
  theme(text=element_text(size=15))+
  ggtitle(sprintf("Read real numbers to CSV, with pandas in Python \nand data.table in R, %d x N", n.rows))+
  scale_x_log10("N = number of columns to write")+
  scale_y_log10("Computation time (seconds)
median line, min/max band
over 10 timings")+
  facet_null()+
  scale_fill_manual(values=read.colors)+
  scale_color_manual(values=read.colors)

plot(gg.read.pd)

When it comes to reading data, data.table in R also demonstrates its superiority. It provides fast and efficient methods for importing and reading various file formats, including CSV. The fread() function in data.table is known for its speed and memory efficiency, making it an optimal choice for handling large datasets.

Example 3. Reshape performance comparison.

Data reshaping means changing the shape of the data, to get it into a more appropriate format, for learning/plotting/etc. Here we consider wide to long and long to wide reshape, which means we start with a wide table (many columns) and end up with a long table (fewer columns) and vice versa.

A. wide to long reshape.

In data.table, the data.table::melt() function is used to convert data from a wide format to a long format, while in Pandas, the pandas::melt() function is used to convert data from a wide format to a long format.

Comparison code

data.table::melt() is faster

ml.colors <- c(
  "data.table::melt"="#D6604D",
  "pd.melt" = "#BF812D"
  )
n.folds <- 10
n.rows <- 100
seconds.limit <- 10
ml.reshape.atime <- atime::atime(
  N=as.integer(10^seq(2, 15, by=0.2)),
  setup={
    df <- data.frame(
      id = rep(1:N, each = 2),
      category = rep(c("A", "B"), N),
      value = rnorm(2 * N)
    py_df <- reticulate::r_to_py(df)
    pd <- import("pandas")
      )
    },
  seconds.limit= seconds.limit,
  "data.table::melt" = {
    data.table::melt(data.table(df), id.vars = c("id",  "category"),variable.names="variable", value.name = "value")
  },
  "pd.melt" = {
    pd$melt(py_df, id_vars = c("id", "category"), value.name = "score")  
  }
  )

ml.reshape.refs <- atime::references_best(ml.reshape.atime)

ml.reshape.pred <- predict(ml.reshape.refs)
plot(ml.reshape.pred)

ml.wide2long.pd <- plot(ml.reshape.pred)+
  theme(text=element_text(size=15))+
  ggtitle(sprintf("Reshaping from wide to long panda & data.table \nover real numbers, N times", n.folds))+
  scale_x_log10("N = number of Mean,SD,Length to compute")+
  scale_y_log10("Computation time (seconds)
median line, min/max band
over 10 timings")+
  facet_null()+
  scale_fill_manual(values=ml.colors)+
  scale_color_manual(values=ml.colors)

plot(ml.wide2long.pd)

When converting data from wide to long format, as shown in the above plot, data.table’s melt() function efficiently gathers multiple columns into key-value pairs, allowing for easy transformation of wide data into a longer, more structured format.

B. long to wide reshape

In data.table, the data.table::dcast() function is often used to convert data from a long format to a wide format, and in pandas, the pd$pivot_table() function is used to convert data from a long format to a wide format.

Comparison code

data.table::dcast() is faster

ml.colors <- c(
  "data.table::dcast" = "#D6604D",
  "pd$pivot_table" = "#BF812D"
)
n.folds <- 10
n.rows <- 100
seconds.limit <- 1
ml.long2wide.atime <- atime::atime(
  N=as.integer(10^seq(2, 7, by=0.2)),
  setup={
    df <- data.frame(
      id = rep(1:N, each = 2),
      category = rep(c("A", "B"), N),
      value = rnorm(2 * N)
     py_df <- reticulate::r_to_py(df)
     pd <- import("pandas")
      )
    },
  seconds.limit= seconds.limit,
  "data.table::dcast" = {
    data.table::dcast(data.table(df), id ~ category, value.var = "value")
  },
  "pd$pivot_table" = {
    pd$pivot_table(py_df, values = "value", index = "id", columns = "category")
  }
  )

ml.long2wide.refs <- atime::references_best(ml.long2wide.atime)

ml.long2wide.pred <- predict(ml.long2wide.refs)
plot(ml.long2wide.pred)

ml.long2wide <- plot(ml.long2wide.pred)+
  theme(text=element_text(size=15))+
  ggtitle(sprintf("Reshaping from long to wide over \nreal numbers, N times", n.folds))+
  scale_x_log10("N = number of Mean,SD,Length to compute")+
  scale_y_log10("Computation time (seconds)
median line, min/max band
over 10 timings")+
  facet_null()+
  scale_fill_manual(values=ml.colors)+
  scale_color_manual(values=ml.colors)

plot(ml.long2wide)

On the other hand, when transforming data from long to wide format, data.table’s dcast() function proves to be more efficient.

Although data.table has advantages in terms of writing, reading and rehaping, pandas library in Python still provides a solid framework for data manipulation as seen in the plot.

Conclusions

In conclusion, we have shown how to use atime to compare asymptotic time of the two packages. Both Pandas and data.table are powerful libraries for data manipulation in Python and R respectively. Pandas offers a comprehensive set of functions and a user-friendly interface, making it suitable for a wide range of data analysis tasks. On the other hand, Data.table excels in terms of performance and memory efficiency, making it an excellent choice for handling large datasets and complex operations.

The choice between the two libraries ultimately depends on the specific requirements of your data manipulation tasks. It is recommended to consider the size of the dataset, the complexity of the operations, and personal preferences when making a decision.