Comparison with Stata¶

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:

In [1]: import pandas as pd

In [2]: import numpy as np

Data structures¶

General terminology translation¶

pandas	Stata
`DataFrame`	data set
column	variable
row	observation
groupby	bysort
`NaN`	`.`

`DataFrame`¶

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.

`Series`¶

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.

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 Stata data set can be built from specified values by placing the data after an input statement and specifying the column names.

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

Reading external data¶

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.

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

The pandas method is read_csv(), which works similarly. Additionally, it will automatically download the data set if presented with a url.

In [5]: url = (
   ...:     "https://raw.githubusercontent.com/pandas-dev"
   ...:     "/pandas/main/pandas/tests/io/data/csv/tips.csv"
   ...: )
   ...: 

In [6]: tips = pd.read_csv(url)
---------------------------------------------------------------------------
ConnectionRefusedError                    Traceback (most recent call last)
File /usr/lib/python3.11/urllib/request.py:1348, in AbstractHTTPHandler.do_open(self, http_class, req, **http_conn_args)
   1347 try:
-> 1348     h.request(req.get_method(), req.selector, req.data, headers,
   1349               encode_chunked=req.has_header('Transfer-encoding'))
   1350 except OSError as err: # timeout error

File /usr/lib/python3.11/http/client.py:1282, in HTTPConnection.request(self, method, url, body, headers, encode_chunked)
   1281 """Send a complete request to the server."""
-> 1282 self._send_request(method, url, body, headers, encode_chunked)

File /usr/lib/python3.11/http/client.py:1328, in HTTPConnection._send_request(self, method, url, body, headers, encode_chunked)
   1327     body = _encode(body, 'body')
-> 1328 self.endheaders(body, encode_chunked=encode_chunked)

File /usr/lib/python3.11/http/client.py:1277, in HTTPConnection.endheaders(self, message_body, encode_chunked)
   1276     raise CannotSendHeader()
-> 1277 self._send_output(message_body, encode_chunked=encode_chunked)

File /usr/lib/python3.11/http/client.py:1037, in HTTPConnection._send_output(self, message_body, encode_chunked)
   1036 del self._buffer[:]
-> 1037 self.send(msg)
   1039 if message_body is not None:
   1040 
   1041     # create a consistent interface to message_body

File /usr/lib/python3.11/http/client.py:975, in HTTPConnection.send(self, data)
    974 if self.auto_open:
--> 975     self.connect()
    976 else:

File /usr/lib/python3.11/http/client.py:1447, in HTTPSConnection.connect(self)
   1445 "Connect to a host on a given (SSL) port."
-> 1447 super().connect()
   1449 if self._tunnel_host:

File /usr/lib/python3.11/http/client.py:941, in HTTPConnection.connect(self)
    940 sys.audit("http.client.connect", self, self.host, self.port)
--> 941 self.sock = self._create_connection(
    942     (self.host,self.port), self.timeout, self.source_address)
    943 # Might fail in OSs that don't implement TCP_NODELAY

File /usr/lib/python3.11/socket.py:851, in create_connection(address, timeout, source_address, all_errors)
    850 if not all_errors:
--> 851     raise exceptions[0]
    852 raise ExceptionGroup("create_connection failed", exceptions)

File /usr/lib/python3.11/socket.py:836, in create_connection(address, timeout, source_address, all_errors)
    835     sock.bind(source_address)
--> 836 sock.connect(sa)
    837 # 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)
Cell In [6], line 1
----> 1 tips = pd.read_csv(url)

File /usr/lib/python3/dist-packages/pandas/util/_decorators.py:211, in deprecate_kwarg.<locals>._deprecate_kwarg.<locals>.wrapper(*args, **kwargs)
    209     else:
    210         kwargs[new_arg_name] = new_arg_value
--> 211 return func(*args, **kwargs)

File /usr/lib/python3/dist-packages/pandas/util/_decorators.py:331, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    325 if len(args) > num_allow_args:
    326     warnings.warn(
    327         msg.format(arguments=_format_argument_list(allow_args)),
    328         FutureWarning,
    329         stacklevel=find_stack_level(),
    330     )
