ENH: IO support for R data files with `pandas.read_rdata` and `DataFrame.to_rdata`

[ParfaitG]

Currently, Pandas IO tools for binary files support largely the commercial statistical packages (SAS, Stata, SPSS). Interestingly, R binary types (.rds, .rda) are not included. Since many data science teams work between the open source stacks, some IO pandas support of R data files may be worthwhile to pursue.

I know there is some history of pandas with rpy2. However, there may be a way to integrate an IO module for R data files without optional dependency (i.e, pyreadr) but using a lightweight C library: librdata. Also, R's saveRDS uses compression types (gzip, bzip2, and xz) already handled with pandas io.

Thanks to the authors of pyreadr and librdata (not unlike the sas7bdat authors for read_sas or PyDTA authors for read_stata), I was able to implement a demo on an uncompressed rds type.

R

set.seed(332020)
alpha <- c(LETTERS, letters, c(0:9))
data_tools <- c("sas", "stata", "spss", "python", "r", "julia")

random_df <- data.frame(
  group = factor(sample(data_tools, 500, replace=TRUE)),
  int = sample(1:15, 500, replace=TRUE),
  num = rnorm(500),
  char = replicate(500, paste(sample(alpha, 3, replace=TRUE), collapse="")),
  bool = sample(c(TRUE, FALSE), 500, replace=TRUE),
  date = sample(seq(as.Date("2001-01-01"), Sys.Date(), by="days"), 500, replace=TRUE),
  ts= as.POSIXct(sample(1577836800:as.integer(Sys.time()), 500, replace=TRUE), origin="1970-01-01")
)

head(random_df, 5)
#    group int        num char  bool       date                  ts
# 1      r  15  1.2012638  3kp  TRUE 2006-12-07 2020-07-04 21:06:23
# 2   spss   5  0.9627570  rf6  TRUE 2010-10-28 2020-03-22 02:04:45
# 3  julia   9 -0.7922929  t9Q FALSE 2003-01-02 2021-02-11 06:33:02
# 4  julia  14 -0.4305794  zWw  TRUE 2004-11-24 2020-01-25 11:24:20
# 5 python   5 -1.0262956  LqK  TRUE 2020-08-20 2021-01-15 23:22:05

tail(random_df, 5)
#      group int        num char  bool       date                  ts
# 496 python   2  1.1038483  xGh  TRUE 2014-01-14 2020-11-30 11:30:00
# 497    sas   1 -0.5588906  BbC  TRUE 2011-03-15 2020-11-03 06:30:08
# 498    sas   1  0.3989181  FHi  TRUE 2003-03-02 2020-01-16 08:23:56
# 499 python   1 -0.7840641  e3k FALSE 2001-10-23 2020-05-27 06:53:21
# 500   spss   7  0.2351526  Klv  TRUE 2002-05-31 2020-05-28 11:42:56

### RDS
saveRDS(random_df, "/path/to/r_df.rds", compress=FALSE)

### RDA
save(random_df, "/path/to/r_df.rda", compress=FALSE)

Python (using a Cython built module)

Parser

from rparser import Parser, Writer           # slight adjustments to pyreadr's pyx w/o 3rd party imports
import pandas as pd
from datetime import datetime as dt, timedelta

