Example of Exchanging data between Python and R with Feather

Background

R and Python are widely used tools by data analysts. A recent survey reported that each is used by approximately 50% of those questions and usage of both is increasing. It is generally recognised both are complimetary and there are many cases where we can benefit from using both languages side-by-side in an analysis in order to captialise upon applications where one outperforms the other. A series of blogs reported on how to integrate Python and R.

Problem

We have recently been working on an analysis wherein we are required to tidy data which is provided in the form of an Excel spreadsheet. We have experience in using the dplyr pacakge in R which is excellent for simplicity of writing code (although other packages outperform it for very large data sets). Hence, there is a benefit to using R.

Several packages exist in R for reading Excel files. However, to date, we have not been able to find one which can read cell formats of ‘xlsx’ files. We are required to identify ‘struck out’ cells which we are not able to do in R. Previously, we performed a manual step to record the strike outs using an Excel macro. However, this impairs the reproducibility of the analysis since this step is not explicitly coded in the literate program.

Python does provide a more advanced Excel reader, called openpyxl, which is capable of reading any cell formatting, including strikethroughs. The objective of this example is to show how we pass data between R and Python using feather-format

Read, Interpret and Write Data with Python

The libraries used are:

• openpyxl python library to read and interepret the Excel spreadsheet.
• pandas python library to convert the data to a dataframe.
• feather python library to the write the dataframe in a fast read/write format which is interchanageable between R and Python.

An aside: we handle carriage returns in headers by removing them since carriage returns in variable names is not a good idea.

You need access to the example.xlsx file to run the code below.

# import libraries ------------------------------------------------------------
from openpyxl import load_workbook
from openpyxl.styles import Font
import pandas as pd 
import feather as ft

# load workbook ---------------------------------------------------------------

wb = load_workbook('./example.xlsx', data_only = True, read_only = True)
ws = wb.get_sheet_by_name('Sheet1')

# get header ------------------------------------------------------------------
header_row = 4
hdr = [elem.value for elem in ws[header_row]]
hdr = [' '.join(elem.splitlines()) if elem else elem for elem in hdr]

# read values and strike-out status -------------------------------------------
val = [(cell.value if cell.value else "" for cell in row) for \
       row in ws.iter_rows(min_row = header_row + 1)]
stk = [(bool(cell.font.strike) if cell.font else False for cell in row) for \
        row in ws.iter_rows(min_row = header_row + 1)]

# write feather files ---------------------------------------------------------
for data in (("val", val), ("stk", stk)):
    df = pd.DataFrame(data[1], columns = hdr)
    print(data[0], "data\n", df, "\n")
    ft.write_dataframe(df, "exp_%s.feather" % data[0])

val data
    A  B   C D -some muppet put a carriage return  NaN E
0  1  2   3                                    A    3  
1  4  5   6                                    1    3  
2  8  9  10                                  1.4    4   

stk data
        A      B     C D -some muppet put a carriage return   NaN      E
0  False  False  True                                False  True  False
1  False   True  True                                False  True  False
2   True   True  True                                False  True  False 

Read Feather with R

Now, we are able to read the data into R. Note that we could have used a CSV file to record the data. However, for large data sets, this would have been much slower.

library(feather)
val <- read_feather("exp_val.feather")

Warning: Coercing int64 to double
Warning: Coercing int64 to double
Warning: Coercing int64 to double
Warning: Coercing int64 to double

stk <- read_feather("exp_stk.feather")
print(val); print(stk)