Meet the Pandas

Pandas includes a rich set of input functions that allow you to get data from various file types

A data frame contains many functions to explore it e.g. the .head() method shows the first lines of a data frame

titanic = pd.read_csv("datasets/titanic.csv")
titanic.head()

a data frame is a table; you can get its column names using the .columns attributes

titanic.columns

Index([’PassengerId’, ’Survived’, ’Pclass’, ’Name’, ’Sex’, ’Age’, ’SibSp’, ’Parch’, ’Ticket’, ’Fare’, ’Cabin’, ’Embarked’], dtype=’object’)

columns can be accessed individually or in groups; this operation is called projection

Single columns can be accessed either

1. using a square bracket operator df.["age"]
2. using the dot operator if the column name is a good identifier df.age

Each column is called a Series in pandas jargon

Groups of columns can be accessed by passing a list of strings to the bracket operator

titanic[["Survived","Pclass","Sex","Age"]].head()

operations on series are vectorized i.e. each individual element is used to get a new vector

Operations within a series and a scalar value are repeated for all values of a series

titanic.Pclass == 1

returns a series of booleans

By passing a list of booleans to the square bracket operators this filters all of the lines which are satisfying the logic statement expressed; this operation is called selection which is a synonim for filter

titanic[titanic.Pclass == 1].head()

Usually selection and projection are used at the same time; the .loc[,] operator can be conveniently used for this purpose; its arguments are:

1. a boolean list for rows or the splice operator : for no filter
2. a string list of column names or the splice operator : for all columns

titanic.loc[titanic.Pclass==1,["Survived","Sex","Age"]].head()

It is possible to extend a table with more columns possibly as a result from a computation in other columns

To create a new column, just assign an expression to a new column name e.g.

df["above_average"] = (df.score > df.score.mean())

countries = pd.read_csv("datasets/WorldDBTables/CountryTable.csv")
countries.columns

Index([’code’, ’name’, ’continent’, ’region’, ’surface_area’, ’independence_year’, ’population’, ’life_expectancy’, ’gnp’, ’gnp_old’, ’local_name’, ’government_form’, ’head_of_state’, ’capital’, ’code2’], dtype=’object’)

A relation may be composed by more than a table; this may offer some consistency and operation efficiency.

If two tables represents entitites which are related they can be joined by selecting one or more columns which contains those attributes which creates the relationship.

Per each matched rows in a table, this is replicated as many times as the matched rows in the other table

There are four kinds of available joins

Suppose we have a list of courses, classrooms and classroom booking per each course; if we want to know where each professor should hold his lesson we need to join these tables

the pd.merge() function performs the join operation e.g.

courses_classrooms = pd.merge(courses,classroom)
courses_bookings = ps.merge(courses_classroom, bookings)

The default kind of join is inner you can use the how= optional argument to choose another kind.

pd.merge will join by default all columns with identical name: if you want to restrict the join to a given list of column you can use the on= option.

If you have different names for the join columns you can use left_on= and right_on= options to match them.

very often you may want to group your data according to one or more attribute and perform some calculation on each group, this operation is called aggregation

e.g. suppose I want to split a restaurant bill with my friends and I have a dataframe which looks like the following table

groups = bill.groupby(["person"])
groups.agg({"amount":"sum"})

will return

It is also possible to ask for multiple aggregation by using a list of functions

g = titanic.groupby(["Pclass","Sex"])
age_summary = g.agg({"Age":["min","max","mean"]})
age_summary

Please note that here the generated columns are accessible using a tuple i.e.

age_summary[("Age","mean")]

using the languages_by_country table we created in the previous exercise

1. create a grouping by using the "language" column
2. using the .agg() method calculate how many people speak each language
3. sort the dataset from the largest group descending
4. show the first lines using .head() method

g = languages_by_country.groupby(["language"])
languages_spoken = g.agg({"people_speaking":"sum"})
languages_spoken_sorted = languages_spoken.sort_values("people_speaking",ascending=False)
languages_spoken_sorted.head(20)

g = languages_by_country.groupby(["continent","language"])
languages_spoken = g.agg({"people_speaking":"sum"})
languages_spoken_sorted = languages_spoken.sort_values("people_speaking",ascending=False)
languages_spoken_sorted.head(20)

languages_spoken_sorted=languages_spoken_sorted.reset_index()
g = languages_spoken_sorted.groupby(["continent"])
result = []
for i,subtable in g:
    result.append(subtable.head(3).reset_index())
pd.concat(result).head()

Suppose we need to translate some foreign language content

resources = pd.read_csv("ds523_consumoacquaenergia.csv",sep=";")
resources.head()

The second column looks like a categorical series, so let’s check it

resources["Consumo pro capite tipo"].unique()

[’Energia elettrica per uso domestico’ ’Gas metano per uso domestico e riscaldamento’ ’Acqua fatturata per uso domestico’]

we can pass a dictionary to the .map() method like this:

translate = {
    'Energia elettrica per uso domestico':'electricity',
    'Gas metano per uso domestico e riscaldamento':'methan',
    'Acqua fatturata per uso domestico':'water'
}
resources["type"] = resources["Consumo pro capite tipo"].map(translate)

Also columns can be renamed or removed

resources = resources.rename({"anno":"year","Consumo pro capite":"usage per person"}, axis="columns")
del resources["Consumo pro capite tipo"]
resources.head()

Pivot is a family of aggregation functions whose main purpose is to collect data from a relation and aggregate them by using one or more attribute columns.

This process will create a column per each combination of the attributes; the result table is sometime referred as “wide format” table or “two entries table”; let’s make an example

resources2 = resources.pivot(index="year",columns="type",values="usage per person").reset_index()
resources2

As there was exactly one value per each year and each commodity the previous example just moved values without performing any calculation.

Suppose now we want to split some restaurant bill

bill = pd.DataFrame([
    {"item":"pepperoni pizza", "amount":12, "customer": "Marco", "day": "Monday"},
    {"item":"beer", "amount":7.5, "customer": "Marco", "day": "Monday"},
    {"item":"coffee", "amount":1.2, "customer": "Marco", "day": "Monday"},
    {"item":"pizza margherita", "amount":10, "customer": "Luca", "day": "Monday"},
    {"item":"wine", "amount":10, "customer": "Luca", "day": "Monday"},
    {"item":"steak", "amount":20, "customer": "Marco", "day": "Tuesday"},
    {"item":"bottled water", "amount":5, "customer": "Marco", "day": "Tuesday"},
])
bill

pandas function pivot_table allows to define an aggregation function in case of collision

splitted_bill = pd.pivot_table(bill,index="day",values="amount",columns="customer",aggfunc="sum")
splitted_bill

pandas pd.melt() function provides a way to get a “long format” table

pd.melt(splitted_bill)