class BaseRParser(Parser):
    """
    Parses the RData or Rds file using the parser defined
    in librdata.pyx which in turn uses the C API of librdata.
    """

    def __init__(self):
        self.counter = 0
        self.is_factor = False
        self.col_names = {}
        self.row_names = {}
        self.col_types = {}
        self.col_data = {}
        self.text_vals = {}
        self.value_labels = {}


    def handle_table(self, name):
        """
        Every object in the file is called table, this method is evoked once per object.
        :param name: str: the name of the table
        """
        pass


    def handle_column(self, name, data_type, data, count):
        """
        Evoked once per each column in the table.
        :param name: str: column name, may be None
        :param data_type: object of type DataType(Enum) (defined in librdata.pyx)
        :param data: a dictionary containing the data in R vector, may be empty
        :param count: int: number of elements in the array
        """
        
        self.row_count = count

        if self.is_factor:
            data = {k:self.value_labels[v] for k,v in data.items()}
            self.is_factor = False

        if data_type == "bool":
            data = {k:True if v==1 else False for k,v in data.items()}

        if data_type == "date":
            data = {k:dt(1970,1,1,0,0) + timedelta(v) for k,v in data.items()}        

        if data_type == "datetime":
            data = {k:dt.fromtimestamp(v) for k,v in data.items()}        

        self.col_data = {**self.col_data, **{self.counter: data}}
        self.col_types[self.counter] = data_type
        self.counter += 1
  
    def handle_column_name(self, name, index):
        """
        Some times name is None in handle column but it is recovered with this method.
        :param name: str: name of the column
        :param index: int: index of the column
        """

        self.col_names[index] = name

        if index == (self.counter - 1):
            self.compile_dataframe()

    def handle_dim(self, name, data_type, data, count):
        """
        Evoked once to retrieve the number of dimensions
        :param name: str: column name, may be None
        :param data_type: object of type DataType(Enum) (defined in librdata.pyx)
        :param data: a numpy array representing the number of dimensions
        :param count: int: number of elements in the array
        """
        pass

    def handle_dim_name(self, name, index):
        """
        Get one dimension name, one at a time, for matrices, arrays, tables.
        :param name: str: name of the dimension
        :param index: int: index of the dimension
        """
        pass

    def handle_row_name(self, name, index):
        """
        Handles R dataframe's rownames
        :param name: str: name of the row
        :param index: int: index of the row
        """

        self.row_names[index] = name

    def handle_text_value(self, name, index):
        """
        For character vectors this will be called once per row and will 
        retrieve the string value for that row.
        :param name: str: string value for the row
        :param index: int: index of the row
        :return:
        """

        self.text_vals[index] = name
        if index == (self.row_count - 1):
            self.col_data = {**self.col_data, **{self.counter-1: self.text_vals}}
            self.text_vals = {} 

    def handle_value_label(self, name, index):
        """
        Factors are represented as integer vectors.
        For factors, this method is called before reading the integer data 
        in the Factor column with handle_column and will give all the 
        string values corresponding to the integer values.
        :param name: str: string value
        :param index: int: integer value
        :return:
        """
        
        self.is_factor = True
        self.value_labels[index+1] = name

    def compile_dataframe(self):
        df_data = {n:v for (k,v),n in zip(self.col_data.items(), list(self.col_names.values()))}

        self.r_dataframe = pd.DataFrame(df_data) 

parser = BaseRParser()
parser.read_rds("/path/to/r_df.rds")
parser.read_rds("/path/to/r_df.rda")

py_df = parser.r_dataframe
print(py_df)
#       group  int       num char   bool       date                  ts
# 0         r   15  1.201264  3kp   True 2006-12-07 2020-07-04 21:06:23
# 1      spss    5  0.962757  rf6   True 2010-10-28 2020-03-22 02:04:45
# 2     julia    9 -0.792293  t9Q  False 2003-01-02 2021-02-11 06:33:02
# 3     julia   14 -0.430579  zWw   True 2004-11-24 2020-01-25 11:24:20
# 4    python    5 -1.026296  LqK   True 2020-08-20 2021-01-15 23:22:05
# ..      ...  ...       ...  ...    ...        ...                 ...
# 495  python    2  1.103848  xGh   True 2014-01-14 2020-11-30 11:30:00
# 496     sas    1 -0.558891  BbC   True 2011-03-15 2020-11-03 06:30:08
# 497     sas    1  0.398918  FHi   True 2003-03-02 2020-01-16 08:23:56
# 498  python    1 -0.784064  e3k  False 2001-10-23 2020-05-27 06:53:21
# 499    spss    7  0.235153  Klv   True 2002-05-31 2020-05-28 11:42:56

# [500 rows x 7 columns]

Writer

class BaseRWriter(Writer):
    """
    Writes pandas data to the RData or Rds file using the writer defined 
    in librdata.pyx which in turn uses the C API of librdata.
    """

    def write_rds(self, path=None, file_format="rds", frame=None, frame_name=None):
        """
        write a RData or Rds file. 
        path: str: path to the file
        file_format: str: rdata or rds
        df: pandas data frame
        df_name = name of the object to write. Irrelevant if rds format.
        """ 

        py_to_r_types = {
            "int64": "INTEGER",
            "float64": "REAL",
            "bool": "LOGICAL",
            "object": "CHARACTER",
            "datetime64[ns]": "TIMESTAMP"
        }
                        
        self.open(path, file_format)
        self.set_row_count(frame.shape[0])
        self.set_table_name(frame_name)

        for col_name, col_type in zip(frame.columns.tolist(), frame.dtypes.tolist()):
            cur_type = py_to_r_types[str(col_type)]

            self.add_column(str(col_name), str(cur_type))

        for col in frame.select_dtypes(include=['datetime']).columns:
            frame[col] = frame[col].dt.tz_localize("America/Chicago").astype(int).div(10**9)

        for col_indx,((col,ser),dtype) in enumerate(zip(frame.to_dict().items(), frame.dtypes.tolist())):
            cur_type = py_to_r_types[str(dtype)]

            for row_indx, (indx, val) in enumerate(ser.items()):
                self.insert_value(row_indx, col_indx, val, cur_type)

        self.close()