--> 331 return func(*args, **kwargs)

File /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py:950, 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)
    935 kwds_defaults = _refine_defaults_read(
    936     dialect,
    937     delimiter,
   (...)
    946     defaults={"delimiter": ","},
    947 )
    948 kwds.update(kwds_defaults)
--> 950 return _read(filepath_or_buffer, kwds)

File /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py:605, in _read(filepath_or_buffer, kwds)
    602 _validate_names(kwds.get("names", None))
    604 # Create the parser.
--> 605 parser = TextFileReader(filepath_or_buffer, **kwds)
    607 if chunksize or iterator:
    608     return parser

File /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py:1442, in TextFileReader.__init__(self, f, engine, **kwds)
   1439     self.options["has_index_names"] = kwds["has_index_names"]
   1441 self.handles: IOHandles | None = None
-> 1442 self._engine = self._make_engine(f, self.engine)

File /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py:1735, in TextFileReader._make_engine(self, f, engine)
   1733     if "b" not in mode:
   1734         mode += "b"
-> 1735 self.handles = get_handle(
   1736     f,
   1737     mode,
   1738     encoding=self.options.get("encoding", None),
   1739     compression=self.options.get("compression", None),
   1740     memory_map=self.options.get("memory_map", False),
   1741     is_text=is_text,
   1742     errors=self.options.get("encoding_errors", "strict"),
   1743     storage_options=self.options.get("storage_options", None),
   1744 )
   1745 assert self.handles is not None
   1746 f = self.handles.handle

File /usr/lib/python3/dist-packages/pandas/io/common.py:713, in get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)
    710     codecs.lookup_error(errors)
    712 # open URLs
--> 713 ioargs = _get_filepath_or_buffer(
    714     path_or_buf,
    715     encoding=encoding,
    716     compression=compression,
    717     mode=mode,
    718     storage_options=storage_options,
    719 )
    721 handle = ioargs.filepath_or_buffer
    722 handles: list[BaseBuffer]

File /usr/lib/python3/dist-packages/pandas/io/common.py:363, in _get_filepath_or_buffer(filepath_or_buffer, encoding, compression, mode, storage_options)
    361 # assuming storage_options is to be interpreted as headers
    362 req_info = urllib.request.Request(filepath_or_buffer, headers=storage_options)
--> 363 with urlopen(req_info) as req:
    364     content_encoding = req.headers.get("Content-Encoding", None)
    365     if content_encoding == "gzip":
    366         # Override compression based on Content-Encoding header

File /usr/lib/python3/dist-packages/pandas/io/common.py:265, in urlopen(*args, **kwargs)
    259 """
    260 Lazy-import wrapper for stdlib urlopen, as that imports a big chunk of
    261 the stdlib.
    262 """
    263 import urllib.request
--> 265 return urllib.request.urlopen(*args, **kwargs)

File /usr/lib/python3.11/urllib/request.py:216, in urlopen(url, data, timeout, cafile, capath, cadefault, context)
    214 else:
    215     opener = _opener
--> 216 return opener.open(url, data, timeout)

File /usr/lib/python3.11/urllib/request.py:519, in OpenerDirector.open(self, fullurl, data, timeout)
    516     req = meth(req)
    518 sys.audit('urllib.Request', req.full_url, req.data, req.headers, req.get_method())
--> 519 response = self._open(req, data)
    521 # post-process response
    522 meth_name = protocol+"_response"

File /usr/lib/python3.11/urllib/request.py:536, in OpenerDirector._open(self, req, data)
    533     return result
    535 protocol = req.type
--> 536 result = self._call_chain(self.handle_open, protocol, protocol +
    537                           '_open', req)
    538 if result:
    539     return result

File /usr/lib/python3.11/urllib/request.py:496, in OpenerDirector._call_chain(self, chain, kind, meth_name, *args)
    494 for handler in handlers:
    495     func = getattr(handler, meth_name)
--> 496     result = func(*args)
    497     if result is not None:
    498         return result

