Pandas cheat sheet

Preliminaries

Before doing anything else, go through the 10-minute intro tutorial.

For a fuller treatment, dip your toes into chapters 7, 11, 12, 13, 16, 17, and 18 of Crystal Ball vol One. I try to be as terse as I can in there without omitting anything important, so I think any time you invest in these chapters will be well rewarded.

Import the package

“import pandas as pd” is the standard import, at the top of every file. (If this doesn’t work for you, exit the interpreter, make sure the correct virtual environment is activated, and then type “pip install pandas” at the command line.)

Tweaking printing

By default, Pandas doesn’t print out as much of its DataFrames as I like. So I set these two options at the top of my file:

pd.set_option("display.max.columns",80)
pd.set_option("display.width",240)

(There’s nothing magic about those particular values; I just want it to be “big enough.”)

About DataFrames

The DataFrame is the lingua franca of modern data analysis. It is a spreadsheet-y looking structure with rows (normally displayed left-to-right) and columns (up-to-down), but rows and columns are not interchangeable. Among other things, DataFrame rows are normally heterogeneous (they have many different data types within them) whereas columns are always homogeneous (all one data type).

Suppose you have successfully loaded a DataFrame called df. You can see the names of all its columns typing “df.columns”, and you can see the data type of each column by typing “df.dtypes”. Note that a type of “object” means “string data.”

Each column of a DataFrame is a smaller, one-dimensional Pandas object called a Series. If your df DataFrame has a column Weapon, you can access that column alone by typing either df['Weapon'] or df.Weapon. The latter is shorter, but is only possible when you have no spaces or other funky characters in the column name. It’s also only possible when the column you want already exists; if you want to create a new column – say, called Armor – you’ll have to use the first form (df['Armor'] = ...).

Possible connection

Btw, if you’ve taken CPSC 350, you’re familiar with various ways to slice, dice, join, view, filter, and update relational database tables. Those instincts will serve you very well here, since a DataFrame is essentially a table, with extra functionality available to you in Python.

Semi-automated ways to produce a DataFrame

Use pd.read_csv(filename) to read a comma-separated, or tab-separated (with sep="\t" argument) text file. You can also use pd.read_html() to screen-scrape HTML tables, pd.read_json() to import JSON files, pd.read_sql() to read from SQL databases, etc. Each of these operations requires its own documentation and are not generally compatible with each other. You must read the docs!

Some useful things to do on a DataFrame

Say you have a DataFrame named df. You can:

• Print only the top or bottom few rows with df.head() or df.tail().
• Select only certain columns with df[[ , ]]
• Rename columns with df.columns = [ , , ]
• Dump its contents to a Parquet file with df.to_parquet(filename). See the documentation for other useful options here.
• Dump its contents to a CSV file with df.to_csv(filename). See the documentation for other useful options here.

Some useful things to do on a Series (DataFrame column)

• If you want a copy of a Series, rather than a reference to the same object in memory, you can .copy() it.
• Appending .dtype to the Series/column name will tell you what data type it holds. You can sometimes convert this by using the .astype() method. (For example, df.Ages = df.Ages.astype(int).)
• On a categorical column:
  • df.MyCatCol.unique()
  • df.MyCatCol.value_counts()
  • Perform string operations. Many of them have .str at the end of the column name, followed by a method call. For example:
    • df.Name.str.replace("Beavis","Butthead")
    • df.Country.str.strip()
    • df.Ship.str.startswith("U.S.S.")
  • The .str.split() method is useful if your column entries actually contain multiple pieces of information. (“4-5”,“Tuscon, AZ”, “Moiraine Sedai”, “DATA 419”, etc.) The first argument to split is the text you want to split on (this would be a dash in the first example, a comma-followed-by-a-space in the second example, and a space in the other two.) Adding expand=True as a second argument will give you back a list of two different Serieses, which you can assign as new columns to your DataFrame.
  • If you want to map one set of values to another set (perhaps with shorter names), you can create a dict (called, say, map) and then call df.MyCatCol.str.map(map) to get a copy with the substituted values.
  • If your Series actually contains numeric info, together with some other string crap denoting missing values (like “None” or “NA” or “Missing”) you’ll probably want to convert it to numeric, and have the missing values filled with something. For example: pd.to_numeric(df.MyShouldBeNumCol, errors='coerce').fillna(0).
• On a numeric column:
  • df.MyNumCol.max()
  • df.MyNumCol.min()
  • df.MyNumCol.mean()
  • df.MyNumCol.median()
  • df.MyNumCol.std()
  • df.MyNumCol.histogram()