writer = BaseRWriter()
writer.write_rds(
    path="/path/to/py_df.rds", 
    file_format="rds",
    frame=py_df, 
    frame_name="pandas_df"
)

R

py_df <- readRDS("/path/to//py_df.rds")

str(py_df)
# 'data.frame':	500 obs. of  7 variables:
#  $ group: chr  "r" "spss" "julia" "julia" ...
#  $ int  : int  15 5 9 14 5 9 7 7 5 10 ...
#  $ num  : num  1.201 0.963 -0.792 -0.431 -1.026 ...
#  $ char : chr  "3kp" "rf6" "t9Q" "zWw" ...
#  $ bool : logi  TRUE TRUE FALSE TRUE TRUE TRUE ...
#  $ date : 'POSIXct' num  2006-12-07 2010-10-28 2003-01-02 2004-11-24 2020-08-20 ...
#  $ ts   : 'POSIXct' num  2020-07-04 21:06:23 2020-03-22 02:04:45 2021-02-11 06:33:02 2020-01-25 11:24:20 2021-01-15 23:22:05 ...
#  - attr(*, "datalabel")= chr "pandas_df"
#  - attr(*, "var.labels")= chr [1:7] "" "" "" "" ...
 
head(py_df, 5)
#    group int        num char  bool       date                  ts
# 1      r  15  1.2012638  3kp  TRUE 2006-12-07 2020-07-04 21:06:23
# 2   spss   5  0.9627570  rf6  TRUE 2010-10-28 2020-03-22 02:04:45
# 3  julia   9 -0.7922929  t9Q FALSE 2003-01-02 2021-02-11 06:33:02
# 4  julia  14 -0.4305794  zWw  TRUE 2004-11-24 2020-01-25 11:24:20
# 5 python   5 -1.0262956  LqK  TRUE 2020-08-20 2021-01-15 23:22:05

tail(py_df, 5)
#      group int        num char  bool       date                  ts
# 496 python   2  1.1038483  xGh  TRUE 2014-01-14 2020-11-30 11:30:00
# 497    sas   1 -0.5588906  BbC  TRUE 2011-03-15 2020-11-03 06:30:08
# 498    sas   1  0.3989181  FHi  TRUE 2003-03-02 2020-01-16 08:23:56
# 499 python   1 -0.7840641  e3k FALSE 2001-10-23 2020-05-27 06:53:21
# 500   spss   7  0.2351526  Klv  TRUE 2002-05-31 2020-05-28 11:42:56

[bashtage]

Are R data files commonly used to data exchange? One of the arguments for SAS and Stata are that is it unfortunately common to see organizations publishing datasets in these formats. I can't see I've ever seen RDS used in this way.

[dsaxton]

@ParfaitG Interesting proposal. We would have to be a bit careful with naming here, as read_rds could very easily lead to confusion with AWS RDS.

[ParfaitG]

Good point @bashtage! Perhaps since R is a programming language or environment and not traditional software with proprietary types, the .rds format is not traditionally used in data exchange. Raw data and code would be enough to reproduce end use data.

However, anecdotally among internal teams, many advanced useRs use this format to save cleaned data, plotting data, modeling results, etc. due to its efficiency as a binary and compressed serialization type without need to parse text files and detect types. Also, the .rda format is the dominant data storage format in R packages. And this need is routinely asked on StackOverflow usually for rpy2 (requires R installed) or pyreadr solutions. A convenience pandas handler can help the open source ecosystem.

@dsaxton, I didn't think of that name collision. We can call it read_rdata especially since the aforementioned C library can read and write .rds (a single R object) and .rda (multiple R objects) types.

[ParfaitG]

To underscore the regularly asked need on StackOverflow throughout the years:

• 2020: Can I convert a .rda file to a pandas dataframe in python without using R?
• 2020: Convert Pandas df to rda file
• 2020: How to read a (.RDS) file from a URL in python?
• 2019: How to read .rds R files with spatial data in python
• 2019: importing rds files in python to be read as a dataframe
• 2019: How to export a list of dataframes from R to Python?
• 2018: Loading .RData files into Python
• 2018: Loading a .rds file in Pandas
• 2018: How to load R's .rdata files into Python?
• 2017: How can I import a data frame from R saved as RData to pandas?
• 2017: converting .rda to pandas dataframe
• 2017: rpy2 load multiple DataFrames from a .RData file into pandas
• 2017: python dataframe write to R data format
• 2016: reading RDa file in python as a pandas data frame
• 2016: How to convert rdat/rdata xts files to python pandas native time series files?
• 2015: Load R data frame into Python and convert to Pandas data frame
• 2015: Reading .RData files into python using rpy2
• 2015: How to access a matrix in an .Rdata file in Python using rpy2
• 2014: Loading .RData files into Python
• 2014: Python Pandas to R dataframe
• 2014: How can I import R dataframes into Pandas?
• 2014: Is there a way to store a pandas data frame in R format?
• 2013: Can I use rpy2 to save a pandas dataframe to an .Rdata file?
• 2013: Writing a RDS file outside R

[bashtage]

I think supporting R data files is reasonable. The big question would be how shoudl support be added. Would it be better to take a soft dep on pyreadr like pandas does for most IO (e.g., openpyxl for Excel)? This way it will work as expected if this library is available. It saves the cost of maintaining a vendored code snippet and keeping it synced upstream. The downside is that new releases on a soft dep can break CI.

[ParfaitG]

Thanks, @bashtage, my thoughts it to bypass pyreadr altogether which is simply a Python wrapper to the C library, librdata. We only need to borrow and moderately adjust their Cython code (.pxd and .pyx), crediting authors.

Specifically, the pandas plan would include for no external dependencies:

1. Store librdata's C src files (12 files at 102 kb) either in pandas._lib or pandas.io.rdata, crediting author.
   (Rarely will this library need update as rds and rda are sacrosanct R core types.)
2. Cythonize with .pxd and .pyx for the r_parser module (i.e., pandas.io.sas cythonizes its _sas module).
3. Integrate r_parser in new pandas.io.rdata module as demonstrated above (with updated results).

Now, would the pandas team be open to a new io C extension? r_parser.so builds at same size as _sas.so (~1 mb).

[ofajardo]