File /usr/lib/python3.11/urllib/request.py:1391, in HTTPSHandler.https_open(self, req)
   1390 def https_open(self, req):
-> 1391     return self.do_open(http.client.HTTPSConnection, req,
   1392         context=self._context, check_hostname=self._check_hostname)

File /usr/lib/python3.11/urllib/request.py:1351, in AbstractHTTPHandler.do_open(self, http_class, req, **http_conn_args)
   1348         h.request(req.get_method(), req.selector, req.data, headers,
   1349                   encode_chunked=req.has_header('Transfer-encoding'))
   1350     except OSError as err: # timeout error
-> 1351         raise URLError(err)
   1352     r = h.getresponse()
   1353 except:

URLError: <urlopen error [Errno 111] Connection refused>

In [7]: tips
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [7], line 1
----> 1 tips

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.

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.

Limiting output¶

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().

In [8]: tips.head(5)
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [8], line 1
----> 1 tips.head(5)

NameError: name 'tips' is not defined

The equivalent in Stata would be:

list in 1/5

Exporting data¶

The inverse of import delimited in Stata is export delimited

export delimited tips2.csv

Similarly in pandas, the opposite of read_csv is DataFrame.to_csv().

tips.to_csv("tips2.csv")

pandas can also export to Stata file format with the DataFrame.to_stata() method.

tips.to_stata("tips2.dta")

Data operations¶

Operations on columns¶

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.

replace total_bill = total_bill - 2
generate new_bill = total_bill / 2
drop new_bill

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 [9]: tips["total_bill"] = tips["total_bill"] - 2
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [9], line 1
----> 1 tips["total_bill"] = tips["total_bill"] - 2

NameError: name 'tips' is not defined

In [10]: tips["new_bill"] = tips["total_bill"] / 2
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [10], line 1
----> 1 tips["new_bill"] = tips["total_bill"] / 2

NameError: name 'tips' is not defined

In [11]: tips
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [11], line 1
----> 1 tips

NameError: name 'tips' is not defined

In [12]: tips = tips.drop("new_bill", axis=1)
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [12], line 1
----> 1 tips = tips.drop("new_bill", axis=1)

NameError: name 'tips' is not defined

Filtering¶

Filtering in Stata is done with an if clause on one or more columns.

list if total_bill > 10

DataFrames can be filtered in multiple ways; the most intuitive of which is using boolean indexing.

In [13]: tips[tips["total_bill"] > 10]
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [13], line 1
----> 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 [14]: is_dinner = tips["time"] == "Dinner"
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [14], line 1
----> 1 is_dinner = tips["time"] == "Dinner"

NameError: name 'tips' is not defined

In [15]: is_dinner
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [15], line 1
----> 1 is_dinner

NameError: name 'is_dinner' is not defined

In [16]: is_dinner.value_counts()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [16], line 1
----> 1 is_dinner.value_counts()

NameError: name 'is_dinner' is not defined

In [17]: tips[is_dinner]
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [17], line 1
----> 1 tips[is_dinner]

NameError: name 'tips' is not defined

If/then logic¶

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.

In [18]: tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [18], line 1
----> 1 tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")

NameError: name 'tips' is not defined

In [19]: tips
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [19], line 1
----> 1 tips

NameError: name 'tips' is not defined

Date functionality¶

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 [20]: tips["date1"] = pd.Timestamp("2013-01-15")
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [20], line 1
----> 1 tips["date1"] = pd.Timestamp("2013-01-15")

NameError: name 'tips' is not defined

In [21]: tips["date2"] = pd.Timestamp("2015-02-15")
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [21], line 1
----> 1 tips["date2"] = pd.Timestamp("2015-02-15")

NameError: name 'tips' is not defined

In [22]: tips["date1_year"] = tips["date1"].dt.year
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [22], line 1
----> 1 tips["date1_year"] = tips["date1"].dt.year

NameError: name 'tips' is not defined

In [23]: tips["date2_month"] = tips["date2"].dt.month
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [23], line 1
----> 1 tips["date2_month"] = tips["date2"].dt.month

