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
Data structures¶
General terminology translation¶
pandas |
SAS |
---|---|
|
data set |
column |
variable |
row |
observation |
groupby |
BY-group |
|
|
DataFrame
¶
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.
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_
).
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.
Copies vs. in place operations¶
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:
df.sort_values("col1", inplace=True)
Its use is discouraged. More information.
Data input / output¶
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 [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
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 [3]: url = (
...: "https://raw.github.com/pandas-dev/"
...: "pandas/master/pandas/tests/io/data/csv/tips.csv"
...: )
...:
In [4]: tips = pd.read_csv(url)
---------------------------------------------------------------------------
ConnectionRefusedError Traceback (most recent call last)
/usr/lib/python3.9/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
1345 try:
-> 1346 h.request(req.get_method(), req.selector, req.data, headers,
1347 encode_chunked=req.has_header('Transfer-encoding'))
/usr/lib/python3.9/http/client.py in request(self, method, url, body, headers, encode_chunked)
1284 """Send a complete request to the server."""
-> 1285 self._send_request(method, url, body, headers, encode_chunked)
1286
/usr/lib/python3.9/http/client.py in _send_request(self, method, url, body, headers, encode_chunked)
1330 body = _encode(body, 'body')
-> 1331 self.endheaders(body, encode_chunked=encode_chunked)
1332
/usr/lib/python3.9/http/client.py in endheaders(self, message_body, encode_chunked)
1279 raise CannotSendHeader()
-> 1280 self._send_output(message_body, encode_chunked=encode_chunked)
1281
/usr/lib/python3.9/http/client.py in _send_output(self, message_body, encode_chunked)
1039 del self._buffer[:]
-> 1040 self.send(msg)
1041
/usr/lib/python3.9/http/client.py in send(self, data)
979 if self.auto_open:
--> 980 self.connect()
981 else:
/usr/lib/python3.9/http/client.py in connect(self)
1446
-> 1447 super().connect()
1448
/usr/lib/python3.9/http/client.py in connect(self)
945 """Connect to the host and port specified in __init__."""
--> 946 self.sock = self._create_connection(
947 (self.host,self.port), self.timeout, self.source_address)
/usr/lib/python3.9/socket.py in create_connection(address, timeout, source_address)
843 try:
--> 844 raise err
845 finally:
/usr/lib/python3.9/socket.py in create_connection(address, timeout, source_address)
831 sock.bind(source_address)
--> 832 sock.connect(sa)
833 # Break explicitly a reference cycle
ConnectionRefusedError: [Errno 111] Connection refused
During handling of the above exception, another exception occurred:
URLError Traceback (most recent call last)
<ipython-input-4-8ab2297b7141> in <module>
----> 1 tips = pd.read_csv(url)
/usr/lib/python3/dist-packages/pandas/util/_decorators.py in wrapper(*args, **kwargs)
309 stacklevel=stacklevel,
310 )
--> 311 return func(*args, **kwargs)
312
313 return wrapper
/usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, error_bad_lines, warn_bad_lines, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)
584 kwds.update(kwds_defaults)
585
--> 586 return _read(filepath_or_buffer, kwds)
587
588
/usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in _read(filepath_or_buffer, kwds)
480
481 # Create the parser.
--> 482 parser = TextFileReader(filepath_or_buffer, **kwds)
483
484 if chunksize or iterator:
/usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in __init__(self, f, engine, **kwds)
809 self.options["has_index_names"] = kwds["has_index_names"]
810
--> 811 self._engine = self._make_engine(self.engine)
812
813 def close(self):
/usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in _make_engine(self, engine)
1038 )
1039 # error: Too many arguments for "ParserBase"
-> 1040 return mapping[engine](self.f, **self.options) # type: ignore[call-arg]
1041
1042 def _failover_to_python(self):
/usr/lib/python3/dist-packages/pandas/io/parsers/c_parser_wrapper.py in __init__(self, src, **kwds)
49
50 # open handles
---> 51 self._open_handles(src, kwds)
52 assert self.handles is not None
53
/usr/lib/python3/dist-packages/pandas/io/parsers/base_parser.py in _open_handles(self, src, kwds)
220 Let the readers open IOHandles after they are done with their potential raises.
221 """
--> 222 self.handles = get_handle(
223 src,
224 "r",
/usr/lib/python3/dist-packages/pandas/io/common.py in get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)
607
608 # open URLs
--> 609 ioargs = _get_filepath_or_buffer(
610 path_or_buf,
611 encoding=encoding,
/usr/lib/python3/dist-packages/pandas/io/common.py in _get_filepath_or_buffer(filepath_or_buffer, encoding, compression, mode, storage_options)
310 # assuming storage_options is to be interpreted as headers
311 req_info = urllib.request.Request(filepath_or_buffer, headers=storage_options)
--> 312 with urlopen(req_info) as req:
313 content_encoding = req.headers.get("Content-Encoding", None)
314 if content_encoding == "gzip":
/usr/lib/python3/dist-packages/pandas/io/common.py in urlopen(*args, **kwargs)
210 import urllib.request
211
--> 212 return urllib.request.urlopen(*args, **kwargs)
213
214
/usr/lib/python3.9/urllib/request.py in urlopen(url, data, timeout, cafile, capath, cadefault, context)
212 else:
213 opener = _opener
--> 214 return opener.open(url, data, timeout)
215
216 def install_opener(opener):
/usr/lib/python3.9/urllib/request.py in open(self, fullurl, data, timeout)
515
516 sys.audit('urllib.Request', req.full_url, req.data, req.headers, req.get_method())
--> 517 response = self._open(req, data)
518
519 # post-process response
/usr/lib/python3.9/urllib/request.py in _open(self, req, data)
532
533 protocol = req.type
--> 534 result = self._call_chain(self.handle_open, protocol, protocol +
535 '_open', req)
536 if result:
/usr/lib/python3.9/urllib/request.py in _call_chain(self, chain, kind, meth_name, *args)
492 for handler in handlers:
493 func = getattr(handler, meth_name)
--> 494 result = func(*args)
495 if result is not None:
496 return result
/usr/lib/python3.9/urllib/request.py in https_open(self, req)
1387
1388 def https_open(self, req):
-> 1389 return self.do_open(http.client.HTTPSConnection, req,
1390 context=self._context, check_hostname=self._check_hostname)
1391
/usr/lib/python3.9/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
1347 encode_chunked=req.has_header('Transfer-encoding'))
1348 except OSError as err: # timeout error
-> 1349 raise URLError(err)
1350 r = h.getresponse()
1351 except:
URLError: <urlopen error [Errno 111] Connection refused>
In [5]: tips
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-5-9034fb5f8272> in <module>
----> 1 tips
NameError: name 'tips' is not defined
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.
Limiting output¶
By default, pandas will truncate output of large DataFrame
s to show the first and last rows.
This can be overridden by changing the pandas options, or using
DataFrame.head()
or DataFrame.tail()
.
In [1]: tips.head(5)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-e2693e4d5ad6> in <module>
----> 1 tips.head(5)
NameError: name 'tips' is not defined
The equivalent in SAS would be:
proc print data=df(obs=5);
run;
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")
Data operations¶
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 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
.
In [1]: tips["total_bill"] = tips["total_bill"] - 2
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-30a6f4d646c7> in <module>
----> 1 tips["total_bill"] = tips["total_bill"] - 2
NameError: name 'tips' is not defined
In [2]: tips["new_bill"] = tips["total_bill"] / 2
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-7e2a96df4894> in <module>
----> 1 tips["new_bill"] = tips["total_bill"] / 2
NameError: name 'tips' is not defined
In [3]: tips
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-3-9034fb5f8272> in <module>
----> 1 tips
NameError: name 'tips' is not defined
In [4]: tips = tips.drop("new_bill", axis=1)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-4-2bfa6fa8e9bc> in <module>
----> 1 tips = tips.drop("new_bill", axis=1)
NameError: name 'tips' is not defined
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 [1]: tips[tips["total_bill"] > 10]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-4e5df2b556a0> in <module>
----> 1 tips[tips["total_bill"] > 10]
NameError: name 'tips' is not defined
The above statement is simply passing a Series
of True
/False
objects to the DataFrame,
returning all rows with True
.
In [1]: is_dinner = tips["time"] == "Dinner"
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-a38011ae4c7d> in <module>
----> 1 is_dinner = tips["time"] == "Dinner"
NameError: name 'tips' is not defined
In [2]: is_dinner
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-49a5e8deec4a> in <module>
----> 1 is_dinner
NameError: name 'is_dinner' is not defined
In [3]: is_dinner.value_counts()
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-3-900d99c3802f> in <module>
----> 1 is_dinner.value_counts()
NameError: name 'is_dinner' is not defined
In [4]: tips[is_dinner]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-4-ed12d6ad643e> in <module>
----> 1 tips[is_dinner]
NameError: name 'tips' is not defined
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 [1]: tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-a8d4661e6618> in <module>
----> 1 tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")
NameError: name 'tips' is not defined
In [2]: tips
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-9034fb5f8272> in <module>
----> 1 tips
NameError: name 'tips' is not defined
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 custom offsets) - see the timeseries documentation for more details.
In [1]: tips["date1"] = pd.Timestamp("2013-01-15")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-33d7f805e8ca> in <module>
----> 1 tips["date1"] = pd.Timestamp("2013-01-15")
NameError: name 'tips' is not defined
In [2]: tips["date2"] = pd.Timestamp("2015-02-15")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-40a3130f91b4> in <module>
----> 1 tips["date2"] = pd.Timestamp("2015-02-15")
NameError: name 'tips' is not defined
In [3]: tips["date1_year"] = tips["date1"].dt.year
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-3-c9e20f269b76> in <module>
----> 1 tips["date1_year"] = tips["date1"].dt.year
NameError: name 'tips' is not defined
In [4]: tips["date2_month"] = tips["date2"].dt.month
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-4-ef1fc2805b92> in <module>
----> 1 tips["date2_month"] = tips["date2"].dt.month
NameError: name 'tips' is not defined
In [5]: tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-5-60c45d997a52> in <module>
----> 1 tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()
NameError: name 'tips' is not defined
In [6]: tips["months_between"] = tips["date2"].dt.to_period("M") - tips[
...: "date1"
...: ].dt.to_period("M")
...:
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-6-a4bc4ed24519> in <module>
----> 1 tips["months_between"] = tips["date2"].dt.to_period("M") - tips[
2 "date1"
3 ].dt.to_period("M")
NameError: name 'tips' is not defined
In [7]: tips[
...: ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
...: ]
...:
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-7-606cac654d39> in <module>
----> 1 tips[
2 ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
3 ]
NameError: name 'tips' is not defined
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 certain columns¶
In [1]: tips[["sex", "total_bill", "tip"]]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-88ea27a409ff> in <module>
----> 1 tips[["sex", "total_bill", "tip"]]
NameError: name 'tips' is not defined
Drop a column¶
In [2]: tips.drop("sex", axis=1)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-e6cea0c561ee> in <module>
----> 1 tips.drop("sex", axis=1)
NameError: name 'tips' is not defined
Rename a column¶
In [1]: tips.rename(columns={"total_bill": "total_bill_2"})
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-89e2303a1fc4> in <module>
----> 1 tips.rename(columns={"total_bill": "total_bill_2"})
NameError: name 'tips' is not defined
Sorting by values¶
Sorting in SAS is accomplished via 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.
In [1]: tips = tips.sort_values(["sex", "total_bill"])
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-9d59129f8932> in <module>
----> 1 tips = tips.sort_values(["sex", "total_bill"])
NameError: name 'tips' is not defined
In [2]: tips
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-9034fb5f8272> in <module>
----> 1 tips
NameError: name 'tips' is not defined
String processing¶
Finding length of string¶
SAS determines the length of a character string with the
LENGTHN
and LENGTHC
functions. LENGTHN
excludes trailing blanks and LENGTHC
includes trailing blanks.
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.
In [1]: tips["time"].str.len()
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-ef639d164546> in <module>
----> 1 tips["time"].str.len()
NameError: name 'tips' is not defined
In [2]: tips["time"].str.rstrip().str.len()
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-3ffb003cc2cc> in <module>
----> 1 tips["time"].str.rstrip().str.len()
NameError: name 'tips' is not defined
Finding position of substring¶
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.
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.
In [1]: tips["sex"].str.find("ale")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-ce45837e4fed> in <module>
----> 1 tips["sex"].str.find("ale")
NameError: name 'tips' is not defined
Extracting substring by position¶
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]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-e5ebda285fba> in <module>
----> 1 tips["sex"].str[0:1]
NameError: name 'tips' is not defined
Extracting nth word¶
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
Changing case¶
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()
.
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
Merging¶
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.
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.
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
Missing data¶
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.
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:
Drop rows with missing values¶
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
Forward fill from previous rows¶
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
Replace missing values with a specified value¶
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
GroupBy¶
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 [1]: tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-766ce4af4bd9> in <module>
----> 1 tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()
NameError: name 'tips' is not defined
In [2]: tips_summed
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-f0d5e7410005> in <module>
----> 1 tips_summed
NameError: name 'tips_summed' is not defined
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 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"]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-1-b48820aab6ea> in <module>
----> 1 gb = tips.groupby("smoker")["total_bill"]
NameError: name 'tips' is not defined
In [2]: tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-2-edcbd24271f3> in <module>
----> 1 tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")
NameError: name 'tips' is not defined
In [3]: tips
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-3-9034fb5f8272> in <module>
----> 1 tips
NameError: name 'tips' is not defined
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 [4]: tips.groupby(["sex", "smoker"]).first()
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-4-b8b50dd17fd6> in <module>
----> 1 tips.groupby(["sex", "smoker"]).first()
NameError: name 'tips' is not defined
Other considerations¶
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
Data interop¶
pandas provides a read_sas()
method that can read SAS data saved in
the XPORT or SAS7BDAT binary format.
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