pyreadr developer here. I personally would suggest to use pyreadr as soft dep. It is not correct that rds and rda formats do not change ,they do with major and minor versions of R and these changes are undocumented (see for example here, here, here). And we are still improving as we cannot read all existing features, as again everything is undocumented. That means if you do do your own code base you will have to maintain it (maintenance would be completely on your side since I don't have capacity to maimtain two code bases)

I also develop pyreadstat, pandas is using it as soft dep for read_spss and that approach seems to be working really well.

Of course pyreadr is an opensource project, so you are free take the code. However take into account that the license of pyreadr is very restrictive, I am not sure what kind of license pandas has, but you have to ensure that the restrictions for these pieces of code, even if they become detached from pyreadr stay as strict as they are now. You will also need to distribe the pyreadr license and attached licenses toghether with pandas license. I will also ask you to do the first commit with my github handle so that I appear as contributor to the repo.

[ParfaitG]

Thank you, @ofajardo, for your input! First, do be aware you can take the lead on a PR for this proposed IO module. As an author who relies on pandas, why not become an original contributor? If using soft dep approach, you can follow the similar setup of pandas.io.spss.

Given your response, here are my thoughts:

• Circular Imports: If using pyreadr as soft dep, we will be building a pandas module that imports a package that imports pandas. Many of the imported modules used in other pandas IO modules (beautifulsoup, lxml, openpyxl, etc.) do not import pandas or serve as wrapper to pandas. The contributor to read_spss may not have known about the underlying C library and proposed a packaged solution. I had hoped to streamline this with direct connection to lower level librdata.

• Dependency Restriction: One of the great aspects of pandas is that it is a full service solution right off the shelf. Currently the SAS and Stata IO modules does not require an additional pip-installed package to work. How can we allow that same convenience for the rdata IO module? Surely, as open source stacks, we can better integrate a data exchange solution between Python and R? Also, the sas7bdat and PyData/StatsModels authors worked with pandas to build a viable solution from original scripts without dependencies. See topline credits on sas (which also uses an author license) and stata scripts. I had hoped to work with you on a similar arrangement with integration of your Cython module.

• Avoid Redundancy: Pandas IO API, mature now for years, includes a suite of IO handling including reading from URL, FTP, storage options like Amazon S3 buckets, file-like objects, buffers, and various compression types with likely additional future features. Additionally, pandas DataFrame API can handle timezone handling, data type migration, categorical dtype, and other nuanced needs with underlying core functions not available to pandas package end users. I had hoped to avoid this overlap of functionality and ensure consistency across other pandas IO modules.

• Librdata Functionality: As mentioned earlier, rds and rda files rarely (not never) changes. But any future issues pandas users encounter, we can report directly to librdata authors much like pyreadr does or make necessary changes to Python, Cython, and C files. Also, as a proficient R user (gold badge holder on StackOverflow in R, Python, and pandas), I well understand the object model of R and so can adjust or recommend workarounds. As a soft dep, pyreadr limits the use of librdata to its needs, assumptions, and abilities (i.e., timezone, rownames). I had hoped to utilize the full functionality with direct access to librdata.

• PEP Standards: Pandas mostly stays current on Python's PEP standards across its functionality including type hinting/annotation, avoidance of relative imports, etc. A brief review of pyreadr does not indicate adherence to current PEP standards. I also see some need to update older uses such as OrderedDict (may not be needed for Python 3.6+ recommended for pandas users) and % operator for string formatting (de-emphasized in Python 3 but not deprecated yet). Possibly, too, code base can integrate pythonic semantics like list/dict comprehensions, generators, f-strings, and others. With rigorous quality standards of pandas using mypy, styling, and CI testing, I had hoped to ensure a robust rdata IO module.

With that said, thank you for authoring various data exchange packages in the pandas ecosystem over the years! From SO posts above, many have been grateful. I am looking into other solutions to build this specific IO support and I may have a different approach in mind.

[bashtage]

Circular Imports: If using pyreadr as soft dep, we will be building a pandas module that imports a package that imports pandas. Many of the imported modules used in other pandas IO modules (beautifulsoup, lxml, openpyxl, etc.) do not import pandas or serve as wrapper to pandas. The contributor to read_spss may not have known about the underlying C library and proposed a packaged solution. I had hoped to streamline this with direct connection to lower level librdata.

It is important to acknowledge that there is a non-trivial developer cost to streamlining. There are three options here:

1. Do nothing and redirect users to pyreadr. This only requires a one-off documentation change
2. Soft depencency on pyread r. Pretty minimal code changes, and as robust as pyreadr is still maintained
3. Vendor the C library. Requires maintenance is in most cases will only work in pyreadr also works if there is a change that requires altering the underlying library code.

Dependency Restriction: One of the great aspects of pandas is that it is a full service solution right off the shelf.

More IO formats do than don't. An incomplete list of formats that require a soft dep:

• Excel
• HDF
• Arrow
• HTML
• Clipboard

Avoid Redundancy: Pandas IO API, mature now for years, includes a suite of IO handling including reading from URL, FTP, storage options like Amazon S3 buckets, file-like objects, buffers, and various compression types with likely additional future features. Additionally, pandas DataFrame API can handle timezone handling, data type migration, categorical dtype, and other nuanced needs with underlying core functions not available to pandas package end users. I had hoped to avoid this overlap of functionality and ensure consistency across other pandas IO modules.

I don't see how this argues against a soft dependency. pandas could take it as a soft dep and still provide a uniform API on top, including building any missing features, or converting between what the dep prefers and what pandas prefers.

Librdata Functionality: As mentioned earlier, rds and rda files rarely (not never) changes.

There have been about a page full of commits in the past year. If these are all necessary then it seems to drift around a bit.

• PEP Standards: Pandas mostly stays current on Python's PEP standards across its functionality including type hinting/annotation, avoidance of relative imports, etc. A brief review of pyreadr does not indicate adherence to current PEP standards. I also see some need to update older uses such as OrderedDict (may not be needed for Python 3.6+ recommended for pandas users) and % operator for string formatting (de-emphasized in Python 3 but not deprecated yet). Possibly, too, code base can integrate pythonic semantics like list/dict comprehensions, generators, f-strings, and others. With rigorous quality standards of pandas using mypy, styling, and CI testing, I had hoped to ensure a robust rdata IO module.

I'm don't think this is much of an argument against the soft dep approach. Each package is allowed to have their own accepted code style. NumPy is pretty far from "full" PEP yet no one suggests not building on NumPy.

[bashtage]

You also seem to acknowledge pyreadr in your Cython above. You cannot use any code from pyreadr since it is GPL. A vendored version will need to have a clean-sheet implementation that directly wraps the C library without using code from pyreadr.