NameError: name 'tips' is not defined

In [24]: tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [24], line 1
----> 1 tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()

NameError: name 'tips' is not defined

In [25]: tips["months_between"] = tips["date2"].dt.to_period("M") - tips[
   ....:     "date1"
   ....: ].dt.to_period("M")
   ....: 
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [25], line 1
----> 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 [26]: tips[
   ....:     ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
   ....: ]
   ....: 
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [26], line 1
----> 1 tips[
      2     ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
      3 ]

NameError: name 'tips' is not defined

Selection of columns¶

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.

Keep certain columns¶

In [27]: tips[["sex", "total_bill", "tip"]]
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [27], line 1
----> 1 tips[["sex", "total_bill", "tip"]]

NameError: name 'tips' is not defined

Drop a column¶

In [28]: tips.drop("sex", axis=1)
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [28], line 1
----> 1 tips.drop("sex", axis=1)

NameError: name 'tips' is not defined

Rename a column¶

In [29]: tips.rename(columns={"total_bill": "total_bill_2"})
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [29], line 1
----> 1 tips.rename(columns={"total_bill": "total_bill_2"})

NameError: name 'tips' is not defined

Sorting by values¶

Sorting in Stata is accomplished via sort

sort sex total_bill

pandas has a DataFrame.sort_values() method, which takes a list of columns to sort by.

In [30]: tips = tips.sort_values(["sex", "total_bill"])
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [30], line 1
----> 1 tips = tips.sort_values(["sex", "total_bill"])

NameError: name 'tips' is not defined

In [31]: tips
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [31], line 1
----> 1 tips

NameError: name 'tips' is not defined

String processing¶

Finding length of string¶

Stata determines the length of a character string with the strlen() and ustrlen() functions for ASCII and Unicode strings, respectively.

generate strlen_time = strlen(time)
generate ustrlen_time = ustrlen(time)

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 [32]: tips["time"].str.len()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [32], line 1
----> 1 tips["time"].str.len()

NameError: name 'tips' is not defined

In [33]: tips["time"].str.rstrip().str.len()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [33], line 1
----> 1 tips["time"].str.rstrip().str.len()

NameError: name 'tips' is not defined

Finding position of substring¶

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.

generate str_position = strpos(sex, "ale")

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 [34]: tips["sex"].str.find("ale")
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [34], line 1
----> 1 tips["sex"].str.find("ale")

NameError: name 'tips' is not defined

Extracting substring by position¶

Stata extracts a substring from a string based on its position with the substr() function.

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 [35]: tips["sex"].str[0:1]
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [35], line 1
----> 1 tips["sex"].str[0:1]

NameError: name 'tips' is not defined

Extracting nth word¶

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.

clear
input str20 string
"John Smith"
"Jane Cook"
end

generate first_name = word(name, 1)
generate last_name = word(name, -1)

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 [36]: firstlast = pd.DataFrame({"String": ["John Smith", "Jane Cook"]})

In [37]: firstlast["First_Name"] = firstlast["String"].str.split(" ", expand=True)[0]

In [38]: firstlast["Last_Name"] = firstlast["String"].str.rsplit(" ", expand=True)[1]

In [39]: firstlast
Out[39]: 
       String First_Name Last_Name
0  John Smith       John     Smith
1   Jane Cook       Jane      Cook

Changing case¶

The Stata strupper(), strlower(), strproper(), ustrupper(), ustrlower(), and ustrtitle() functions change the case of ASCII and Unicode strings, respectively.

clear
input str20 string
"John Smith"
"Jane Cook"
end

generate upper = strupper(string)
generate lower = strlower(string)
generate title = strproper(string)
list

The equivalent pandas methods are Series.str.upper(), Series.str.lower(), and Series.str.title().

In [40]: firstlast = pd.DataFrame({"string": ["John Smith", "Jane Cook"]})

In [41]: firstlast["upper"] = firstlast["string"].str.upper()

In [42]: firstlast["lower"] = firstlast["string"].str.lower()

In [43]: firstlast["title"] = firstlast["string"].str.title()

