Spring 2020 Data 100/200 Midterm Reference Sheet
Pandas and Matplotlib
df is a DataFrame; s is a Series.
Function Description
df[col] Returns the column labeled col from df as a Series.
Returns a DataFrame containing the columns labeled col1 and
df[[col1, col2]]
col2 .
[Link][rows] / [Link][rows, Returns a Series/DataFrame with rows (and columns) selected by
cols] their index values.
[Link][rows] / [Link][rows, Returns a Series/DataFrame with rows (and columns) selected by
cols] their positions.
[Link]() / [Link]() Returns boolean Series/DataFrame identifying missing values
[Link](value) / Returns a Series/DataFrame where missing values are replaced by
[Link](value) value
Returns a DataFrame without the rows or columns named labels
[Link](labels, axis)
along axis (either 0 or 1)
[Link](index=None, Returns a DataFrame with renamed columns from a dictionary
columns=None) index and/or columns
df.sort_values(by, Returns a DataFrame where rows are sorted by the values in
ascending=True) columns by
s.sort_values(ascending=True) Returns a sorted Series.
[Link]() Returns a NumPy array of the unique values
Returns the number of times each unique value appears in a
s.value_counts()
Series
[Link](left, right, Returns a DataFrame joining DataFrames left and right on the
how='inner', on='a') column labeled a ; the join is of type inner
[Link](right, Returns a DataFrame joining DataFrames left and right on
left_on=col1, right_on=col2) columns labeled col1 and col2 .
Returns a DataFrame that uses the values in the column labeled
df.set_index(col)
col as the row index.
Returns a DataFrame that has row index 0, 1, etc., and adds the
df.reset_index(col)
current index as a column.
Groups, Strings, & Plots
grouped = [Link](by) where by can be a column label or a list of labels.
Function Description
Return a Series containing the size of each
[Link]()
group, excluding missing values
Return a Series containing size of each group,
[Link]()
including missing values
Return a Series/DataFrame containing
[Link]() / [Link]() / [Link]() mean/min/max of each group for each column,
excluding missing values
[Link](f) / [Link](f) Filters or aggregates using the given function f
s is a series of strings.
Function Description
[Link]() Returns a Series containing length of each string
Returns a Series containing lowercase/uppercase version of each
[Link]() / [Link]()
string
Returns a Series after replacing occurences of substrings matching
[Link](pat, repl)
regular expression pat with string repl
Returns a boolean Series indicating whether a substring matching
[Link](pat)
the regular expression pat is contained in each string
Returns a Series of the first subsequence of each string that
[Link](pat) matches the regular expression pat . If pat contains one group,
then only the substring matching the group is extracted
x and y are sequences of values.
Function Description
[Link](x, y) Creates a line plot of x against y
[Link](x, y) Creates a scatter plot of x against y
[Link](x, bins=None) Creates a histogram of x ; bins can be an integer or a sequence
Creates a bar plot of categories x and corresponding heights
[Link](x, height)
height
Regular Expressions
List of all metacharacters: . ^ $ * + ? ] [ \ | ( ) { }
Operator Description
. Matches any character except \n
\ Escapes metacharacters
| Matches expression on either side of expression; has lowest priority of any operator
Predefined character group of digits (0-9), alphanumerics (a-z, A-Z, 0-9, and underscore), or
\d , \w, \s
whitespace, respectively
\D, \W,
Inverse sets of \d , \w, \s, respectively
\S
* Matches preceding character/group zero or more times
? Matches preceding character/group zero or one times
+ Matches preceding character/group one or more times
*? , +? Applies non-greedy matching to * and +, respectively
{m} Matches preceding character/group exactly m times
Matches preceding character/group at least m times and at most n times; if either m or n are
{m, n}
omitted, set lower/upper bounds to 0 and ∞, respectively
^, $ Matches the beginning and end of the line, respectively
[] Matching group used to match any of the specified characters or range (e.g.[abcde]) [a-e])
() Capturing group used to create a sub-expression
[^ ] Invert matching group; e.g. [^a-c] matches all characters excepta , b, c
Function Description
[Link](pattern, Returns a match if zero or more characters at beginning of string
string) matches pattern , else None
[Link](pattern, Returns a match if zero or more characters anywhere in string matches
string) pattern , else None
[Link](pattern, Returns a list of all non-overlapping matches of pattern in string (if
string) none, returns empty list)
[Link](pattern, repl,
Returns string after replacing all occurrences of pattern with repl
string)
SQL
For a table x with columns labeled a and g, here are two example SELECT statements: SELECT a, a+1 AS b
FROM x WHERE b>2 ORDER BY -a SELECT g, max(a) FROM x GROUP BY g HAVING min(a) > 1
Syntax Description
FROM s INNER JOIN t on
Inner join of tables s and t using cond to filter rows
cond
FROM s JOIN t ON cond Same as above
FROM s LEFT JOIN t on
Left outer join of tables s and t using cond to filter rows
cond
From s, t Cross join of tables s and t: all pairs of a row from s and one from t
FROM (SELECT …) Select rows from a temporary table defined by a SELECT statement
Select rows for which the value in column a is among the values in a one-
WHERE a IN (SELECT …)
column temporary table defined by a select statement
ORDER BY RANDOM LIMIT
Draw a simple random sample of n rows
n
CASE WHEN pred THEN Evaluates to cons if pred is true and alt otherwise; Multiple WHEN/THEN
cons ELSE alt END pairs can be included, and ELSE is optional
WHERE s.a LIKE ‘p’ Matches each entry in the column a of table s to the pattern p