For potential users coming from Stata this page is meant to demonstrate how different Stata 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. This means that we can refer to the libraries as pd and np, respectively, for the rest of the document.
pd
np
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 Stata would be:
DataFrame
df.head()
list in 1/5
pandas
Stata
data set
column
variable
row
observation
groupby
bysort
NaN
.
Series
A DataFrame in pandas is analogous to a Stata 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 in Stata can also be accomplished in pandas.
A Series is the data structure that represents one column of a DataFrame. Stata doesn’t have a separate data structure for a single column, but in general, working with a Series is analogous to referencing a column of a data set in Stata.
Index
Every DataFrame and Series has an Index – labels on the rows of the data. Stata does not have an exactly analogous concept. In Stata, a data set’s rows are essentially unlabeled, other than an implicit integer index that can be accessed with _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
A Stata data set can be built from specified values by placing the data after an input statement and specifying the column names.
input
input x y 1 2 3 4 5 6 end
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
Like Stata, pandas provides utilities for reading in data from many formats. The tips data set, found within the pandas tests (csv) will be used in many of the following examples.
tips
Stata provides import delimited to read csv data into a data set in memory. If the tips.csv file is in the current working directory, we can import it as follows.
import delimited
tips.csv
import delimited tips.csv
The pandas method is read_csv(), which works similarly. Additionally, it will automatically download the data set if presented with a url.
read_csv()
In [5]: url = ('https://raw.github.com/pandas-dev' ...: '/pandas/master/pandas/tests/io/data/csv/tips.csv') ...: In [6]: tips = pd.read_csv(url) --------------------------------------------------------------------------- ConnectionRefusedError Traceback (most recent call last) /opt/anaconda3/lib/python3.7/urllib/request.py in do_open(self, http_class, req, **http_conn_args) 1318 h.request(req.get_method(), req.selector, req.data, headers, -> 1319 encode_chunked=req.has_header('Transfer-encoding')) 1320 except OSError as err: # timeout error /opt/anaconda3/lib/python3.7/http/client.py in request(self, method, url, body, headers, encode_chunked) 1251 """Send a complete request to the server.""" -> 1252 self._send_request(method, url, body, headers, encode_chunked) 1253 /opt/anaconda3/lib/python3.7/http/client.py in _send_request(self, method, url, body, headers, encode_chunked) 1297 body = _encode(body, 'body') -> 1298 self.endheaders(body, encode_chunked=encode_chunked) 1299 /opt/anaconda3/lib/python3.7/http/client.py in endheaders(self, message_body, encode_chunked) 1246 raise CannotSendHeader() -> 1247 self._send_output(message_body, encode_chunked=encode_chunked) 1248 /opt/anaconda3/lib/python3.7/http/client.py in _send_output(self, message_body, encode_chunked) 1025 del self._buffer[:] -> 1026 self.send(msg) 1027 /opt/anaconda3/lib/python3.7/http/client.py in send(self, data) 965 if self.auto_open: --> 966 self.connect() 967 else: /opt/anaconda3/lib/python3.7/http/client.py in connect(self) 1413 -> 1414 super().connect() 1415 /opt/anaconda3/lib/python3.7/http/client.py in connect(self) 937 self.sock = self._create_connection( --> 938 (self.host,self.port), self.timeout, self.source_address) 939 self.sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1) /opt/anaconda3/lib/python3.7/socket.py in create_connection(address, timeout, source_address) 727 try: --> 728 raise err 729 finally: /opt/anaconda3/lib/python3.7/socket.py in create_connection(address, timeout, source_address) 715 sock.bind(source_address) --> 716 sock.connect(sa) 717 # 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-6-8ab2297b7141> in <module> ----> 1 tips = pd.read_csv(url) ~/build/pandas/pandas/io/parsers.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, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision) 684 ) 685 --> 686 return _read(filepath_or_buffer, kwds) 687 688 ~/build/pandas/pandas/io/parsers.py in _read(filepath_or_buffer, kwds) 433 # See https://github.com/python/mypy/issues/1297 434 fp_or_buf, _, compression, should_close = get_filepath_or_buffer( --> 435 filepath_or_buffer, encoding, compression 436 ) 437 kwds["compression"] = compression ~/build/pandas/pandas/io/common.py in get_filepath_or_buffer(filepath_or_buffer, encoding, compression, mode, storage_options) 181 if isinstance(filepath_or_buffer, str) and is_url(filepath_or_buffer): 182 # TODO: fsspec can also handle HTTP via requests, but leaving this unchanged --> 183 req = urlopen(filepath_or_buffer) 184 content_encoding = req.headers.get("Content-Encoding", None) 185 if content_encoding == "gzip": ~/build/pandas/pandas/io/common.py in urlopen(*args, **kwargs) 135 import urllib.request 136 --> 137 return urllib.request.urlopen(*args, **kwargs) 138 139 /opt/anaconda3/lib/python3.7/urllib/request.py in urlopen(url, data, timeout, cafile, capath, cadefault, context) 220 else: 221 opener = _opener --> 222 return opener.open(url, data, timeout) 223 224 def install_opener(opener): /opt/anaconda3/lib/python3.7/urllib/request.py in open(self, fullurl, data, timeout) 529 for processor in self.process_response.get(protocol, []): 530 meth = getattr(processor, meth_name) --> 531 response = meth(req, response) 532 533 return response /opt/anaconda3/lib/python3.7/urllib/request.py in http_response(self, request, response) 639 if not (200 <= code < 300): 640 response = self.parent.error( --> 641 'http', request, response, code, msg, hdrs) 642 643 return response /opt/anaconda3/lib/python3.7/urllib/request.py in error(self, proto, *args) 561 http_err = 0 562 args = (dict, proto, meth_name) + args --> 563 result = self._call_chain(*args) 564 if result: 565 return result /opt/anaconda3/lib/python3.7/urllib/request.py in _call_chain(self, chain, kind, meth_name, *args) 501 for handler in handlers: 502 func = getattr(handler, meth_name) --> 503 result = func(*args) 504 if result is not None: 505 return result /opt/anaconda3/lib/python3.7/urllib/request.py in http_error_302(self, req, fp, code, msg, headers) 753 fp.close() 754 --> 755 return self.parent.open(new, timeout=req.timeout) 756 757 http_error_301 = http_error_303 = http_error_307 = http_error_302 /opt/anaconda3/lib/python3.7/urllib/request.py in open(self, fullurl, data, timeout) 523 req = meth(req) 524 --> 525 response = self._open(req, data) 526 527 # post-process response /opt/anaconda3/lib/python3.7/urllib/request.py in _open(self, req, data) 541 protocol = req.type 542 result = self._call_chain(self.handle_open, protocol, protocol + --> 543 '_open', req) 544 if result: 545 return result /opt/anaconda3/lib/python3.7/urllib/request.py in _call_chain(self, chain, kind, meth_name, *args) 501 for handler in handlers: 502 func = getattr(handler, meth_name) --> 503 result = func(*args) 504 if result is not None: 505 return result /opt/anaconda3/lib/python3.7/urllib/request.py in https_open(self, req) 1360 def https_open(self, req): 1361 return self.do_open(http.client.HTTPSConnection, req, -> 1362 context=self._context, check_hostname=self._check_hostname) 1363 1364 https_request = AbstractHTTPHandler.do_request_ /opt/anaconda3/lib/python3.7/urllib/request.py in do_open(self, http_class, req, **http_conn_args) 1319 encode_chunked=req.has_header('Transfer-encoding')) 1320 except OSError as err: # timeout error -> 1321 raise URLError(err) 1322 r = h.getresponse() 1323 except: URLError: <urlopen error [Errno 111] Connection refused> In [7]: tips.head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-7-53a0cf752a4f> in <module> ----> 1 tips.head() NameError: name 'tips' is not defined
Like import delimited, read_csv() can take a number of parameters to specify how the data should be parsed. For example, if the data were instead tab delimited, did not have column names, and existed in the current working directory, 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)
Pandas can also read Stata data sets in .dta format with the read_stata() function.
.dta
read_stata()
df = pd.read_stata('data.dta')
In addition to text/csv and Stata files, pandas supports a variety of other data formats such as Excel, SAS, HDF5, Parquet, and SQL databases. These are all read via a pd.read_* function. See the IO documentation for more details.
pd.read_*
The inverse of import delimited in Stata is export delimited
export delimited
export delimited tips2.csv
Similarly in pandas, the opposite of read_csv is DataFrame.to_csv().
read_csv
DataFrame.to_csv()
tips.to_csv('tips2.csv')
Pandas can also export to Stata file format with the DataFrame.to_stata() method.
DataFrame.to_stata()
tips.to_stata('tips2.dta')
In Stata, arbitrary math expressions can be used with the generate and replace commands on new or existing columns. The drop command drops the column from the data set.
generate
replace
drop
replace total_bill = total_bill - 2 generate new_bill = total_bill / 2 drop new_bill
pandas provides similar 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 [8]: tips['total_bill'] = tips['total_bill'] - 2 --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-8-c2b722dfbb7a> in <module> ----> 1 tips['total_bill'] = tips['total_bill'] - 2 NameError: name 'tips' is not defined In [9]: tips['new_bill'] = tips['total_bill'] / 2 --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-9-9c39e9dcd652> in <module> ----> 1 tips['new_bill'] = tips['total_bill'] / 2 NameError: name 'tips' is not defined In [10]: tips.head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-10-53a0cf752a4f> in <module> ----> 1 tips.head() NameError: name 'tips' is not defined In [11]: tips = tips.drop('new_bill', axis=1) --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-11-08e1bfbf8723> in <module> ----> 1 tips = tips.drop('new_bill', axis=1) NameError: name 'tips' is not defined
Filtering in Stata is done with an if clause on one or more columns.
if
list if total_bill > 10
DataFrames can be filtered in multiple ways; the most intuitive of which is using boolean indexing.
In [12]: tips[tips['total_bill'] > 10].head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-12-0d501c078554> in <module> ----> 1 tips[tips['total_bill'] > 10].head() NameError: name 'tips' is not defined
In Stata, an if clause can also be used to create new columns.
generate bucket = "low" if total_bill < 10 replace bucket = "high" if total_bill >= 10
The same operation in pandas can be accomplished using the where method from numpy.
where
numpy
In [13]: tips['bucket'] = np.where(tips['total_bill'] < 10, 'low', 'high') --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-13-dc36aec0e1e5> in <module> ----> 1 tips['bucket'] = np.where(tips['total_bill'] < 10, 'low', 'high') NameError: name 'tips' is not defined In [14]: tips.head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-14-53a0cf752a4f> in <module> ----> 1 tips.head() NameError: name 'tips' is not defined
Stata provides a variety of functions to do operations on date/datetime columns.
generate date1 = mdy(1, 15, 2013) generate date2 = date("Feb152015", "MDY") generate date1_year = year(date1) generate date2_month = month(date2) * shift date to beginning of next month generate date1_next = mdy(month(date1) + 1, 1, year(date1)) if month(date1) != 12 replace date1_next = mdy(1, 1, year(date1) + 1) if month(date1) == 12 generate months_between = mofd(date2) - mofd(date1) list date1 date2 date1_year date2_month date1_next months_between
The equivalent pandas operations are shown below. In addition to these functions, pandas supports other Time Series features not available in Stata (such as time zone handling and custom offsets) – see the timeseries documentation for more details.
In [15]: tips['date1'] = pd.Timestamp('2013-01-15') --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-15-4218712dcdec> in <module> ----> 1 tips['date1'] = pd.Timestamp('2013-01-15') NameError: name 'tips' is not defined In [16]: tips['date2'] = pd.Timestamp('2015-02-15') --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-16-c2ca0884c4e4> in <module> ----> 1 tips['date2'] = pd.Timestamp('2015-02-15') NameError: name 'tips' is not defined In [17]: tips['date1_year'] = tips['date1'].dt.year --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-17-ce0e7e4dded0> in <module> ----> 1 tips['date1_year'] = tips['date1'].dt.year NameError: name 'tips' is not defined In [18]: tips['date2_month'] = tips['date2'].dt.month --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-18-45457f2388b5> in <module> ----> 1 tips['date2_month'] = tips['date2'].dt.month NameError: name 'tips' is not defined In [19]: tips['date1_next'] = tips['date1'] + pd.offsets.MonthBegin() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-19-a8fce403598f> in <module> ----> 1 tips['date1_next'] = tips['date1'] + pd.offsets.MonthBegin() NameError: name 'tips' is not defined In [20]: tips['months_between'] = (tips['date2'].dt.to_period('M') ....: - tips['date1'].dt.to_period('M')) ....: --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-20-7477b06e2c06> in <module> ----> 1 tips['months_between'] = (tips['date2'].dt.to_period('M') 2 - tips['date1'].dt.to_period('M')) NameError: name 'tips' is not defined In [21]: tips[['date1', 'date2', 'date1_year', 'date2_month', 'date1_next', ....: 'months_between']].head() ....: --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-21-2c21c770fc36> in <module> ----> 1 tips[['date1', 'date2', 'date1_year', 'date2_month', 'date1_next', 2 'months_between']].head() NameError: name 'tips' is not defined
Stata provides keywords to select, drop, and rename columns.
keep sex total_bill tip drop sex rename total_bill total_bill_2
The same operations are expressed in pandas below. Note that in contrast to Stata, these operations do not happen in place. To make these changes persist, assign the operation back to a variable.
# keep In [22]: tips[['sex', 'total_bill', 'tip']].head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-22-3fd2a7fe5ed5> in <module> ----> 1 tips[['sex', 'total_bill', 'tip']].head() NameError: name 'tips' is not defined # drop In [23]: tips.drop('sex', axis=1).head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-23-0aa2dc1f187e> in <module> ----> 1 tips.drop('sex', axis=1).head() NameError: name 'tips' is not defined # rename In [24]: tips.rename(columns={'total_bill': 'total_bill_2'}).head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-24-bea02207d15a> in <module> ----> 1 tips.rename(columns={'total_bill': 'total_bill_2'}).head() NameError: name 'tips' is not defined
Sorting in Stata is accomplished via sort
sort
sort sex total_bill
pandas objects have a DataFrame.sort_values() method, which takes a list of columns to sort by.
DataFrame.sort_values()
In [25]: tips = tips.sort_values(['sex', 'total_bill']) --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-25-5419d1fabdcc> in <module> ----> 1 tips = tips.sort_values(['sex', 'total_bill']) NameError: name 'tips' is not defined In [26]: tips.head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-26-53a0cf752a4f> in <module> ----> 1 tips.head() NameError: name 'tips' is not defined
Stata determines the length of a character string with the strlen() and ustrlen() functions for ASCII and Unicode strings, respectively.
strlen()
ustrlen()
generate strlen_time = strlen(time) generate ustrlen_time = ustrlen(time)
Python determines the length of a character string with the len function. In Python 3, all strings are Unicode strings. len includes trailing blanks. Use len and rstrip to exclude trailing blanks.
len
rstrip
In [27]: tips['time'].str.len().head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-27-d7f5e5528e52> in <module> ----> 1 tips['time'].str.len().head() NameError: name 'tips' is not defined In [28]: tips['time'].str.rstrip().str.len().head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-28-61f535a15695> in <module> ----> 1 tips['time'].str.rstrip().str.len().head() NameError: name 'tips' is not defined
Stata determines the position of a character in a string with the strpos() function. This takes the string defined by the first argument and searches for the first position of the substring you supply as the second argument.
strpos()
generate str_position = strpos(sex, "ale")
Python determines the position of a character in a string with the find() function. find searches for the first position of the substring. If the substring is found, the function returns its position. Keep in mind that Python indexes are zero-based and the function will return -1 if it fails to find the substring.
find()
find
In [29]: tips['sex'].str.find("ale").head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-29-059d9d8e0006> in <module> ----> 1 tips['sex'].str.find("ale").head() NameError: name 'tips' is not defined
Stata extracts a substring from a string based on its position with the substr() function.
substr()
generate short_sex = substr(sex, 1, 1)
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 [30]: tips['sex'].str[0:1].head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-30-560ab9d35724> in <module> ----> 1 tips['sex'].str[0:1].head() NameError: name 'tips' is not defined
The Stata word() 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.
word()
clear input str20 string "John Smith" "Jane Cook" end generate first_name = word(name, 1) generate last_name = word(name, -1)
Python extracts a substring from a string based on its text by using regular expressions. There are much more powerful approaches, but this just shows a simple approach.
In [31]: firstlast = pd.DataFrame({'string': ['John Smith', 'Jane Cook']}) In [32]: firstlast['First_Name'] = firstlast['string'].str.split(" ", expand=True)[0] In [33]: firstlast['Last_Name'] = firstlast['string'].str.rsplit(" ", expand=True)[0] In [34]: firstlast Out[34]: string First_Name Last_Name 0 John Smith John John 1 Jane Cook Jane Jane
The Stata strupper(), strlower(), strproper(), ustrupper(), ustrlower(), and ustrtitle() functions change the case of ASCII and Unicode strings, respectively.
strupper()
strlower()
strproper()
ustrupper()
ustrlower()
ustrtitle()
clear input str20 string "John Smith" "Jane Cook" end generate upper = strupper(string) generate lower = strlower(string) generate title = strproper(string) list
The equivalent Python functions are upper, lower, and title.
upper
lower
title
In [35]: firstlast = pd.DataFrame({'string': ['John Smith', 'Jane Cook']}) In [36]: firstlast['upper'] = firstlast['string'].str.upper() In [37]: firstlast['lower'] = firstlast['string'].str.lower() In [38]: firstlast['title'] = firstlast['string'].str.title() In [39]: firstlast Out[39]: 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 [40]: df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], ....: 'value': np.random.randn(4)}) ....: In [41]: df1 Out[41]: key value 0 A 0.469112 1 B -0.282863 2 C -1.509059 3 D -1.135632 In [42]: df2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'], ....: 'value': np.random.randn(4)}) ....: In [43]: df2 Out[43]: key value 0 B 1.212112 1 D -0.173215 2 D 0.119209 3 E -1.044236
In Stata, to perform a merge, one data set must be in memory and the other must be referenced as a file name on disk. In contrast, Python must have both DataFrames already in memory.
DataFrames
By default, Stata performs an outer join, where all observations from both data sets are left in memory after the merge. One can keep only observations from the initial data set, the merged data set, or the intersection of the two by using the values created in the _merge variable.
_merge
* First create df2 and save to disk clear input str1 key B D D E end generate value = rnormal() save df2.dta * Now create df1 in memory clear input str1 key A B C D end generate value = rnormal() preserve * Left join merge 1:n key using df2.dta keep if _merge == 1 * Right join restore, preserve merge 1:n key using df2.dta keep if _merge == 2 * Inner join restore, preserve merge 1:n key using df2.dta keep if _merge == 3 * Outer join restore merge 1:n key using df2.dta
pandas DataFrames have a DataFrame.merge() method, which provides similar functionality. Note that different join types are accomplished via the how keyword.
DataFrame.merge()
how
In [44]: inner_join = df1.merge(df2, on=['key'], how='inner') In [45]: inner_join Out[45]: key value_x value_y 0 B -0.282863 1.212112 1 D -1.135632 -0.173215 2 D -1.135632 0.119209 In [46]: left_join = df1.merge(df2, on=['key'], how='left') In [47]: left_join Out[47]: 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 [48]: right_join = df1.merge(df2, on=['key'], how='right') In [49]: right_join Out[49]: 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 [50]: outer_join = df1.merge(df2, on=['key'], how='outer') In [51]: outer_join Out[51]: 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
Like Stata, pandas has a representation for missing data – 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 [52]: outer_join Out[52]: 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 [53]: outer_join['value_x'] + outer_join['value_y'] Out[53]: 0 NaN 1 0.929249 2 NaN 3 -1.308847 4 -1.016424 5 NaN dtype: float64 In [54]: outer_join['value_x'].sum() Out[54]: -3.5940742896293765
One difference is that missing data cannot be compared to its sentinel value. For example, in Stata you could do this to filter missing values.
* Keep missing values list if value_x == . * Keep non-missing values list if value_x != .
This doesn’t work in pandas. Instead, the pd.isna() or pd.notna() functions should be used for comparisons.
pd.isna()
pd.notna()
In [55]: outer_join[pd.isna(outer_join['value_x'])] Out[55]: key value_x value_y 5 E NaN -1.044236 In [56]: outer_join[pd.notna(outer_join['value_x'])] Out[56]: 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 also provides a variety of methods to work with missing data – some of which would be challenging to express in Stata. 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.
# Drop rows with any missing value In [57]: outer_join.dropna() Out[57]: key value_x value_y 1 B -0.282863 1.212112 3 D -1.135632 -0.173215 4 D -1.135632 0.119209 # Fill forwards In [58]: outer_join.fillna(method='ffill') Out[58]: 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 # Impute missing values with the mean In [59]: outer_join['value_x'].fillna(outer_join['value_x'].mean()) Out[59]: 0 0.469112 1 -0.282863 2 -1.509059 3 -1.135632 4 -1.135632 5 -0.718815 Name: value_x, dtype: float64
Stata’s collapse can be used to group by one or more key variables and compute aggregations on numeric columns.
collapse
collapse (sum) total_bill tip, by(sex smoker)
pandas provides a flexible groupby mechanism that allows similar aggregations. See the groupby documentation for more details and examples.
In [60]: tips_summed = tips.groupby(['sex', 'smoker'])[['total_bill', 'tip']].sum() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-60-1ec4395848ad> in <module> ----> 1 tips_summed = tips.groupby(['sex', 'smoker'])[['total_bill', 'tip']].sum() NameError: name 'tips' is not defined In [61]: tips_summed.head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-61-3d0a2a6150c9> in <module> ----> 1 tips_summed.head() NameError: name 'tips_summed' is not defined
In Stata, if the group aggregations need to be used with the original data set, one would usually use bysort with egen(). For example, to subtract the mean for each observation by smoker group.
egen()
bysort sex smoker: egen group_bill = mean(total_bill) generate adj_total_bill = total_bill - group_bill
pandas groupby provides a transform mechanism that allows these type of operations to be succinctly expressed in one operation.
transform
In [62]: gb = tips.groupby('smoker')['total_bill'] --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-62-5366e81198e8> in <module> ----> 1 gb = tips.groupby('smoker')['total_bill'] NameError: name 'tips' is not defined In [63]: tips['adj_total_bill'] = tips['total_bill'] - gb.transform('mean') --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-63-4af8aafc38e1> in <module> ----> 1 tips['adj_total_bill'] = tips['total_bill'] - gb.transform('mean') NameError: name 'tips' is not defined In [64]: tips.head() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-64-53a0cf752a4f> in <module> ----> 1 tips.head() NameError: name 'tips' is not defined
In addition to aggregation, pandas groupby can be used to replicate most other bysort processing from Stata. For example, the following example lists the first observation in the current sort order by sex/smoker group.
bysort sex smoker: list if _n == 1
In pandas this would be written as:
In [65]: tips.groupby(['sex', 'smoker']).first() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-65-255e03c34a49> in <module> ----> 1 tips.groupby(['sex', 'smoker']).first() NameError: name 'tips' is not defined
Pandas and Stata both operate exclusively in memory. This means that the size of data able to be loaded in pandas is limited by your machine’s memory. If out of core processing is needed, one possibility is the dask.dataframe library, which provides a subset of pandas functionality for an on-disk DataFrame.