<jupyter_start><jupyter_text>Comparison with StataFor potential users coming from [Stata](https://en.wikipedia.org/wiki/Stata)this page is meant to demonstrate how different Stata operations would beperformed in pandas.If you’re new to pandas, you might want to first read through [10 Minutes to pandas](../04_user_guide/10min.ipynbmin)to familiarize yourself with the library.As is customary, we import pandas and NumPy as follows:<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>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-dimensionaldata source with labeled columns that can be of different types. As will beshown in this document, almost any operation that can be applied to a data setin 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 columnof 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’srows are essentially unlabeled, other than an implicit integer index that can beaccessed 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 importantpart of pandas to understand, for this comparison we will essentially ignore the`Index` and just treat the `DataFrame` as a collection of columns. Pleasesee the indexing documentation for much more on how to use an`Index` effectively.  Copies vs. in place operationsMost 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. :ref:`More information. `  Data input / output  Constructing a DataFrame from valuesA Stata data set can be built from specified values byplacing the data after an `input` statement andspecifying the column names. ```statainput x y1 23 45 6end``` A pandas `DataFrame` can be constructed in many different ways,but for a small number of values, it is often convenient to specify it asa Python dictionary, where the keys are the column namesand the values are the data.<jupyter_code>df = pd.DataFrame({"x": [1, 3, 5], "y": [2, 4, 6]})
df<jupyter_output><empty_output><jupyter_text>Reading external dataLike Stata, pandas provides utilities for reading in data frommany formats.  The `tips` data set, found within the pandastests ([csv](https://raw.github.com/pandas-dev/pandas/master/pandas/tests/io/data/csv/tips.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. ```stataimport delimited tips.csv``` The pandas method is `read_csv()`, which works similarly. Additionally, it will automatically downloadthe data set if presented with a url.<jupyter_code>url = (
    "https://raw.github.com/pandas-dev"
    "/pandas/master/pandas/tests/io/data/csv/tips.csv"
)
tips = pd.read_csv(url)
tips<jupyter_output><empty_output><jupyter_text>Like `import delimited`, `read_csv()` can take a number of parameters to specifyhow 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: ```pythontips = pd.read_csv("tips.csv", sep="\t", header=None) alternatively, read_table is an alias to read_csv with tab delimitertips = pd.read_table("tips.csv", header=None)``` pandas can also read Stata data sets in `.dta` format with the `read_stata()` function. ```pythondf = pd.read_stata("data.dta")``` In addition to text/csv and Stata files, pandas supports a variety of other data formatssuch 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 outputBy 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()`.<jupyter_code>tips.head(5)<jupyter_output><empty_output><jupyter_text>The equivalent in Stata would be: ```statalist in 1/5```  Exporting dataThe inverse of `import delimited` in Stata is `export delimited` ```stataexport delimited tips2.csv``` Similarly in pandas, the opposite of `read_csv` is `DataFrame.to_csv()`. ```pythontips.to_csv("tips2.csv")``` pandas can also export to Stata file format with the `DataFrame.to_stata()` method. ```pythontips.to_stata("tips2.dta")```  Data operations  Operations on columnsIn Stata, arbitrary math expressions can be used with the `generate` and`replace` commands on new or existing columns. The `drop` command dropsthe column from the data set. ```statareplace total_bill = total_bill - 2generate new_bill = total_bill / 2drop 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 dropsa column from the `DataFrame`.<jupyter_code>tips["total_bill"] = tips["total_bill"] - 2
tips["new_bill"] = tips["total_bill"] / 2
tips

tips = tips.drop("new_bill", axis=1)<jupyter_output><empty_output><jupyter_text>FilteringFiltering in Stata is done with an `if` clause on one or more columns. ```statalist if total_bill > 10``` DataFrames can be filtered in multiple ways; the most intuitive of which is usingboolean indexing.<jupyter_code>tips[tips["total_bill"] > 10]<jupyter_output><empty_output><jupyter_text>The above statement is simply passing a `Series` of `True`/`False` objects to the DataFrame,returning all rows with `True`.<jupyter_code>is_dinner = tips["time"] == "Dinner"
is_dinner
is_dinner.value_counts()
tips[is_dinner]<jupyter_output><empty_output><jupyter_text>If/then logicIn Stata, an `if` clause can also be used to create new columns. ```statagenerate bucket = "low" if total_bill < 10replace bucket = "high" if total_bill >= 10``` The same operation in pandas can be accomplished usingthe `where` method from `numpy`.<jupyter_code>tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")
tips<jupyter_output><empty_output><jupyter_text>::suppresstips = tips.drop(“bucket”, axis=1)  Date functionalityStata provides a variety of functions to do operations ondate/datetime columns. ```statagenerate 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 monthgenerate date1_next = mdy(month(date1) + 1, 1, year(date1)) if month(date1) != 12replace date1_next = mdy(1, 1, year(date1) + 1) if month(date1) == 12generate 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 thesefunctions, pandas supports other Time Series featuresnot available in Stata (such as time zone handling and custom offsets) –see the timeseries documentation for more details.<jupyter_code>tips["date1"] = pd.Timestamp("2013-01-15")
tips["date2"] = pd.Timestamp("2015-02-15")
tips["date1_year"] = tips["date1"].dt.year
tips["date2_month"] = tips["date2"].dt.month
tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()
tips["months_between"] = tips["date2"].dt.to_period("M") - tips[
    "date1"
].dt.to_period("M")

tips[
    ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
]
# cleanup
tips = tips.drop(["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"],
axis=1,
)<jupyter_output><empty_output><jupyter_text>Selection of columnsStata provides keywords to select, drop, and rename columns. ```statakeep sex total_bill tipdrop sexrename total_bill total_bill_2``` The same operations are expressed in pandas below.  Keep certain columns<jupyter_code>tips[["sex", "total_bill", "tip"]]<jupyter_output><empty_output><jupyter_text>Drop a column<jupyter_code>tips.drop("sex", axis=1)<jupyter_output><empty_output><jupyter_text>Rename a column<jupyter_code>tips.rename(columns={"total_bill": "total_bill_2"})<jupyter_output><empty_output><jupyter_text>Sorting by valuesSorting in Stata is accomplished via `sort` ```statasort sex total_bill``` pandas has a `DataFrame.sort_values()` method, which takes a list of columns to sort by.<jupyter_code>tips = tips.sort_values(["sex", "total_bill"])
tips<jupyter_output><empty_output><jupyter_text>String processing  Finding length of stringStata determines the length of a character string with the `strlen()` and`ustrlen()` functions for ASCII and Unicode strings, respectively. ```statagenerate 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.<jupyter_code>tips["time"].str.len()
tips["time"].str.rstrip().str.len()<jupyter_output><empty_output><jupyter_text>Finding position of substringStata 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 thefirst position of the substring you supply as the second argument. ```statagenerate 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, themethod returns its position. If not found, it returns `-1`. Keep in mind that Python indexes arezero-based.<jupyter_code>tips["sex"].str.find("ale")<jupyter_output><empty_output><jupyter_text>Extracting substring by positionStata extracts a substring from a string based on its position with the `substr()` function. ```statagenerate short_sex = substr(sex, 1, 1)``` With pandas you can use `[]` notation to extract a substringfrom a string by position locations. Keep in mind that Pythonindexes are zero-based.<jupyter_code>tips["sex"].str[0:1]<jupyter_output><empty_output><jupyter_text>Extracting nth wordThe Stata `word()` function returns the nth word from a string.The first argument is the string you want to parse and thesecond argument specifies which word you want to extract. ```stataclearinput str20 string"<NAME>""<NAME>"endgenerate 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 theword by index. Note there are more powerful approaches should you need them.<jupyter_code>firstlast = pd.DataFrame({"String": ["<NAME>", "<NAME>"]})
firstlast["First_Name"] = firstlast["String"].str.split(" ", expand=True)[0]
firstlast["Last_Name"] = firstlast["String"].str.rsplit(" ", expand=True)[0]
firstlast<jupyter_output><empty_output><jupyter_text>Changing caseThe Stata `strupper()`, `strlower()`, `strproper()`,`ustrupper()`, `ustrlower()`, and `ustrtitle()` functionschange the case of ASCII and Unicode strings, respectively. ```stataclearinput str20 string"<NAME>""<NAME>"endgenerate 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()`.<jupyter_code>firstlast = pd.DataFrame({"string": ["<NAME>", "<NAME>"]})
firstlast["upper"] = firstlast["string"].str.upper()
firstlast["lower"] = firstlast["string"].str.lower()
firstlast["title"] = firstlast["string"].str.title()
firstlast<jupyter_output><empty_output><jupyter_text>MergingThe following tables will be used in the merge examples:<jupyter_code>df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df1
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})
df2<jupyter_output><empty_output><jupyter_text>In Stata, to perform a merge, one data set must be in memoryand the other must be referenced as a file name on disk. Incontrast, Python must have both `DataFrames` already in memory.By default, Stata performs an outer join, where all observationsfrom both data sets are left in memory after the merge. One cankeep 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. ```stata* First create df2 and save to diskclearinput str1 keyBDDEendgenerate value = rnormal()save df2.dta* Now create df1 in memoryclearinput str1 keyABCDendgenerate value = rnormal()preserve* Left joinmerge 1:n key using df2.dtakeep if _merge == 1* Right joinrestore, preservemerge 1:n key using df2.dtakeep if _merge == 2* Inner joinrestore, preservemerge 1:n key using df2.dtakeep if _merge == 3* Outer joinrestoremerge 1:n key using df2.dta``` pandas DataFrames have a `merge()` method, which provides similar functionality. Thedata does not have to be sorted ahead of time, and different join types are accomplished via the`how` keyword.<jupyter_code>inner_join = df1.merge(df2, on=["key"], how="inner")
inner_join

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

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

