DataFrame and Series¶
The DataFrame is the data structure everything in Pandas revolves around: a table with named columns, each column with its own data type. If you know R, you know it as data.frame; if you have only used Numpy so far, think of it as a record array (np.genfromtxt(..., names=True) in the chapter Exploratory analysis produces one of those), only more convenient. The nearest thing to it is an Excel sheet in Python.
Unlike a Numpy array, a DataFrame is always two-dimensional – there is no such thing as a DataFrame with three or four axes. That is not a restriction but a commitment to exactly the shape data usually arrive in: rows are observations, columns are features.
import numpy as np
import pandas as pd
columns = {
"species": ["setosa", "setosa", "versicolor"],
"sepal_width": [3.5, 3.0, 3.2],
}
df = pd.DataFrame(columns)
print(df) species sepal_width
0 setosa 3.5
1 setosa 3.0
2 versicolor 3.2
There are two ways to get at a column, and they do the same thing: with square brackets, as for a dictionary, or as an attribute. The brackets always work; the attribute notation only works if the column name is a valid Python identifier and does not happen to shadow a method – so when in doubt, brackets.
print(df["sepal_width"])
print(df.sepal_width) # the same thing
print(df.dtypes) # here you can see that Numpy is underneath
print(df["sepal_width"].to_numpy())0 3.5
1 3.0
2 3.2
Name: sepal_width, dtype: float64
0 3.5
1 3.0
2 3.2
Name: sepal_width, dtype: float64
species str
sepal_width float64
dtype: object
[3.5 3. 3.2]
A single column is not a DataFrame but the second of the two data structures Pandas has: a Series, that is, a one-dimensional array with an index. Most methods exist for both, some only for one of the two – the error message 'Series' object has no attribute ... usually means you have accidentally got hold of a column instead of the table.
To disk and back is to_csv and read_csv; the latter also accepts a URL. The index=False when writing matters: without it the row index lands in the file as a column and comes back as Unnamed: 0 when the file is read in again.
print(type(df["sepal_width"]))
df.to_csv("example.csv", index=False, encoding="utf-8")
back_again = pd.read_csv("example.csv")
print(back_again)
import os
os.remove("example.csv")<class 'pandas.Series'>
species sepal_width
0 setosa 3.5
1 setosa 3.0
2 versicolor 3.2
Pandas has some of the best documentation in the Python ecosystem, and it is worth using instead of guessing: the tutorials for getting started, the API reference with a page per method (pandas.DataFrame.groupby, say) and the overview of the data types.
That last one is worth a look because the Pandas type system goes beyond the Numpy types. Besides int64, float64 and bool there are, among others:
| Kind of data | pandas data type | Scalar |
|---|---|---|
| point in time with time zone | DatetimeTZDtype | Timestamp |
| duration | – | Timedelta |
| period | PeriodDtype | Period |
| interval | IntervalDtype | Interval |
| integer with missing values | Int64Dtype, … | – |
| categorical values | CategoricalDtype | – |
| sparse column | SparseDtype | – |
| strings | StringDtype | str |
| truth values with missing values | BooleanDtype | – |
Two of them will turn up again: CategoricalDtype in one-hot encoding in Regression analysis and the timestamps in Data cleaning. That the column species above has type str rather than object is new – up to pandas 2, strings lived in an object column, which you will still see often in older code and older tutorials.
SQL and relational databases¶
Anything that can be expressed in SQL can be expressed with DataFrames, and the rest of this chapter puts the two side by side. A few words on where that language fits are in order first.
SQL is the standard query language for relational database systems (RDBMS) and has been an ISO standard since 1987. The two most widespread free systems are MySQL and PostgreSQL; they sit behind a large share of the smaller websites – every WordPress installation comes with a database, and WordPress powers something like 40% of all websites. Anyone pulling data out of a live system almost always writes SQL first and only then carries on in Python.
The most important commands, grouped by what they touch:
| Command | Effect |
|---|---|
SELECT | select data and output it |
INSERT INTO | add rows |
UPDATE | change existing values |
DELETE | delete rows |
CREATE / ALTER DATABASE | create, change a database |
CREATE / ALTER / DROP TABLE | create, change, delete a table |
CREATE / DROP INDEX | create, delete a search key |
A query then looks like this, for example:
SELECT "Petal.Width" FROM iris;The vocabulary of the relational world is worth knowing, because you meet it constantly in documentation and error messages:
Translated into the Pandas world:
| relational | Pandas |
|---|---|
| relation / table | DataFrame |
| attribute / column | Series |
| tuple / row | a row, addressed via the index |
| domain | the dtype of the column |
One difference remains: mathematically, a relation is a set of tuples and so has no order, whereas a DataFrame keeps its rows in order and addresses them through an index. That is why SQL needs an ORDER BY to get any order at all – and why Pandas does not guarantee the row order after a merge.
From SQL to Pandas¶
The comparison below follows the Pandas documentation, section “Comparison with SQL”. In each code cell, the SQL statement appears as a comment above the Pandas expression that does the same job.
import numpy as np
import pandas as pd
url = (
"https://raw.github.com/pandas-dev"
"/pandas/main/pandas/tests/io/data/csv/tips.csv"
)
tips = pd.read_csv(url)
print(tips) 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
.. ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3
240 27.18 2.00 Female Yes Sat Dinner 2
241 22.67 2.00 Male Yes Sat Dinner 2
242 17.82 1.75 Male No Sat Dinner 2
243 18.78 3.00 Female No Thur Dinner 2
[244 rows x 7 columns]
Operations on Pandas DataFrames produce copies; the original DataFrame stays unchanged.
tips.sort_values("total_bill")
print(tips)
print("\nThat is not sorted at all! But the next one is:\n")
sorted_tips = tips.sort_values("total_bill")
print(sorted_tips) 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
.. ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3
240 27.18 2.00 Female Yes Sat Dinner 2
241 22.67 2.00 Male Yes Sat Dinner 2
242 17.82 1.75 Male No Sat Dinner 2
243 18.78 3.00 Female No Thur Dinner 2
[244 rows x 7 columns]
That is not sorted at all! But the next one is:
total_bill tip sex smoker day time size
67 3.07 1.00 Female Yes Sat Dinner 1
92 5.75 1.00 Female Yes Fri Dinner 2
111 7.25 1.00 Female No Sat Dinner 1
172 7.25 5.15 Male Yes Sun Dinner 2
149 7.51 2.00 Male No Thur Lunch 2
.. ... ... ... ... ... ... ...
182 45.35 3.50 Male Yes Sun Dinner 3
156 48.17 5.00 Male No Sun Dinner 6
59 48.27 6.73 Male No Sat Dinner 4
212 48.33 9.00 Male No Sat Dinner 4
170 50.81 10.00 Male Yes Sat Dinner 3
[244 rows x 7 columns]
Selecting columns is SELECT:
# SELECT total_bill, tip, smoker, time FROM tips;
tips[["total_bill", "tip", "smoker", "time"]]You can add new columns computed from old ones:
# SELECT *, tip/total_bill as tip_rate FROM tips;
tips.assign(tip_rate=tips["tip"] / tips["total_bill"])Filtering data (selecting rows by conditions) with WHERE
# SELECT * FROM tips WHERE time = 'Dinner';
is_dinner = tips["time"] == "Dinner" # bitmask
tips[is_dinner]Combining several conditions logically with OR | or AND &:
# SELECT * FROM tips WHERE time = 'Dinner' AND tip > 6.00;
tips[(tips["time"] == "Dinner") & (tips["tip"] > 6.00)]# SELECT * FROM tips WHERE size >= 6 OR total_bill > 45;
tips[(tips["size"] >= 6) | (tips["total_bill"] > 45)]Split the data into groups, compute aggregate statistics, put the groups back together – that is GROUP BY in SQL and groupby in Pandas:
# SELECT sex, count(*) FROM tips GROUP BY sex;
tips.groupby("sex").size()
# tips.groupby("sex").count() would be wrong, that counts non-NA entries!sex
Female 87
Male 157
dtype: int64# SELECT day, AVG(tip), COUNT(*) FROM tips GROUP BY day;
tips.groupby("day").agg({"tip": "mean", "day": np.size})A trap that snaps shut again and again when you aggregate: the mean of the group means is not the
overall mean, unless all the groups are the same size. The overall mean is the weighted
mean of the group means, with the group sizes as weights. With the values in
group A and in group B, the group means are 20 and 10 and their mean is 15 – but
the overall mean of all five values is 16. So if all you have is a table of group means and
you want the overall mean from it, you need the size column as well. The same phenomenon in
a sharper form – group means whose order reverses when the groups are pooled – is called
Simpson’s paradox.
small = pd.DataFrame({"Group": ["A", "A", "A", "B", "B"], "Value": [10, 20, 30, 5, 15]})
group_means = small.groupby("Group")["Value"].mean()
print("mean of the group means:", group_means.mean())
print("overall mean: ", small["Value"].mean())
# correct: weight by the group sizes
sizes = small.groupby("Group")["Value"].size()
print("weighted: ", (group_means * sizes).sum() / sizes.sum())
mean of the group means: 15.0
overall mean: 16.0
weighted: 16.0
# SELECT smoker, day, COUNT(*), AVG(tip) FROM tips GROUP BY smoker, day;
tips.groupby(["smoker", "day"]).agg({"tip": [np.size, "mean"]})Data rarely arrive in a single table. Suppose the Iris measurements sit in two files: one holds the species of each plant, the other its sepal width. Both have the same number of rows – but may we simply lay them side by side? Only if the rows are in the same order, and you cannot know that once anyone has sorted one of the files in between.
So every row gets a column that identifies it uniquely: a key. That can be a plain number, or some unique piece of information that already exists (a student number, an ISBN). When data come together from several sources and nobody can hand out numbers centrally, you use a UUID (universally unique identifier), a 128-bit random number for which a collision is so unlikely that it can be ignored. The column that uniquely indexes a table is called its primary key; when the same key turns up in another table to point at those rows, it is a foreign key there. That is the basic idea of relational databases. The chapter Relational data of R for Data Science has a fuller account.
import uuid
species = pd.DataFrame({"No": [1, 51], "species": ["setosa", "versicolor"]})
width = pd.DataFrame({"No": [51, 1], "sepal_width": [3.2, 3.5]})
print(species, "\n")
print(width, "\n")
# merged on the key No, so the order does not matter:
print(pd.merge(species, width, on="No"), "\n")
print(uuid.uuid4()) # a fresh UUID No species
0 1 setosa
1 51 versicolor
No sepal_width
0 51 3.2
1 1 3.5
No species sepal_width
0 1 setosa 3.5
1 51 versicolor 3.2
2c741528-2614-4361-a4e1-b190d6217e53
It gets interesting when the two tables do not have the same keys. Then you have to decide what happens to the rows that find no partner – and that is exactly what separates the four kinds of join. The two tables below are built so that every case occurs: A exists only on the left, E only on the right, and D appears twice on the right.
df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})
print(df1, "\n", df2) key value
0 A 0.370613
1 B 1.987784
2 C -0.885252
3 D 0.673109
key value
0 B -1.594238
1 D 1.483229
2 D -0.353315
3 E 0.321249
# SELECT * FROM df1 INNER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key") # inner join is the defaultThe inner join keeps only the keys that occur in both tables: B and D. That D yields two rows is the most important point in the whole business – the key is not unique in df2, and a join then multiplies the rows on the other side. If a dataset is suddenly longer after a join than before, this is almost always where the problem lies.
# SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="left")The left outer join keeps every row of the left table, partner or not: A and C come along too, with NaN in the columns from df2. This is the commonest case in practice – you have a dataset and want to enrich it with extra information without losing any rows along the way.
# SELECT * FROM df1 RIGHT OUTER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="right")The right outer join is the same with the roles swapped: every row of df2, E included, and NaN where the left-hand side is missing.
# SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="outer")The full outer join keeps everything, A to E, and pads both sides with NaN wherever nothing matches. Afterwards it is nearly always worth a look at isna().sum() to see how many gaps you have let yourself in for.
Changing data is UPDATE in SQL; in Pandas we simply run Python code, as if we were changing a single variable:
# state before the change:
tips.sort_values("tip").head()# UPDATE tips SET tip = tip*2 WHERE tip < 2;
tips.loc[tips["tip"] < 2, "tip"] *= 2
tips.sort_values("tip").head()# state before the next change:
tips.sort_values("tip", ascending=False).head()# DELETE FROM tips WHERE tip > 9;
tips = tips.loc[tips["tip"] <= 9]
tips.sort_values("tip", ascending=False).head()A first look at a dataset¶
Pandas comes with the tools we discussed in the chapter on descriptive statistics – the ones that were still handwork with Numpy in the Titanic notebook. The ‘broken’ CSV from there is read directly by read_csv, which understands quotation marks. info shows the columns, the data types and how many values are missing: compare this with the Titanic notebook from the chapter on Numpy, where we converted the CSV into a TSV by hand, marked missing values with masks and counted groups with loops.
titanic = pd.read_csv("titanic.csv")
titanic.info()<class 'pandas.DataFrame'>
RangeIndex: 891 entries, 0 to 890
Data columns (total 12 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 PassengerId 891 non-null int64
1 Survived 891 non-null int64
2 Pclass 891 non-null int64
3 Name 891 non-null str
4 Sex 891 non-null str
5 Age 714 non-null float64
6 SibSp 891 non-null int64
7 Parch 891 non-null int64
8 Ticket 891 non-null str
9 Fare 891 non-null float64
10 Cabin 204 non-null str
11 Embarked 889 non-null str
dtypes: float64(2), int64(5), str(5)
memory usage: 83.7 KB
For every numerical column, describe gives the count, the mean, the standard deviation (with in the denominator!), the minimum, the quartiles and the maximum:
titanic.describe()For categorical columns, value_counts counts the levels:
print(titanic["Sex"].value_counts())
print(titanic["Embarked"].value_counts(dropna=False)) # count the missing values tooSex
male 577
female 314
Name: count, dtype: int64
Embarked
S 644
C 168
Q 77
NaN 2
Name: count, dtype: int64
isna spots missing values. You can discard them (dropna) or replace them (fillna), with the median for instance. How to deal systematically with missing values, outliers, text fields and dates is the subject of the chapter Data cleaning.
Skewness and kurtosis from the chapter on expected values are built in as well; kurt gives the excess kurtosis, that is, the kurtosis minus 3:
print(titanic.isna().sum())
age = titanic["Age"].fillna(titanic["Age"].median())
print("skewness:", age.skew(), " excess kurtosis:", age.kurt())PassengerId 0
Survived 0
Pclass 0
Name 0
Sex 0
Age 177
SibSp 0
Parch 0
Ticket 0
Fare 0
Cabin 687
Embarked 2
dtype: int64
skewness: 0.5102446555756495 excess kurtosis: 0.9938710163801736
Grouped summary measures come from groupby, as above:
titanic.groupby("Pclass")["Age"].describe()