Monday, June 24, 2013

Merging Data -- SAS, R, and Python

On analyticbridge, the question was posed about moving an inner join from Excel (which was taking many minutes via VLOOKUP()) to some other package. The question asked what types of performance can be expected in other systems. Of the list given, I have varying degrees of experience in SAS, R, and Python.

In the question, the user has about 80,000 records that match between 2 tables and need to be merged by a character key.

To start, let's create 2 tables in SAS. Each will have 150,000 records and there will be 80,000 overlapping records. We will randomize the tables (obviously the merge is trivial when the tables are sorted with the matching records on top) to be more "real world."

data
A;

format
address $32. a $8.;

a = "1";

do
i=1to150000;

rand = ranuni(123);

if
i <=80000thendo;

address = put(i,z32.);

end;

elsedo;

address = "AAA"
|| put(i,z29.);

end;

output;

end;

run;

data
B;

format
address $32. b $8.;

b = "1";

do
i=1to150000;

rand = ranuni(234);

if
i <=80000thendo;

address = put(i,z32.);

end;

elsedo;

address = "BBB"
|| put(i,z29.);

end;

output;

end;

run;

procsortdata=a;

by
rand;

run;

procsortdata=b;

by
rand;

run;

Now SAS pages tables to the hard drive. Good and bad. Python and R will be starting with the tables in memory, so we use SASFILE to load the tables into the main memory. Also note that SAS is writing the result table to the HD. We will do the same in Python and R.

594 sasfile a load;

NOTE:
The file WORK.A.DATA has been loaded into memory by the SASFILE statement.

595 sasfile b load;

NOTE:
The file WORK.B.DATA has been loaded into memory by the SASFILE statement.

596

597 proc sql noprint;

598 create table temp.tableA_B as

599 select a.address,

600 a.a,

601 b.b

602 from a inner join

603 b

604 on a.address = b.address;

NOTE:
Table TEMP.TABLEA_B created, with 80000 rows and 3 columns.

605 quit;

NOTE:
PROCEDURE SQL used (Total process time):

real time 0.09 seconds

cpu time 0.09 seconds

So SAS took 0.09 seconds. Much faster than the many minutes in Excel.

In R this is trivial. I wrote the tables to csv files (not shown). So we will read them in, do the merge, and then save the result as an .rda file.

> system.time({

+ a =
read.delim('c://temp//a.csv',header=T,sep=',')

+ b =
read.delim('c://temp//b.csv',header=T,sep=',')

+ })

user system elapsed

9.03 0.06
9.09

> system.time({

+ m =
merge(a[c("address","a")],b[c("address","b")])

+ })

user system elapsed

2.15 0.02
2.17

> system.time({

+ save(m,
file="c://temp//tableA_B.rda")

+ })

user system elapsed

0.21 0.00
0.20

R took 2.17 seconds for the merge and 0.20 seconds to write. A total of 2.37 seconds.

I am least familiar with the optimal way to do this in Python. I have a question to my Python guru about the optimal way to do the merge. For the time being, my attempt is here. The basics is to read the files into a Dictionary with the address string as the key and a basic object as the value. Then iterate over the keys in 1 table and see if they are in the second table. If so, add the merged data to a new dictionary.

Outputting the merged data might be faster in a list instead of a dictionary. The index hash is not being built in SAS or R.

C:\Temp>c:\Python27\python.exe
merge.py

2.4200000763 Starting Merge

Took 0.2660000324 seconds to
merge, 1.1210000515 total with pickle

Python took 0.27 seconds for the merge and a total of 1.12 seconds for the merge and write.

A final note, here is a screen grab of the resulting file sizes. R wins hands down -- I assume there is some compression going on there.

This example is fairly trivial. Hopefully, someone will find it useful while trying to learn one of these languages.

8 comments:

HiThanks for this interesting work . But I think there is some kind of bias . In fact you are not using the same technics : Sql for SAS native for R and homemade for python . But you can do Sql also in those two ( sqldf package ).

As the "mystery poster" implies, "merge" is a sql operation, whether a union or join (of some sort). If you're a real business, that'll be SQL Server or DB2. If not, go with Postgres. And, if you're not too picky about ANSI coverage and want to work in memory, then SQlite is your 7% solution.

SAS, many decades ago, attempted to sell itself as a "one stop data management shop", with some stats thrown in for good measure. With PL/R and Postgres, one can use a (nearly) industrial strength RDBMS and R. Give it a look.

Well, Thanks for your article. Its impressive to know something about your note on SAS FINANACIAL Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on SAS FINANCIAL Course. for more info Contact-Us at@ online-training-sas.com/.

About Me

I'm an econometrician by training and an IT guy because it pays the bills. Expert in most things SAS, I'm learning the arts of other statistical languages. I dabble in R, C/C++, and occasionaly C#. One of these days I'm going to be forced into AVS. If you know what that is, you know my pain. ;-)