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. This means that we can refer to the
libraries as pd
and np
, respectively, for the rest of the document.
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:
list in 1/5
Data Structures¶
General Terminology Translation¶
pandas | Stata |
---|---|
DataFrame |
data set |
column | variable |
row | observation |
groupby | bysort |
NaN |
. |
DataFrame
/ 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
.
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.
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.github.com/pandas-dev/pandas/master/pandas/tests/data/tips.csv'
In [6]: tips = pd.read_csv(url)
In [7]: tips.head()
Out[7]:
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
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.
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 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
.
In [8]: tips['total_bill'] = tips['total_bill'] - 2
In [9]: tips['new_bill'] = tips['total_bill'] / 2
In [10]: tips.head()
Out[10]:
total_bill tip sex smoker day time size new_bill
0 14.99 1.01 Female No Sun Dinner 2 7.495
1 8.34 1.66 Male No Sun Dinner 3 4.170
2 19.01 3.50 Male No Sun Dinner 3 9.505
3 21.68 3.31 Male No Sun Dinner 2 10.840
4 22.59 3.61 Female No Sun Dinner 4 11.295
In [11]: tips = tips.drop('new_bill', axis=1)
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 [12]: tips[tips['total_bill'] > 10].head()
Out[12]:
total_bill tip sex smoker day time size
0 14.99 1.01 Female No Sun Dinner 2
2 19.01 3.50 Male No Sun Dinner 3
3 21.68 3.31 Male No Sun Dinner 2
4 22.59 3.61 Female No Sun Dinner 4
5 23.29 4.71 Male No Sun Dinner 4
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 [13]: tips['bucket'] = np.where(tips['total_bill'] < 10, 'low', 'high')
In [14]: tips.head()
Out[14]:
total_bill tip sex smoker day time size bucket
0 14.99 1.01 Female No Sun Dinner 2 high
1 8.34 1.66 Male No Sun Dinner 3 low
2 19.01 3.50 Male No Sun Dinner 3 high
3 21.68 3.31 Male No Sun Dinner 2 high
4 22.59 3.61 Female No Sun Dinner 4 high
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 [15]: tips['date1'] = pd.Timestamp('2013-01-15')
In [16]: tips['date2'] = pd.Timestamp('2015-02-15')
In [17]: tips['date1_year'] = tips['date1'].dt.year
In [18]: tips['date2_month'] = tips['date2'].dt.month
In [19]: tips['date1_next'] = tips['date1'] + pd.offsets.MonthBegin()
In [20]: tips['months_between'] = (tips['date2'].dt.to_period('M') -
....: tips['date1'].dt.to_period('M'))
....:
In [21]: tips[['date1','date2','date1_year','date2_month',
....: 'date1_next','months_between']].head()
....:
Out[21]:
date1 date2 date1_year date2_month date1_next months_between
0 2013-01-15 2015-02-15 2013 2 2013-02-01 25
1 2013-01-15 2015-02-15 2013 2 2013-02-01 25
2 2013-01-15 2015-02-15 2013 2 2013-02-01 25
3 2013-01-15 2015-02-15 2013 2 2013-02-01 25
4 2013-01-15 2015-02-15 2013 2 2013-02-01 25
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. 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()
Out[22]:
sex total_bill tip
0 Female 14.99 1.01
1 Male 8.34 1.66
2 Male 19.01 3.50
3 Male 21.68 3.31
4 Female 22.59 3.61
# drop
In [23]: tips.drop('sex', axis=1).head()