In [44]: firstlast
Out[44]: 
       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 [45]: df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})

In [46]: df1
Out[46]: 
  key     value
0   A  0.469112
1   B -0.282863
2   C -1.509059
3   D -1.135632

In [47]: df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})

In [48]: df2
Out[48]: 
  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.

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.

* 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 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 [49]: inner_join = df1.merge(df2, on=["key"], how="inner")

In [50]: inner_join
Out[50]: 
  key   value_x   value_y
0   B -0.282863  1.212112
1   D -1.135632 -0.173215
2   D -1.135632  0.119209

In [51]: left_join = df1.merge(df2, on=["key"], how="left")

In [52]: left_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

In [53]: right_join = df1.merge(df2, on=["key"], how="right")

In [54]: right_join
Out[54]: 
  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 [55]: outer_join = df1.merge(df2, on=["key"], how="outer")

In [56]: outer_join
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
5   E       NaN -1.044236

Missing data¶

Both pandas and Stata 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 [57]: outer_join
Out[57]: 
  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 [58]: outer_join["value_x"] + outer_join["value_y"]
Out[58]: 
0         NaN
1    0.929249
2         NaN
3   -1.308847
4   -1.016424
5         NaN
dtype: float64

In [59]: outer_join["value_x"].sum()
Out[59]: -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 != .

In pandas, Series.isna() and Series.notna() can be used to filter the rows.

In [60]: outer_join[outer_join["value_x"].isna()]
Out[60]: 
  key  value_x   value_y
5   E      NaN -1.044236

In [61]: outer_join[outer_join["value_x"].notna()]
Out[61]: 
  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 [62]: outer_join.dropna()
Out[62]: 
  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 [63]: outer_join.fillna(method="ffill")
Out[63]: 
  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 [64]: outer_join["value_x"].fillna(outer_join["value_x"].mean())
Out[64]: 
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¶

Stata’s collapse can be used to group by one or more key variables and compute aggregations on numeric columns.

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 [65]: tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [65], line 1
----> 1 tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()

NameError: name 'tips' is not defined

In [66]: tips_summed
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [66], line 1
----> 1 tips_summed

NameError: name 'tips_summed' is not defined

Transformation¶

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.

bysort sex smoker: egen group_bill = mean(total_bill)
generate adj_total_bill = total_bill - group_bill

pandas provides a Transformation mechanism that allows these type of operations to be succinctly expressed in one operation.

In [67]: gb = tips.groupby("smoker")["total_bill"]
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [67], line 1
----> 1 gb = tips.groupby("smoker")["total_bill"]

NameError: name 'tips' is not defined

In [68]: tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [68], line 1
----> 1 tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")

NameError: name 'tips' is not defined

In [69]: tips
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [69], line 1
----> 1 tips

NameError: name 'tips' is not defined

By group processing¶

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 [70]: tips.groupby(["sex", "smoker"]).first()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [70], line 1
----> 1 tips.groupby(["sex", "smoker"]).first()

NameError: name 'tips' is not defined

Other considerations¶

Disk vs memory¶

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.

Comparison with SAS

Community tutorials

Comparison with Stata¶

Data structures¶

General terminology translation¶

DataFrame¶

Series¶

Index¶

Copies vs. in place operations¶

Data input / output¶

Constructing a DataFrame from values¶

Reading external data¶

Limiting output¶

Exporting data¶

Data operations¶

Operations on columns¶

Filtering¶

If/then logic¶

Date functionality¶

Selection of columns¶

Keep certain columns¶

Drop a column¶

Rename a column¶

Sorting by values¶

String processing¶

Finding length of string¶

Finding position of substring¶

Extracting substring by position¶

Extracting nth word¶

Changing case¶

Merging¶

Missing data¶

Drop rows with missing values¶

Forward fill from previous rows¶

Replace missing values with a specified value¶

GroupBy¶

Aggregation¶

Transformation¶

By group processing¶

Other considerations¶

Disk vs memory¶

`DataFrame`¶

`Series`¶

`Index`¶