outer_join = df1.merge(df2, on=["key"], how="outer")
outer_join<jupyter_output><empty_output><jupyter_text>Missing dataBoth pandas and Stata have a representation for missing data.pandas represents missing data with the special float value `NaN` (not a number).  Many of thesemantics are the same; for example missing data propagates through numeric operations, and isignored by default for aggregations.<jupyter_code>outer_join
outer_join["value_x"] + outer_join["value_y"]
outer_join["value_x"].sum()<jupyter_output><empty_output><jupyter_text>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. ```stata* Keep missing valueslist if value_x == .* Keep non-missing valueslist if value_x != .``` In pandas, `Series.isna()` and `Series.notna()` can be used to filter the rows.<jupyter_code>outer_join[outer_join["value_x"].isna()]
outer_join[outer_join["value_x"].notna()]<jupyter_output><empty_output><jupyter_text>pandas provides a variety of methods to work with missing data. Here are some examples:  Drop rows with missing values<jupyter_code>outer_join.dropna()<jupyter_output><empty_output><jupyter_text>Forward fill from previous rows<jupyter_code>outer_join.fillna(method="ffill")<jupyter_output><empty_output><jupyter_text>Replace missing values with a specified valueUsing the mean:<jupyter_code>outer_join["value_x"].fillna(outer_join["value_x"].mean())<jupyter_output><empty_output><jupyter_text>GroupBy  AggregationStata’s `collapse` can be used to group by one ormore key variables and compute aggregations onnumeric columns. ```statacollapse (sum) total_bill tip, by(sex smoker)``` pandas provides a flexible `groupby` mechanism that allows similar aggregations. See thegroupby documentation for more details and examples.<jupyter_code>tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()
tips_summed<jupyter_output><empty_output><jupyter_text>TransformationIn Stata, if the group aggregations need to be used with theoriginal data set, one would usually use `bysort` with `egen()`.For example, to subtract the mean for each observation by smoker group. ```statabysort sex smoker: egen group_bill = mean(total_bill)generate adj_total_bill = total_bill - group_bill``` pandas provides a [Transformation](../04_user_guide/36_groupby.ipynbgroupby-transform) mechanism that allows these type of operations to besuccinctly expressed in one operation.<jupyter_code>gb = tips.groupby("smoker")["total_bill"]
tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")
tips<jupyter_output><empty_output><jupyter_text>By group processingIn addition to aggregation, pandas `groupby` can be used toreplicate most other `bysort` processing from Stata. For example,the following example lists the first observation in the currentsort order by sex/smoker group. ```statabysort sex smoker: list if _n == 1``` In pandas this would be written as:<jupyter_code>tips.groupby(["sex", "smoker"]).first()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11116983791402396, 'fraction_numerical': 0.010394644115574348, 'mean_word_length': 6.465585269618589, 'pattern_counts': {'":': 9, '<': 123, '<?xml version=': 0, '>': 130, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '62708', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 5311, 'n_tokens_neox': 5070, 'n_words': 1826, 'path': '01_comparison/comparison_with_stata.ipynb', 'repo_name': 'olippuner/Pandas_Docu_as_Notebooks', 'size': '17056'}