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
pandas
SAS
DataFrame
data set
column
variable
row
observation
groupby
BY-group
NaN
.
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.
DATA
Series
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.
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 rows are essentially unlabeled, other than an implicit integer index that can be accessed during the DATA step (_N_).
_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.
MultiIndex
Most pandas operations return copies of the Series/DataFrame. To make the changes “stick”, you’ll need to either assign to a new variable:
sorted_df = df.sort_values("col1")
or overwrite the original one:
df = df.sort_values("col1")
Note
You will see an inplace=True keyword argument available for some methods:
inplace=True
df.sort_values("col1", inplace=True)
Its use is discouraged. More information.
A SAS data set can be built from specified values by placing the data after a datalines statement and specifying the column names.
datalines
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 [1]: df = pd.DataFrame({"x": [1, 3, 5], "y": [2, 4, 6]}) In [2]: df Out[2]: x y 0 1 2 1 3 4 2 5 6
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.
tips
SAS provides PROC IMPORT to read csv data into a data set.
PROC IMPORT
proc import datafile='tips.csv' dbms=csv out=tips replace; getnames=yes; run;
The pandas method is read_csv(), which works similarly.
read_csv()
In [3]: url = ( ...: "https://raw.github.com/pandas-dev/" ...: "pandas/master/pandas/tests/io/data/csv/tips.csv" ...: ) ...: In [4]: tips = pd.read_csv(url) In [5]: tips Out[5]: 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 .. ... ... ... ... ... ... ... 239 29.03 5.92 Male No Sat Dinner 3 240 27.18 2.00 Female Yes Sat Dinner 2 241 22.67 2.00 Male Yes Sat Dinner 2 242 17.82 1.75 Male No Sat Dinner 2 243 18.78 3.00 Female No Thur Dinner 2 [244 rows x 7 columns]
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:
read_csv
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.
pd.read_*
By default, pandas will truncate output of large DataFrames to show the first and last rows. This can be overridden by changing the pandas options, or using DataFrame.head() or DataFrame.tail().
DataFrame.head()
DataFrame.tail()
In [1]: tips.head(5) Out[1]: 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
The equivalent in SAS would be:
proc print data=df(obs=5); run;
The inverse of PROC IMPORT in SAS is PROC EXPORT
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.
to_csv()
tips.to_csv("tips2.csv")
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 vectorized operations by specifying the individual Series in the DataFrame. New columns can be assigned in the same way. The DataFrame.drop() method drops a column from the DataFrame.
DataFrame.drop()
In [1]: tips["total_bill"] = tips["total_bill"] - 2 In [2]: tips["new_bill"] = tips["total_bill"] / 2 In [3]: tips Out[3]: 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 .. ... ... ... ... ... ... ... ... 239 27.03 5.92 Male No Sat Dinner 3 13.515 240 25.18 2.00 Female Yes Sat Dinner 2 12.590 241 20.67 2.00 Male Yes Sat Dinner 2 10.335 242 15.82 1.75 Male No Sat Dinner 2 7.910 243 16.78 3.00 Female No Thur Dinner 2 8.390 [244 rows x 8 columns] In [4]: tips = tips.drop("new_bill", axis=1)
Filtering in SAS is done with an if or where statement, on one or more columns.
if
where
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 [1]: tips[tips["total_bill"] > 10] Out[1]: 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 .. ... ... ... ... ... ... ... 239 27.03 5.92 Male No Sat Dinner 3 240 25.18 2.00 Female Yes Sat Dinner 2 241 20.67 2.00 Male Yes Sat Dinner 2 242 15.82 1.75 Male No Sat Dinner 2 243 16.78 3.00 Female No Thur Dinner 2 [204 rows x 7 columns]
The above statement is simply passing a Series of True/False objects to the DataFrame, returning all rows with True.
True
False
In [1]: is_dinner = tips["time"] == "Dinner" In [2]: is_dinner Out[2]: 0 True 1 True 2 True 3 True 4 True ... 239 True 240 True 241 True 242 True 243 True Name: time, Length: 244, dtype: bool In [3]: is_dinner.value_counts() Out[3]: True 176 False 68 Name: time, dtype: int64 In [4]: tips[is_dinner] Out[4]: total_bill 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 .. ... ... ... ... ... ... ... 239 27.03 5.92 Male No Sat Dinner 3 240 25.18 2.00 Female Yes Sat Dinner 2 241 20.67 2.00 Male Yes Sat Dinner 2 242 15.82 1.75 Male No Sat Dinner 2 243 16.78 3.00 Female No Thur Dinner 2 [176 rows x 7 columns]
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.
numpy
In [1]: tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high") In [2]: tips Out[2]: 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 .. ... ... ... ... ... ... ... ... 239 27.03 5.92 Male No Sat Dinner 3 high 240 25.18 2.00 Female Yes Sat Dinner 2 high 241 20.67 2.00 Male Yes Sat Dinner 2 high 242 15.82 1.75 Male No Sat Dinner 2 high 243 16.78 3.00 Female No Thur Dinner 2 high [244 rows x 8 columns]
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 custom offsets) - see the timeseries documentation for more details.
In [1]: tips["date1"] = pd.Timestamp("2013-01-15") In [2]: tips["date2"] = pd.Timestamp("2015-02-15") In [3]: tips["date1_year"] = tips["date1"].dt.year In [4]: tips["date2_month"] = tips["date2"].dt.month In [5]: tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin() In [6]: tips["months_between"] = tips["date2"].dt.to_period("M") - tips[ ...: "date1" ...: ].dt.to_period("M") ...: In [7]: tips[ ...: ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"] ...: ] ...: Out[7]: date1 date2 date1_year date2_month date1_next months_between 0 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 1 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 2 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 3 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 4 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> .. ... ... ... ... ... ... 239 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 240 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 241 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 242 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> 243 2013-01-15 2015-02-15 2013 2 2013-02-01 <25 * MonthEnds> [244 rows x 6 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.
In [1]: tips[["sex", "total_bill", "tip"]] Out[1]: 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 .. ... ... ... 239 Male 27.03 5.92 240 Female 25.18 2.00 241 Male 20.67 2.00 242 Male 15.82 1.75 243 Female 16.78 3.00 [244 rows x 3 columns]
In [2]: tips.drop("sex", axis=1) Out[2]: 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 .. ... ... ... ... ... ... 239 27.03 5.92 No Sat Dinner 3 240 25.18 2.00 Yes Sat Dinner 2 241 20.67 2.00 Yes Sat Dinner 2 242 15.82 1.75 No Sat Dinner 2 243 16.78 3.00 No Thur Dinner 2 [244 rows x 6 columns]
In [1]: tips.rename(columns={"total_bill": "total_bill_2"}) Out[1]: 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 .. ... ... ... ... ... ... ... 239 27.03 5.92 Male No Sat Dinner 3 240 25.18 2.00 Female Yes Sat Dinner 2 241 20.67 2.00 Male Yes Sat Dinner 2 242 15.82 1.75 Male No Sat Dinner 2 243 16.78 3.00 Female No Thur Dinner 2 [244 rows x 7 columns]
Sorting in SAS is accomplished via PROC SORT
PROC SORT
proc sort data=tips; by sex total_bill; run;
pandas has a DataFrame.sort_values() method, which takes a list of columns to sort by.
DataFrame.sort_values()
In [1]: tips = tips.sort_values(["sex", "total_bill"]) In [2]: tips Out[2]: 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 .. ... ... ... ... ... ... ... 182 43.35 3.50 Male Yes Sun Dinner 3 156 46.17 5.00 Male No Sun Dinner 6 59 46.27 6.73 Male No Sat Dinner 4 212 46.33 9.00 Male No Sat Dinner 4 170 48.81 10.00 Male Yes Sat Dinner 3 [244 rows x 7 columns]
SAS determines the length of a character string with the LENGTHN and LENGTHC functions. LENGTHN excludes trailing blanks and LENGTHC includes trailing blanks.
LENGTHN
LENGTHC
data _null_; set tips; put(LENGTHN(time)); put(LENGTHC(time)); run;
You can find the length of a character string with Series.str.len(). In Python 3, all strings are Unicode strings. len includes trailing blanks. Use len and rstrip to exclude trailing blanks.
Series.str.len()
len
rstrip
In [1]: tips["time"].str.len() Out[1]: 67 6 92 6 111 6 145 5 135 5 .. 182 6 156 6 59 6 212 6 170 6 Name: time, Length: 244, dtype: int64 In [2]: tips["time"].str.rstrip().str.len() Out[2]: 67 6 92 6 111 6 145 5 135 5 .. 182 6 156 6 59 6 212 6 170 6 Name: time, Length: 244, dtype: int64
SAS determines the position of a character in a string with the FINDW function. FINDW takes the string defined by the first argument and searches for the first position of the substring you supply as the second argument.
FINDW
data _null_; set tips; put(FINDW(sex,'ale')); run;
You can find the position of a character in a column of strings with the Series.str.find() method. find searches for the first position of the substring. If the substring is found, the method returns its position. If not found, it returns -1. Keep in mind that Python indexes are zero-based.
Series.str.find()
find
-1
In [1]: tips["sex"].str.find("ale") Out[1]: 67 3 92 3 111 3 145 3 135 3 .. 182 1 156 1 59 1 212 1 170 1 Name: sex, Length: 244, dtype: int64
SAS extracts a substring from a string based on its position with the SUBSTR function.
data _null_; set tips; put(substr(sex,1,1)); run;
With pandas you can use [] notation to extract a substring from a string by position locations. Keep in mind that Python indexes are zero-based.
[]
In [1]: tips["sex"].str[0:1] Out[1]: 67 F 92 F 111 F 145 F 135 F .. 182 M 156 M 59 M 212 M 170 M Name: sex, Length: 244, dtype: object
The SAS SCAN function returns the nth word from a string. The first argument is the string you want to parse and the second argument specifies which word you want to extract.
data firstlast; input String $60.; First_Name = scan(string, 1); Last_Name = scan(string, -1); datalines2; John Smith; Jane Cook; ;;; run;
The simplest way to extract words in pandas is to split the strings by spaces, then reference the word by index. Note there are more powerful approaches should you need them.
In [1]: firstlast = pd.DataFrame({"String": ["John Smith", "Jane Cook"]}) In [2]: firstlast["First_Name"] = firstlast["String"].str.split(" ", expand=True)[0] In [3]: firstlast["Last_Name"] = firstlast["String"].str.rsplit(" ", expand=True)[0] In [4]: firstlast Out[4]: String First_Name Last_Name 0 John Smith John John 1 Jane Cook Jane Jane
The SAS UPCASE LOWCASE and PROPCASE functions change the case of the argument.
data firstlast; input String $60.; string_up = UPCASE(string); string_low = LOWCASE(string); string_prop = PROPCASE(string); datalines2; John Smith; Jane Cook; ;;; run;
The equivalent pandas methods are Series.str.upper(), Series.str.lower(), and Series.str.title().
Series.str.upper()
Series.str.lower()
Series.str.title()
In [1]: firstlast = pd.DataFrame({"string": ["John Smith", "Jane Cook"]}) In [2]: firstlast["upper"] = firstlast["string"].str.upper() In [3]: firstlast["lower"] = firstlast["string"].str.lower() In [4]: firstlast["title"] = firstlast["string"].str.title() In [5]: firstlast Out[5]: string upper lower title 0 John Smith JOHN SMITH john smith John Smith 1 Jane Cook JANE COOK jane cook Jane Cook
The following tables will be used in the merge examples:
In [1]: df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)}) In [2]: df1 Out[2]: key value 0 A 0.469112 1 B -0.282863 2 C -1.509059 3 D -1.135632 In [3]: df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)}) In [4]: df2 Out[4]: key value 0 B 1.212112 1 D -0.173215 2 D 0.119209 3 E -1.044236
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.
in=
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. The data does not have to be sorted ahead of time, and different join types are accomplished via the how keyword.
merge()
how
In [1]: inner_join = df1.merge(df2, on=["key"], how="inner") In [2]: inner_join Out[2]: key value_x value_y 0 B -0.282863 1.212112 1 D -1.135632 -0.173215 2 D -1.135632 0.119209 In [3]: left_join = df1.merge(df2, on=["key"], how="left") In [4]: left_join Out[4]: key value_x value_y 0 A 0.469112 NaN 1 B -0.282863 1.212112 2 C -1.509059 NaN 3 D -1.135632 -0.173215 4 D -1.135632 0.119209 In [5]: right_join = df1.merge(df2, on=["key"], how="right") In [6]: right_join Out[6]: key value_x value_y 0 B -0.282863 1.212112 1 D -1.135632 -0.173215 2 D -1.135632 0.119209 3 E NaN -1.044236 In [7]: outer_join = df1.merge(df2, on=["key"], how="outer") In [8]: outer_join Out[8]: key value_x value_y 0 A 0.469112 NaN 1 B -0.282863 1.212112 2 C -1.509059 NaN 3 D -1.135632 -0.173215 4 D -1.135632 0.119209 5 E NaN -1.044236
Both pandas and SAS have a representation for missing data.
pandas represents missing data with 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 [1]: outer_join Out[1]: key value_x value_y 0 A 0.469112 NaN 1 B -0.282863 1.212112 2 C -1.509059 NaN 3 D -1.135632 -0.173215 4 D -1.135632 0.119209 5 E NaN -1.044236 In [2]: outer_join["value_x"] + outer_join["value_y"] Out[2]: 0 NaN 1 0.929249 2 NaN 3 -1.308847 4 -1.016424 5 NaN dtype: float64 In [3]: outer_join["value_x"].sum() Out[3]: -3.5940742896293765
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;
In pandas, Series.isna() and Series.notna() can be used to filter the rows.
Series.isna()
Series.notna()
In [1]: outer_join[outer_join["value_x"].isna()] Out[1]: key value_x value_y 5 E NaN -1.044236 In [2]: outer_join[outer_join["value_x"].notna()] Out[2]: key value_x value_y 0 A 0.469112 NaN 1 B -0.282863 1.212112 2 C -1.509059 NaN 3 D -1.135632 -0.173215 4 D -1.135632 0.119209
pandas provides a variety of methods to work with missing data. Here are some examples:
In [3]: outer_join.dropna() Out[3]: key value_x value_y 1 B -0.282863 1.212112 3 D -1.135632 -0.173215 4 D -1.135632 0.119209
In [4]: outer_join.fillna(method="ffill") Out[4]: key value_x value_y 0 A 0.469112 NaN 1 B -0.282863 1.212112 2 C -1.509059 1.212112 3 D -1.135632 -0.173215 4 D -1.135632 0.119209 5 E -1.135632 -1.044236
Using the mean:
In [1]: outer_join["value_x"].fillna(outer_join["value_x"].mean()) Out[1]: 0 0.469112 1 -0.282863 2 -1.509059 3 -1.135632 4 -1.135632 5 -0.718815 Name: value_x, dtype: float64
SAS’s PROC SUMMARY can be used to group by one or more key variables and compute aggregations on numeric columns.
PROC SUMMARY
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 [1]: tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum() In [2]: tips_summed Out[2]: 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
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 provides a Transformation mechanism that allows these type of operations to be succinctly expressed in one operation.
In [1]: gb = tips.groupby("smoker")["total_bill"] In [2]: tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean") In [3]: tips Out[3]: 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 .. ... ... ... ... ... ... ... ... 182 43.35 3.50 Male Yes Sun Dinner 3 24.593656 156 46.17 5.00 Male No Sun Dinner 6 28.981722 59 46.27 6.73 Male No Sat Dinner 4 29.081722 212 46.33 9.00 Male No Sat Dinner 4 29.141722 170 48.81 10.00 Male Yes Sat Dinner 3 30.053656 [244 rows x 8 columns]
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 [4]: tips.groupby(["sex", "smoker"]).first() Out[4]: 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
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
pandas provides a read_sas() method that can read SAS data saved in the XPORT or SAS7BDAT binary format.
read_sas()
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") df = pd.read_sas("binary-file.sas7bdat")
You can also specify the file format directly. By default, pandas will try to infer the file format based on its extension.
df = pd.read_sas("transport-file.xpt", format="xport") df = pd.read_sas("binary-file.sas7bdat", format="sas7bdat")
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