Enter search terms or a module, class or function name.
For potential users coming fromSASthis page is meant to demonstrate how different SAS operations would beperformed in pandas.
If you’re new to pandas, you might want to first read through10 Minutes to pandasto familiarize yourself with the library.
As is customary, we import pandas and numpy as follows:
In [1]:importpandasaspdIn [2]:importnumpyasnp
Note
Throughout this tutorial, the pandasDataFrame will be displayed by callingdf.head(), which displays the first N (default 5) rows of theDataFrame.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;
| pandas | SAS |
|---|---|
DataFrame | data set |
| column | variable |
| row | observation |
| groupby | BY-group |
NaN | . |
DataFrame /Series¶ADataFrame in pandas is analogous to a SAS data set - a two-dimensionaldata source with labeled columns that can be of different types. As will beshown in this document, almost any operation that can be applied to a data setusing SAS’sDATA step, can also be accomplished in pandas.
ASeries is the data structure that represents one column of aDataFrame. SAS doesn’t have a separate data structure for a single column,but in general, working with aSeries is analogous to referencing a columnin theDATA step.
Index¶EveryDataFrame andSeries has anIndex - which are labels on therows of the data. SAS does not have an exactly analogous concept. A data set’srow are essentially unlabeled, other than an implicit integer index that can beaccessed during theDATA 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 labeledIndex orMultiIndex can enable sophisticated analyses and is ultimately an importantpart of pandas to understand, for this comparison we will essentially ignore theIndex and just treat theDataFrame as a collection of columns. Pleasesee theindexing documentation for much more on how to use anIndex effectively.
A SAS data set can be built from specified values byplacing the data after adatalines statement andspecifying the column names.
data df; input x y; datalines; 1 2 3 4 5 6 ;run;
A pandasDataFrame can be constructed in many different ways,but for a small number of values, it is often convenient to specify it asa python dictionary, where the keys are the column namesand the values are the data.
In [3]:df=pd.DataFrame({ ...:'x':[1,3,5], ...:'y':[2,4,6]}) ...:In [4]:dfOut[4]: x y0 1 21 3 42 5 6
Like SAS, pandas provides utilities for reading in data frommany formats. Thetips dataset, found within the pandastests (csv)will be used in many of the following examples.
SAS providesPROCIMPORT to read csv data into a data set.
proc import datafile='tips.csv' dbms=csv out=tips replace; getnames=yes;run;
The pandas method isread_csv(), which works similarly.
In [5]:url='https://raw.github.com/pandas-dev/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 size0 16.99 1.01 Female No Sun Dinner 21 10.34 1.66 Male No Sun Dinner 32 21.01 3.50 Male No Sun Dinner 33 23.68 3.31 Male No Sun Dinner 24 24.59 3.61 Female No Sun Dinner 4
LikePROCIMPORT,read_csv can take a number of parameters to specifyhow 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 delimitertips=pd.read_table('tips.csv',header=None)
In addition to text/csv, pandas supports a variety of other data formatssuch as Excel, HDF5, and SQL databases. These are all read via apd.read_*function. See theIO documentation for more details.
In theDATA step, arbitrary math expressions canbe 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 byspecifying the individualSeries in theDataFrame.New columns can be assigned in the same way.
In [8]:tips['total_bill']=tips['total_bill']-2In [9]:tips['new_bill']=tips['total_bill']/2.0In [10]:tips.head()Out[10]: total_bill tip sex smoker day time size new_bill0 14.99 1.01 Female No Sun Dinner 2 7.4951 8.34 1.66 Male No Sun Dinner 3 4.1702 19.01 3.50 Male No Sun Dinner 3 9.5053 21.68 3.31 Male No Sun Dinner 2 10.8404 22.59 3.61 Female No Sun Dinner 4 11.295
Filtering in SAS is done with anif orwhere statement, on oneor 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 usingboolean indexing
In [11]:tips[tips['total_bill']>10].head()Out[11]: total_bill tip sex smoker day time size0 14.99 1.01 Female No Sun Dinner 22 19.01 3.50 Male No Sun Dinner 33 21.68 3.31 Male No Sun Dinner 24 22.59 3.61 Female No Sun Dinner 45 23.29 4.71 Male No Sun Dinner 4
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 usingthewhere method fromnumpy.
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 bucket0 14.99 1.01 Female No Sun Dinner 2 high1 8.34 1.66 Male No Sun Dinner 3 low2 19.01 3.50 Male No Sun Dinner 3 high3 21.68 3.31 Male No Sun Dinner 2 high4 22.59 3.61 Female No Sun Dinner 4 high
SAS provides a variety of functions to do operations ondate/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 thesefunctions pandas supports other Time Series featuresnot available in Base SAS (such as resampling and and custom offsets) -see thetimeseries 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.yearIn [17]:tips['date2_month']=tips['date2'].dt.monthIn [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_between0 2013-01-15 2015-02-15 2013 2 2013-02-01 251 2013-01-15 2015-02-15 2013 2 2013-02-01 252 2013-01-15 2015-02-15 2013 2 2013-02-01 253 2013-01-15 2015-02-15 2013 2 2013-02-01 254 2013-01-15 2015-02-15 2013 2 2013-02-01 25
SAS provides keywords in theDATA 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.
# keepIn [21]:tips[['sex','total_bill','tip']].head()Out[21]: sex total_bill tip0 Female 14.99 1.011 Male 8.34 1.662 Male 19.01 3.503 Male 21.68 3.314 Female 22.59 3.61# dropIn [22]:tips.drop('sex',axis=1).head()Out[22]: total_bill tip smoker day time size0 14.99 1.01 No Sun Dinner 21 8.34 1.66 No Sun Dinner 32 19.01 3.50 No Sun Dinner 33 21.68 3.31 No Sun Dinner 24 22.59 3.61 No Sun Dinner 4# renameIn [23]:tips.rename(columns={'total_bill':'total_bill_2'}).head()Out[23]: total_bill_2 tip sex smoker day time size0 14.99 1.01 Female No Sun Dinner 21 8.34 1.66 Male No Sun Dinner 32 19.01 3.50 Male No Sun Dinner 33 21.68 3.31 Male No Sun Dinner 24 22.59 3.61 Female No Sun Dinner 4
Sorting in SAS is accomplished viaPROCSORT
proc sort data=tips; by sex total_bill;run;
pandas objects have asort_values() method, whichtakes 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 size67 1.07 1.00 Female Yes Sat Dinner 192 3.75 1.00 Female Yes Fri Dinner 2111 5.25 1.00 Female No Sat Dinner 1145 6.35 1.50 Female No Thur Lunch 2135 6.51 1.25 Female No Thur Lunch 2
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]:df1Out[27]: key value0 A -0.8573261 B 1.0754162 C 0.3717273 D 1.065735In [28]:df2=pd.DataFrame({'key':['B','D','D','E'], ....:'value':np.random.randn(4)}) ....:In [29]:df2Out[29]: key value0 B -0.2273141 D 2.1027262 D -0.0927963 E 0.094694
In SAS, data must be explicitly sorted before merging. Differenttypes of joins are accomplished using thein= dummyvariables to track whether a match was found in one or bothinput 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 amerge() method, which providessimilar functionality. Note that the data does not haveto be sorted ahead of time, and different jointypes are accomplished via thehow keyword.
In [30]:inner_join=df1.merge(df2,on=['key'],how='inner')In [31]:inner_joinOut[31]: key value_x value_y0 B 1.075416 -0.2273141 D 1.065735 2.1027262 D 1.065735 -0.092796In [32]:left_join=df1.merge(df2,on=['key'],how='left')In [33]:left_joinOut[33]: key value_x value_y0 A -0.857326 NaN1 B 1.075416 -0.2273142 C 0.371727 NaN3 D 1.065735 2.1027264 D 1.065735 -0.092796In [34]:right_join=df1.merge(df2,on=['key'],how='right')In [35]:right_joinOut[35]: key value_x value_y0 B 1.075416 -0.2273141 D 1.065735 2.1027262 D 1.065735 -0.0927963 E NaN 0.094694In [36]:outer_join=df1.merge(df2,on=['key'],how='outer')In [37]:outer_joinOut[37]: key value_x value_y0 A -0.857326 NaN1 B 1.075416 -0.2273142 C 0.371727 NaN3 D 1.065735 2.1027264 D 1.065735 -0.0927965 E NaN 0.094694
Like SAS, pandas has a representation for missing data - which is thespecial float valueNaN (not a number). Many of the semanticsare the same, for example missing data propagates through numericoperations, and is ignored by default for aggregations.
In [38]:outer_joinOut[38]: key value_x value_y0 A -0.857326 NaN1 B 1.075416 -0.2273142 C 0.371727 NaN3 D 1.065735 2.1027264 D 1.065735 -0.0927965 E NaN 0.094694In [39]:outer_join['value_x']+outer_join['value_y']Out[39]:0 NaN1 0.8481022 NaN3 3.1684614 0.9729395 NaNdtype: float64In [40]:outer_join['value_x'].sum()Out[40]:2.72128653544262
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, thepd.isnull orpd.notnull functionsshould be used for comparisons.
In [41]:outer_join[pd.isnull(outer_join['value_x'])]Out[41]: key value_x value_y5 E NaN 0.094694In [42]:outer_join[pd.notnull(outer_join['value_x'])]Out[42]: key value_x value_y0 A -0.857326 NaN1 B 1.075416 -0.2273142 C 0.371727 NaN3 D 1.065735 2.1027264 D 1.065735 -0.092796
pandas also provides a variety of methods to work with missing data - some ofwhich would be challenging to express in SAS. For example, there are methods todrop all rows with any missing values, replacing missing values with a specifiedvalue, like the mean, or forward filling from previous rows. See themissing data documentation for more.
In [43]:outer_join.dropna()Out[43]: key value_x value_y1 B 1.075416 -0.2273143 D 1.065735 2.1027264 D 1.065735 -0.092796In [44]:outer_join.fillna(method='ffill')Out[44]: key value_x value_y0 A -0.857326 NaN1 B 1.075416 -0.2273142 C 0.371727 -0.2273143 D 1.065735 2.1027264 D 1.065735 -0.0927965 E 1.065735 0.094694In [45]:outer_join['value_x'].fillna(outer_join['value_x'].mean())Out[45]:0 -0.8573261 1.0754162 0.3717273 1.0657354 1.0657355 0.544257Name: value_x, dtype: float64
SAS’s PROC SUMMARY can be used to group by one ormore key variables and compute aggregations onnumeric columns.
proc summary data=tips nway; class sex smoker; var total_bill tip; output out=tips_summed sum=;run;
pandas provides a flexiblegroupby mechanism thatallows similar aggregations. See thegroupby documentationfor 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 tipsex smokerFemale No 869.68 149.77 Yes 527.27 96.74Male No 1725.75 302.00 Yes 1217.07 183.07
In SAS, if the group aggregations need to be used withthe original frame, it must be merged back together. Forexample, 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;
pandasgroubpy provides atransform mechanism that allowsthese type of operations to be succinctly expressed in oneoperation.
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_bill67 1.07 1.00 Female Yes Sat Dinner 1 -17.68634492 3.75 1.00 Female Yes Fri Dinner 2 -15.006344111 5.25 1.00 Female No Sat Dinner 1 -11.938278145 6.35 1.50 Female No Thur Lunch 2 -10.838278135 6.51 1.25 Female No Thur Lunch 2 -10.678278
In addition to aggregation, pandasgroupby can be used toreplicate most other by group processing from SAS. For example,thisDATA step reads the data by sex/smoker group and filters tothe 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_billsex smokerFemale No 5.25 1.00 Sat Dinner 1 -11.938278 Yes 1.07 1.00 Sat Dinner 1 -17.686344Male No 5.51 2.00 Thur Lunch 2 -11.678278 Yes 5.25 5.15 Sun Dinner 2 -13.506344
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 yourmachine’s memory, but also that the operations on that data may be faster.
If out of core processing is needed, one possibility is thedask.dataframelibrary (currently in development) whichprovides a subset of pandas functionality for an on-diskDataFrame
pandas provides aread_sas() method that can read SAS data saved inthe XPORT format. The ability to read SAS’s binary format is planned for afuture 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 asoptimized as some of the other pandas readers. An alternative wayto interop data between SAS and pandas is to serialize to csv.
# version 0.17, 10M rowsIn[8]:%timedf=pd.read_sas('big.xpt')Walltime:14.6sIn[9]:%timedf=pd.read_csv('big.csv')Walltime:4.86s