6 Comparison with SAS
For potential users coming from SAS this page is meant to demonstrate how different SAS operations would be performed in pandas.
If you’re new to pandas, you might want to first read through 10 Minutes to pandas to familiarize yourself with the library.
As is customary, we import pandas and numpy as follows:
In [1]: import pandas as pd
In [2]: import numpy as np
Note
Throughout this tutorial, the pandas DataFrame will be displayed by calling
df.head(), which displays the first N (default 5) rows of the DataFrame.
This is often used in interactive work (e.g. Jupyter notebook or terminal) - the equivalent in SAS would be:
proc print data=df(obs=5);
run;
6.1 Data Structures
6.1.1 General Terminology Translation
| pandas | SAS |
|---|---|
DataFrame |
data set |
| column | variable |
| row | observation |
| groupby | BY-group |
NaN |
. |
6.1.2 DataFrame / Series
A DataFrame in pandas is analogous to a SAS data set - a two-dimensional
data source with labeled columns that can be of different types. As will be
shown in this document, almost any operation that can be applied to a data set
using SAS’s DATA step, can also be accomplished in pandas.
A Series is the data structure that represents one column of a
DataFrame. SAS doesn’t have a separate data structure for a single column,
but in general, working with a Series is analogous to referencing a column
in the DATA step.
6.1.3 Index
Every DataFrame and Series has an Index - which are labels on the
rows of the data. SAS does not have an exactly analogous concept. A data set’s
row are essentially unlabeled, other than an implicit integer index that can be
accessed during the DATA step (_N_).
In pandas, if no index is specified, an integer index is also used by default
(first row = 0, second row = 1, and so on). While using a labeled Index or
MultiIndex can enable sophisticated analyses and is ultimately an important
part of pandas to understand, for this comparison we will essentially ignore the
Index and just treat the DataFrame as a collection of columns. Please
see the indexing documentation for much more on how to use an
Index effectively.
6.2 Data Input / Output
6.2.1 Constructing a DataFrame from Values
A SAS data set can be built from specified values by
placing the data after a datalines statement and
specifying the column names.
data df;
input x y;
datalines;
1 2
3 4
5 6
;
run;
A pandas DataFrame can be constructed in many different ways,
but for a small number of values, it is often convenient to specify it as
a python dictionary, where the keys are the column names
and the values are the data.
In [3]: df = pd.DataFrame({
...: 'x': [1, 3, 5],
...: 'y': [2, 4, 6]})
...:
In [4]: df
Out[4]:
x y
0 1 2
1 3 4
2 5 6
6.2.2 Reading External Data
Like SAS, pandas provides utilities for reading in data from
many formats. The tips dataset, found within the pandas
tests (csv)
will be used in many of the following examples.
SAS provides PROC IMPORT to read csv data into a data set.
proc import datafile='tips.csv' dbms=csv out=tips replace;
getnames=yes;
run;
The pandas method is read_csv(), which works similarly.
In [5]: url = 'https://raw.github.com/pydata/pandas/master/pandas/tests/data/tips.csv'
In [6]: tips = pd.read_csv(url)
In [7]: tips.head()
Out[7]:
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
Like PROC IMPORT, read_csv can take a number of parameters to specify
how the data should be parsed. For example, if the data was instead tab delimited,
and did not have column names, the pandas command would be:
tips = pd.read_csv('tips.csv', sep='\t', header=None)
# alternatively, read_table is an alias to read_csv with tab delimiter
tips = pd.read_table('tips.csv', header=None)
In addition to text/csv, pandas supports a variety of other data formats
such as Excel, HDF5, and SQL databases. These are all read via a pd.read_*
function. See the IO documentation for more details.
6.2.3 Exporting Data
The inverse of PROC IMPORT in SAS is PROC EXPORT
proc export data=tips outfile='tips2.csv' dbms=csv;
run;
Similarly in pandas, the opposite of read_csv is to_csv(),
and other data formats follow a similar api.
tips.to_csv('tips2.csv')
6.3 Data Operations
6.3.1 Operations on Columns
In the DATA step, arbitrary math expressions can
be used on new or existing columns.
data tips;
set tips;
total_bill = total_bill - 2;
new_bill = total_bill / 2;
run;
pandas provides similar vectorized operations by
specifying the individual Series in the DataFrame.
New columns can be assigned in the same way.
In [8]: tips['total_bill'] = tips['total_bill'] - 2
In [9]: tips['new_bill'] = tips['total_bill'] / 2.0
In [10]: tips.head()
Out[10]:
total_bill tip sex smoker day time size new_bill
0 14.99 1.01 Female No Sun Dinner 2 7.495
1 8.34 1.66 Male No Sun Dinner 3 4.170
2 19.01 3.50 Male No Sun Dinner 3 9.505
3 21.68 3.31 Male No Sun Dinner 2 10.840
4 22.59 3.61 Female No Sun Dinner 4 11.295
6.3.2 Filtering
Filtering in SAS is done with an if or where statement, on one
or more columns.
data tips;
set tips;
if total_bill > 10;
run;
data tips;
set tips;
where total_bill > 10;
/* equivalent in this case - where happens before the
DATA step begins and can also be used in PROC statements */
run;
DataFrames can be filtered in multiple ways; the most intuitive of which is using boolean indexing
In [11]: tips[tips['total_bill'] > 10].head()
Out[11]:
total_bill tip sex smoker day time size
0 14.99 1.01 Female No Sun Dinner 2
2 19.01 3.50 Male No Sun Dinner 3
3 21.68 3.31 Male No Sun Dinner 2
4 22.59 3.61 Female No Sun Dinner 4
5 23.29 4.71 Male No Sun Dinner 4
6.3.3 If/Then Logic
In SAS, if/then logic can be used to create new columns.
data tips;
set tips;
format bucket $4.;
if total_bill < 10 then bucket = 'low';
else bucket = 'high';
run;
The same operation in pandas can be accomplished using
the where method from numpy.
In [12]: tips['bucket'] = np.where(tips['total_bill'] < 10, 'low', 'high')
In [13]: tips.head()
Out[13]:
total_bill tip sex smoker day time size bucket
0 14.99 1.01 Female No Sun Dinner 2 high
1 8.34 1.66 Male No Sun Dinner 3 low
2 19.01 3.50 Male No Sun Dinner 3 high
3 21.68 3.31 Male No Sun Dinner 2 high
4 22.59 3.61 Female No Sun Dinner 4 high
6.3.4 Date Functionality
SAS provides a variety of functions to do operations on date/datetime columns.
data tips;
set tips;
format date1 date2 date1_plusmonth mmddyy10.;
date1 = mdy(1, 15, 2013);
date2 = mdy(2, 15, 2015);
date1_year = year(date1);
date2_month = month(date2);
* shift date to beginning of next interval;
date1_next = intnx('MONTH', date1, 1);
* count intervals between dates;
months_between = intck('MONTH', date1, date2);
run;
The equivalent pandas operations are shown below. In addition to these functions pandas supports other Time Series features not available in Base SAS (such as resampling and and custom offsets) - see the timeseries documentation for more details.
In [14]: tips['date1'] = pd.Timestamp('2013-01-15')
In [15]: tips['date2'] = pd.Timestamp('2015-02-15')
In [16]: tips['date1_year'] = tips['date1'].dt.year
In [17]: tips['date2_month'] = tips['date2'].dt.month
In [18]: tips['date1_next'] = tips['date1'] + pd.offsets.MonthBegin()
In [19]: tips['months_between'] = (tips['date2'].dt.to_period('M') -
....: tips['date1'].dt.to_period('M'))
....:
In [20]: tips[['date1','date2','date1_year','date2_month',
....: 'date1_next','months_between']].head()
....:
Out[20]:
date1 date2 date1_year date2_month date1_next months_between
0 2013-01-15 2015-02-15 2013 2 2013-02-01 25
1 2013-01-15 2015-02-15 2013 2 2013-02-01 25
2 2013-01-15 2015-02-15 2013 2 2013-02-01 25
3 2013-01-15 2015-02-15 2013 2 2013-02-01 25
4 2013-01-15 2015-02-15 2013 2 2013-02-01 25
6.3.5 Selection of Columns
SAS provides keywords in the DATA step to select,
drop, and rename columns.
data tips;
set tips;
keep sex total_bill tip;
run;
data tips;
set tips;
drop sex;
run;
data tips;
set tips;
rename total_bill=total_bill_2;
run;
The same operations are expressed in pandas below.
# keep
In [21]: tips[['sex', 'total_bill', 'tip']].head()
Out[21]:
sex total_bill tip
0 Female 14.99 1.01
1 Male 8.34 1.66
2 Male 19.01 3.50
3 Male 21.68 3.31
4 Female 22.59 3.61
# drop
In [22]: tips.drop('sex', axis=1).head()
Out[22]:
total_bill tip smoker day time size
0 14.99 1.01 No Sun Dinner 2
1 8.34 1.66 No Sun Dinner 3
2 19.01 3.50 No Sun Dinner 3
3 21.68 3.31 No Sun Dinner 2
4 22.59 3.61 No Sun Dinner 4
# rename
In [23]: tips.rename(columns={'total_bill':'total_bill_2'}).head()
Out[23]:
total_bill_2 tip sex smoker day time size
0 14.99 1.01 Female No Sun Dinner 2
1 8.34 1.66 Male No Sun Dinner 3
2 19.01 3.50 Male No Sun Dinner 3
3 21.68 3.31 Male No Sun Dinner 2
4 22.59 3.61 Female No Sun Dinner 4
6.3.6 Sorting by Values
Sorting in SAS is accomplished via PROC SORT
proc sort data=tips;
by sex total_bill;
run;
pandas objects have a sort_values() method, which
takes a list of columns to sort by.
In [24]: tips = tips.sort_values(['sex', 'total_bill'])
In [25]: tips.head()
Out[25]:
total_bill tip sex smoker day time size
67 1.07 1.00 Female Yes Sat Dinner 1
92 3.75 1.00 Female Yes Fri Dinner 2
111 5.25 1.00 Female No Sat Dinner 1
145 6.35 1.50 Female No Thur Lunch 2
135 6.51 1.25 Female No Thur Lunch 2
6.4 Merging
The following tables will be used in the merge examples
In [26]: df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'],
....: 'value': np.random.randn(4)})
....:
In [27]: df1
Out[27]:
key value
0 A -0.244914
1 B 1.237780
2 C -1.311117
3 D 1.516956
In [28]: df2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'],
....: 'value': np.random.randn(4)})
....:
In [29]: df2
Out[29]:
key value
0 B 0.710051
1 D -0.062955
2 D 1.701084
3 E -0.069660
In SAS, data must be explicitly sorted before merging. Different
types of joins are accomplished using the in= dummy
variables to track whether a match was found in one or both
input frames.
proc sort data=df1;
by key;
run;
proc sort data=df2;
by key;
run;
data left_join inner_join right_join outer_join;
merge df1(in=a) df2(in=b);
if a and b then output inner_join;
if a then output left_join;
if b then output right_join;
if a or b then output outer_join;
run;
pandas DataFrames have a merge() method, which provides
similar functionality. Note that the data does not have
to be sorted ahead of time, and different join
types are accomplished via the how keyword.
In [30]: inner_join = df1.merge(df2, on=['key'], how='inner')
In [31]: inner_join
Out[31]:
key value_x value_y
0 B 1.237780 0.710051
1 D 1.516956 -0.062955
2 D 1.516956 1.701084
In [32]: left_join = df1.merge(df2, on=['key'], how='left')
In [33]: left_join
Out[33]:
key value_x value_y
0 A -0.244914 NaN
1 B 1.237780 0.710051
2 C -1.311117 NaN
3 D 1.516956 -0.062955
4 D 1.516956 1.701084
In [34]: right_join = df1.merge(df2, on=['key'], how='right')
In [35]: right_join
Out[35]:
key value_x value_y
0 B 1.237780 0.710051
1 D 1.516956 -0.062955
2 D 1.516956 1.701084
3 E NaN -0.069660
In [36]: outer_join = df1.merge(df2, on=['key'], how='outer')
In [37]: outer_join
Out[37]:
key value_x value_y
0 A -0.244914 NaN
1 B 1.237780 0.710051
2 C -1.311117 NaN
3 D 1.516956 -0.062955
4 D 1.516956 1.701084
5 E NaN -0.069660
6.5 Missing Data
Like SAS, pandas has a representation for missing data - which is the
special float value NaN (not a number). Many of the semantics
are the same, for example missing data propagates through numeric
operations, and is ignored by default for aggregations.
In [38]: outer_join
Out[38]:
key value_x value_y
0 A -0.244914 NaN
1 B 1.237780 0.710051
2 C -1.311117 NaN
3 D 1.516956 -0.062955
4 D 1.516956 1.701084
5 E NaN -0.069660
In [39]: outer_join['value_x'] + outer_join['value_y']
Out[39]:
0 NaN
1 1.947831
2 NaN
3 1.454000
4 3.218040
5 NaN
dtype: float64
In [40]: outer_join['value_x'].sum()
Out[40]: 2.715659394442855
One difference is that missing data cannot be compared to its sentinel value. For example, in SAS you could do this to filter missing values.
data outer_join_nulls;
set outer_join;
if value_x = .;
run;
data outer_join_no_nulls;
set outer_join;
if value_x ^= .;
run;
Which doesn’t work in in pandas. Instead, the pd.isnull or pd.notnull functions
should be used for comparisons.
In [41]: outer_join[pd.isnull(outer_join['value_x'])]
Out[41]:
key value_x value_y
5 E NaN -0.06966
In [42]: outer_join[pd.notnull(outer_join['value_x'])]
Out[42]:
key value_x value_y
0 A -0.244914 NaN
1 B 1.237780 0.710051
2 C -1.311117 NaN
3 D 1.516956 -0.062955
4 D 1.516956 1.701084
pandas also provides a variety of methods to work with missing data - some of which would be challenging to express in SAS. For example, there are methods to drop all rows with any missing values, replacing missing values with a specified value, like the mean, or forward filling from previous rows. See the missing data documentation for more.
In [43]: outer_join.dropna()
Out[43]:
key value_x value_y
1 B 1.237780 0.710051
3 D 1.516956 -0.062955
4 D 1.516956 1.701084
In [44]: outer_join.fillna(method='ffill')
Out[44]:
key value_x value_y
0 A -0.244914 NaN
1 B 1.237780 0.710051
2 C -1.311117 0.710051
3 D 1.516956 -0.062955
4 D 1.516956 1.701084
5 E 1.516956 -0.069660
In [45]: outer_join['value_x'].fillna(outer_join['value_x'].mean())
Out[45]:
0 -0.244914
1 1.237780
2 -1.311117
3 1.516956
4 1.516956
5 0.543132
Name: value_x, dtype: float64
6.6 GroupBy
6.6.1 Aggregation
SAS’s PROC SUMMARY can be used to group by one or more key variables and compute aggregations on numeric columns.
proc summary data=tips nway;
class sex smoker;
var total_bill tip;
output out=tips_summed sum=;
run;
pandas provides a flexible groupby mechanism that
allows similar aggregations. See the groupby documentation
for more details and examples.
In [46]: tips_summed = tips.groupby(['sex', 'smoker'])['total_bill', 'tip'].sum()
In [47]: tips_summed.head()
Out[47]:
total_bill tip
sex smoker
Female No 869.68 149.77
Yes 527.27 96.74
Male No 1725.75 302.00
Yes 1217.07 183.07
6.6.2 Transformation
In SAS, if the group aggregations need to be used with the original frame, it must be merged back together. For example, to subtract the mean for each observation by smoker group.
proc summary data=tips missing nway;
class smoker;
var total_bill;
output out=smoker_means mean(total_bill)=group_bill;
run;
proc sort data=tips;
by smoker;
run;
data tips;
merge tips(in=a) smoker_means(in=b);
by smoker;
adj_total_bill = total_bill - group_bill;
if a and b;
run;
pandas groubpy provides a transform mechanism that allows
these type of operations to be succinctly expressed in one
operation.
In [48]: gb = tips.groupby('smoker')['total_bill']
In [49]: tips['adj_total_bill'] = tips['total_bill'] - gb.transform('mean')
In [50]: tips.head()
Out[50]:
total_bill tip sex smoker day time size adj_total_bill
67 1.07 1.00 Female Yes Sat Dinner 1 -17.686344
92 3.75 1.00 Female Yes Fri Dinner 2 -15.006344
111 5.25 1.00 Female No Sat Dinner 1 -11.938278
145 6.35 1.50 Female No Thur Lunch 2 -10.838278
135 6.51 1.25 Female No Thur Lunch 2 -10.678278
6.6.3 By Group Processing
In addition to aggregation, pandas groupby can be used to
replicate most other by group processing from SAS. For example,
this DATA step reads the data by sex/smoker group and filters to
the first entry for each.
proc sort data=tips;
by sex smoker;
run;
data tips_first;
set tips;
by sex smoker;
if FIRST.sex or FIRST.smoker then output;
run;
In pandas this would be written as:
In [51]: tips.groupby(['sex','smoker']).first()
Out[51]:
total_bill tip day time size adj_total_bill
sex smoker
Female No 5.25 1.00 Sat Dinner 1 -11.938278
Yes 1.07 1.00 Sat Dinner 1 -17.686344
Male No 5.51 2.00 Thur Lunch 2 -11.678278
Yes 5.25 5.15 Sun Dinner 2 -13.506344
6.7 Other Considerations
6.7.1 Disk vs Memory
pandas operates exclusively in memory, where a SAS data set exists on disk. This means that the size of data able to be loaded in pandas is limited by your machine’s memory, but also that the operations on that data may be faster.
If out of core processing is needed, one possibility is the
dask.dataframe
library (currently in development) which
provides a subset of pandas functionality for an on-disk DataFrame
6.7.2 Data Interop
pandas provides a read_sas() method that can read SAS data saved in
the XPORT format. The ability to read SAS’s binary format is planned for a
future release.
libname xportout xport 'transport-file.xpt';
data xportout.tips;
set tips(rename=(total_bill=tbill));
* xport variable names limited to 6 characters;
run;
df = pd.read_sas('transport-file.xpt')
XPORT is a relatively limited format and the parsing of it is not as optimized as some of the other pandas readers. An alternative way to interop data between SAS and pandas is to serialize to csv.
# version 0.17, 10M rows
In [8]: %time df = pd.read_sas('big.xpt')
Wall time: 14.6 s
In [9]: %time df = pd.read_csv('big.csv')
Wall time: 4.86 s