# Python Course

Previous Chapter: Image Processing Techniques
Next Chapter: Linear Combinations

# Introduction into Pandas

There is often some confusion about whether Pandas is an alternative to Numpy, SciPy and Matplotlib. The truth is that it is built on top of Numpy. This means that Numpy is required by pandas. Scipy and Matplotlib on the other hand are not required by pandas but they are extremely useful. That's why the Pandas project lists them as "optional dependency".

Pandas is a software library written for the Python programming language. It is used for data manipulation and analysis. It provides special data structures and operations for the manipulation of numerical tables and time series. Pandas is free software released under the three-clause BSD license.

## Data Structures

We will start with the following two important data structures of Pandas:

- Series and
- DataFrame

### Series

A Series is a one-dimensional array-like object containing an array of data, which can be any NumPy data type, and an associated array of data labels, functioning as its index.

A simple example:

    import pandas as pd
    S = pd.Series([11, 28, 72, 3, 5, 8])
    S

Output:

    0    11
    1    28
    2    72
    3     3
    4     5
    5     8
    dtype: int64

We can see two columns: The indices on the left side and the values on the right side. Pandas uses a default indexing starting with 0 going to 5, which is the length of the data minus 1.

    print(S.index)
    print(S.values)

Output:

    Int64Index([0, 1, 2, 3, 4, 5], dtype='int64')
    [11 28 72  3  5  8]

If we compare this to creating an array in numpy, there are still lots of similarities:

    import numpy as np
    X = np.array([11, 28, 72, 3, 5, 8])
    print(X)
    print(S.values)
    list(S.values) == list(X)

Output:

    [11 28 72  3  5  8]
    [11 28 72  3  5  8]

Output:

    True

It's possible to create an individual index instead of the default index:

    fruits = ['apples', 'oranges', 'cherries', 'pears']
    quantities = [20, 33, 52, 10]
    S = pd.Series(quantities, index=fruits)
    S

Output:

    apples      20
    oranges     33
    cherries    52
    pears       10
    dtype: int64

A big advantage to NumPy arrays is obvious from the previous example: We can use arbitrary indices.

It's possible to select both single values or more than one value of a Series:

    print(S['apples'])

Output:

    20

    print(S[['apples', 'oranges', 'cherries']])

Output:

    apples      20
    oranges     33
    cherries    52
    dtype: int64

It's possible to scalar operations or mathematical functions on a series

    import numpy as np
    print((S + 3) * 4)
    print("======================")
    np.sin(S)

Output:

    apples       92
    oranges     144
    cherries    220
    pears        52
    dtype: int64
    ======================

Output:

    apples      0.912945
    oranges     0.999912
    cherries    0.986628
    pears      -0.544021
    dtype: float64

Filtering with a boolean array:

    S[S>30]
    S

Output:

    apples      20
    oranges     33
    cherries    52
    pears       10
    dtype: int64

A series can be seen as an ordered dictionary with a fixed length.

    "apples" in S

Output:

    True

We can even pass a dictionary to a Series object, when we create it. We get a Series with the dict's keys as the indices. The indices will be sorted.

    cities = {"London":   8615246,
              "Berlin":   3562166,
              "Madrid":   3165235,
              "Rome":     2874038,
              "Paris":    2273305,
              "Vienna":   1805681,
              "Bucharest":1803425,
              "Hamburg":  1760433,
              "Budapest": 1754000,
              "Warsaw":   1740119,
              "Barcelona":1602386,
              "Munich":   1493900,
              "Milan":    1350680}
    city_series = pd.Series(cities)
    print(city_series)

Output:

    Barcelona    1602386
    Berlin       3562166
    Bucharest    1803425
    Budapest     1754000
    Hamburg      1760433
    London       8615246
    Madrid       3165235
    Milan        1350680
    Munich       1493900
    Paris        2273305
    Rome         2874038
    Vienna       1805681
    Warsaw       1740119
    dtype: int64

We have already seen that we can pass a list or a tuple to the keyword argument 'index'. In this case, the list (or tuple) passed to index might not be equal to the keys, e.g. there may be less or more entries in index:

    my_cities = ["London", "Paris", "Zurich", "Berlin",
                 "Stuttgart", "Hamburg"]
    my_city_series = pd.Series(cities, index=my_cities)
    print(my_city_series)

Output:

    London       8615246
    Paris        2273305
    Zurich           NaN
    Berlin       3562166
    Stuttgart        NaN
    Hamburg      1760433
    dtype: float64

We can see, that the cities, which are not included in the dictionary, get the value NaN assigned. NaN stands for "not a number". It can also be seen as meaning "missing" in our example.

We can check for missing values with the methods isnull and notnull:

    print(my_city_series.isnull())

Output:

    London       False
    Paris        False
    Zurich        True
    Berlin       False
    Stuttgart     True
    Hamburg      False
    dtype: bool

    print(my_city_series.notnull())

Output:

    London        True
    Paris         True
    Zurich       False
    Berlin        True
    Stuttgart    False
    Hamburg       True
    dtype: bool

We get also a NaN, if a value in the dictionary has a None:

    d = {"a":23, "b":45, "c":None, "d":0}
    S = pd.Series(d)
    print(S)

Output:

    a    23
    b    45
    c   NaN
    d     0
    dtype: float64

    pd.isnull(S)

Output:

    a    False
    b    False
    c     True
    d    False
    dtype: bool

    pd.notnull(S)

Output:

    a     True
    b     True
    c    False
    d     True
    dtype: bool

### DataFrame

The underlying idea of a DataFrame is based on spreadsheets. We can see the data structure of a DataFrame as tabular and spreadsheet-like. It contains an ordered collection of columns. Each column consists of a unique data typye, but different columns can have different types, e.g. the first column may consist of integers, while the second one consists of boolean values and so on.

A DataFrame has a row and column index; it's like a dict of Series with a common index.

    cities = {"name": ["London", "Berlin", "Madrid", "Rome",
                       "Paris", "Vienna", "Bucharest", "Hamburg",
                       "Budapest", "Warsaw", "Barcelona",
                       "Munich", "Milan"],
              "population": [8615246, 3562166, 3165235, 2874038,
                             2273305, 1805681, 1803425, 1760433,
                             1754000, 1740119, 1602386, 1493900,
                             1350680],
              "country": ["England", "Germany", "Spain", "Italy",
                          "France", "Austria", "Romania",
                          "Germany", "Hungary", "Poland", "Spain",
                          "Germany", "Italy"]}
    city_frame = pd.DataFrame(cities)
    print(city_frame)

Output:

        country       name  population
    0   England     London     8615246
    1   Germany     Berlin     3562166
    2     Spain     Madrid     3165235
    3     Italy       Rome     2874038
    4    France      Paris     2273305
    5   Austria     Vienna     1805681
    6   Romania  Bucharest     1803425
    7   Germany    Hamburg     1760433
    8   Hungary   Budapest     1754000
    9    Poland     Warsaw     1740119
    10    Spain  Barcelona     1602386
    11  Germany     Munich     1493900
    12    Italy      Milan     1350680
    [13 rows x 3 columns]

We can see that an index (0,1,2, ...) has been automatically assigned to the DataFrame. We can also assign a custom index to the DataFrame object:

    ordinals = ["first", "second", "third", "fourth",
                "fifth", "sixth", "seventh", "eigth",
                "ninth", "tenth", "eleventh", "twelvth",
                "thirteenth"]
    city_frame = pd.DataFrame(cities, index=ordinals)
    print(city_frame)

Output:

                country       name  population
    first       England     London     8615246
    second      Germany     Berlin     3562166
    third         Spain     Madrid     3165235
    fourth        Italy       Rome     2874038
    fifth        France      Paris     2273305
    sixth       Austria     Vienna     1805681
    seventh     Romania  Bucharest     1803425
    eigth       Germany    Hamburg     1760433
    ninth       Hungary   Budapest     1754000
    tenth        Poland     Warsaw     1740119
    eleventh      Spain  Barcelona     1602386
    twelvth     Germany     Munich     1493900
    thirteenth    Italy      Milan     1350680
    [13 rows x 3 columns]

We can also define or rearrange the order of the columns.

    city_frame = pd.DataFrame(cities,
                              columns=["name",
                                       "country",
                                       "population"],
                              index=ordinals)
    print(city_frame)

Output:

                     name  country  population
    first          London  England     8615246
    second         Berlin  Germany     3562166
    third          Madrid    Spain     3165235
    fourth           Rome    Italy     2874038
    fifth           Paris   France     2273305
    sixth          Vienna  Austria     1805681
    seventh     Bucharest  Romania     1803425
    eigth         Hamburg  Germany     1760433
    ninth        Budapest  Hungary     1754000
    tenth          Warsaw   Poland     1740119
    eleventh    Barcelona    Spain     1602386
    twelvth        Munich  Germany     1493900
    thirteenth      Milan    Italy     1350680
    [13 rows x 3 columns]

We can calculate the sum of all the columns of a DataFrame or the sum of certain columns:

    city_frame.sum()

Output:

    name          LondonBerlinMadridRomeParisViennaBucharestHamb...
    country       EnglandGermanySpainItalyFranceAustriaRomaniaGe...
    population                                             33800614
    dtype: object

    city_frame["population"].sum()

Output:

    33800614

We can use "cumsum" to calculate the cumulative sum:

    x = city_frame["population"].cumsum()
    print(x)

Output:

    first          8615246
    second        12177412
    third         15342647
    fourth        18216685
    fifth         20489990
    sixth         22295671
    seventh       24099096
    eigth         25859529
    ninth         27613529
    tenth         29353648
    eleventh      30956034
    twelvth       32449934
    thirteenth    33800614
    Name: population, dtype: int64

x is a Pandas Series. We can reassign it to the population column:

    city_frame["population"] = x
    print(city_frame)

Output:

                     name  country  population
    first          London  England     8615246
    second         Berlin  Germany    12177412
    third          Madrid    Spain    15342647
    fourth           Rome    Italy    18216685
    fifth           Paris   France    20489990
    sixth          Vienna  Austria    22295671
    seventh     Bucharest  Romania    24099096
    eigth         Hamburg  Germany    25859529
    ninth        Budapest  Hungary    27613529
    tenth          Warsaw   Poland    29353648
    eleventh    Barcelona    Spain    30956034
    twelvth        Munich  Germany    32449934
    thirteenth      Milan    Italy    33800614
    [13 rows x 3 columns]

We can also include a column name which is not contained in the dictionary. In this case, all the values of this column will be set to NaN:

    city_frame = pd.DataFrame(cities,
                              columns=["name",
                                       "country",
                                       "area",
                                       "population"],
                              index=ordinals)
    print(city_frame)

Output:

                     name  country area  population
    first          London  England  NaN     8615246
    second         Berlin  Germany  NaN     3562166
    third          Madrid    Spain  NaN     3165235
    fourth           Rome    Italy  NaN     2874038
    fifth           Paris   France  NaN     2273305
    sixth          Vienna  Austria  NaN     1805681
    seventh     Bucharest  Romania  NaN     1803425
    eigth         Hamburg  Germany  NaN     1760433
    ninth        Budapest  Hungary  NaN     1754000
    tenth          Warsaw   Poland  NaN     1740119
    eleventh    Barcelona    Spain  NaN     1602386
    twelvth        Munich  Germany  NaN     1493900
    thirteenth      Milan    Italy  NaN     1350680
    [13 rows x 4 columns]

There are two ways to access a column of a DataFrame. The result is in both cases a Series:

    # in a dictionary-like way:
    print(city_frame["population"])

Output:

    first         8615246
    second        3562166
    third         3165235
    fourth        2874038
    fifth         2273305
    sixth         1805681
    seventh       1803425
    eigth         1760433
    ninth         1754000
    tenth         1740119
    eleventh      1602386
    twelvth       1493900
    thirteenth    1350680
    Name: population, dtype: int64

    # as an attribute
    print(city_frame.population)

Output:

    first         8615246
    second        3562166
    third         3165235
    fourth        2874038
    fifth         2273305
    sixth         1805681
    seventh       1803425
    eigth         1760433
    ninth         1754000
    tenth         1740119
    eleventh      1602386
    twelvth       1493900
    thirteenth    1350680
    Name: population, dtype: int64

    print(type(city_frame.population))

Output:

    <class 'pandas.core.series.Series'>

    p = city_frame.population
    p["first"] = 9000000
    print(city_frame)

Output:

                     name  country area  population
    first          London  England  NaN     9000000
    second         Berlin  Germany  NaN     3562166
    third          Madrid    Spain  NaN     3165235
    fourth           Rome    Italy  NaN     2874038
    fifth           Paris   France  NaN     2273305
    sixth          Vienna  Austria  NaN     1805681
    seventh     Bucharest  Romania  NaN     1803425
    eigth         Hamburg  Germany  NaN     1760433
    ninth        Budapest  Hungary  NaN     1754000
    tenth          Warsaw   Poland  NaN     1740119
    eleventh    Barcelona    Spain  NaN     1602386
    twelvth        Munich  Germany  NaN     1493900
    thirteenth      Milan    Italy  NaN     1350680
    [13 rows x 4 columns]

From the previous example, we can see that we have not copied the population column. "p" is a view on the data of city_frame.

We can also access the rows directly. We access the info of the fourth city in the following way:

    city_frame.ix['fourth']

Output:

    name             Rome
    country         Italy
    area              NaN
    population    2874038
    Name: fourth, dtype: object

The column area is still not defined. We can set all elements of the column to the same value:

    city_frame["area"] = 1572
    print(city_frame)

Output:

                     name  country  area  population
    first          London  England  1572     9000000
    second         Berlin  Germany  1572     3562166
    third          Madrid    Spain  1572     3165235
    fourth           Rome    Italy  1572     2874038
    fifth           Paris   France  1572     2273305
    sixth          Vienna  Austria  1572     1805681
    seventh     Bucharest  Romania  1572     1803425
    eigth         Hamburg  Germany  1572     1760433
    ninth        Budapest  Hungary  1572     1754000
    tenth          Warsaw   Poland  1572     1740119
    eleventh    Barcelona    Spain  1572     1602386
    twelvth        Munich  Germany  1572     1493900
    thirteenth      Milan    Italy  1572     1350680
    [13 rows x 4 columns]

In this case, it will be definitely better to assign the exact area to the cities. The list with the area values needs to have the same length as the number of rows in our DataFrame.

    # area in square km:
    area = [1572, 891.85, 605.77, 1285,
            105.4, 414.6, 228, 755,
            525.2, 517, 101.9, 310.4,
            181.8]
    city_frame["area"] = area
    print(city_frame)

Output:

                     name  country     area  population
    first          London  England  1572.00     9000000
    second         Berlin  Germany   891.85     3562166
    third          Madrid    Spain   605.77     3165235
    fourth           Rome    Italy  1285.00     2874038
    fifth           Paris   France   105.40     2273305
    sixth          Vienna  Austria   414.60     1805681
    seventh     Bucharest  Romania   228.00     1803425
    eigth         Hamburg  Germany   755.00     1760433
    ninth        Budapest  Hungary   525.20     1754000
    tenth          Warsaw   Poland   517.00     1740119
    eleventh    Barcelona    Spain   101.90     1602386
    twelvth        Munich  Germany   310.40     1493900
    thirteenth      Milan    Italy   181.80     1350680
    [13 rows x 4 columns]

Let's sort our DataFrame according to the city area:

    city_frame = city_frame.sort(columns="area", ascending=False)
    print(city_frame)

Output:

                     name  country     area  population
    first          London  England  1572.00     9000000
    fourth           Rome    Italy  1285.00     2874038
    second         Berlin  Germany   891.85     3562166
    eigth         Hamburg  Germany   755.00     1760433
    third          Madrid    Spain   605.77     3165235
    ninth        Budapest  Hungary   525.20     1754000
    tenth          Warsaw   Poland   517.00     1740119
    sixth          Vienna  Austria   414.60     1805681
    twelvth        Munich  Germany   310.40     1493900
    seventh     Bucharest  Romania   228.00     1803425
    thirteenth      Milan    Italy   181.80     1350680
    fifth           Paris   France   105.40     2273305
    eleventh    Barcelona    Spain   101.90     1602386
    [13 rows x 4 columns]

Let's assume, we have only the areas of London, Hamburg and Milan. The areas are in a series with the correct indices. We can assign this series as well:

    city_frame = pd.DataFrame(cities,
                              columns=["name",
                                       "country",
                                       "area",
                                       "population"],
                              index=ordinals)
    some_areas = pd.Series([1572, 755, 181.8],
                        index=['first', 'eigth', 'thirteenth'])
    city_frame['area'] = some_areas
    print(city_frame)

Output:

                     name  country    area  population
    first          London  England  1572.0     8615246
    second         Berlin  Germany     NaN     3562166
    third          Madrid    Spain     NaN     3165235
    fourth           Rome    Italy     NaN     2874038
    fifth           Paris   France     NaN     2273305
    sixth          Vienna  Austria     NaN     1805681
    seventh     Bucharest  Romania     NaN     1803425
    eigth         Hamburg  Germany   755.0     1760433
    ninth        Budapest  Hungary     NaN     1754000
    tenth          Warsaw   Poland     NaN     1740119
    eleventh    Barcelona    Spain     NaN     1602386
    twelvth        Munich  Germany     NaN     1493900
    thirteenth      Milan    Italy   181.8     1350680
    [13 rows x 4 columns]

A nested dictionary of dicts can be passed to a DataFrame as well. The indices of the outer dictionary are taken as the the columns and the inner keys. i.e. the keys of the nested dictionaries, are used as the row indices:

    growth = {"Switzerland": {"2010": 3.0, "2011": 1.8, "2012": 1.1, "2013": 1.9},
              "Germany": {"2010": 4.1, "2011": 3.6, "2012":	0.4, "2013": 0.1},
              "France": {"2010":2.0,  "2011":2.1, "2012": 0.3, "2013": 0.3},
              "Greece": {"2010":-5.4, "2011":-8.9, "2012":-6.6, "2013":	-3.3},
              "Italy": {"2010":1.7, "2011":	0.6, "2012":-2.3, "2013":-1.9}
              }

    growth_frame = pd.DataFrame(growth)
    growth_frame

Output:

| France | Germany | Greece | Italy | Switzerland
--- | --- | --- | --- | --- | ---
2010 | 2.0 | 4.1 | -5.4 | 1.7 | 3.0
2011 | 2.1 | 3.6 | -8.9 | 0.6 | 1.8
2012 | 0.3 | 0.4 | -6.6 | -2.3 | 1.1
2013 | 0.3 | 0.1 | -3.3 | -1.9 | 1.9

4 rows × 5 columns

You like to have the years in the columns and the countries in the rows? No problem, you can transpose the data:

    growth_frame.T

Output:

| 2010 | 2011 | 2012 | 2013
--- | --- | --- | --- | ---
France | 2.0 | 2.1 | 0.3 | 0.3
Germany | 4.1 | 3.6 | 0.4 | 0.1
Greece | -5.4 | -8.9 | -6.6 | -3.3
Italy | 1.7 | 0.6 | -2.3 | -1.9
Switzerland | 3.0 | 1.8 | 1.1 | 1.9

5 rows × 4 columns

    growth_frame = pd.DataFrame(growth)
    growth_frame.reindex(["2013", "2012", "2011", "2010"])

Output:

| France | Germany | Greece | Italy | Switzerland
--- | --- | --- | --- | --- | ---
2013 | 0.3 | 0.1 | -3.3 | -1.9 | 1.9
2012 | 0.3 | 0.4 | -6.6 | -2.3 | 1.1
2011 | 2.1 | 3.6 | -8.9 | 0.6 | 1.8
2010 | 2.0 | 4.1 | -5.4 | 1.7 | 3.0

4 rows × 5 columns

Filling a DataFrame with random values:

    df = pd.DataFrame(np.random.randn(10, 5),
    columns=['a', 'b', 'c', 'd', 'e'])
    df

Output:

| a | b | c | d | e
--- | --- | --- | --- | --- | ---
0 | 0.120591 | -0.919881 | 0.215054 | -0.596307 | -0.482016
1 | -0.101367 | -0.327798 | -0.193926 | -1.019803 | -0.409547
2 | 0.070791 | 0.049981 | 0.808494 | 1.024858 | 0.437878
3 | 1.198251 | -0.153978 | 1.742216 | -2.068733 | -1.192922
4 | -0.451963 | -0.717011 | 1.399723 | -1.175493 | 1.396906
5 | -1.991932 | 1.816324 | -0.599968 | 0.124527 | 0.286920
6 | -0.068553 | -0.352935 | 1.618726 | -1.102455 | -0.093867
7 | 1.050722 | 1.293406 | 0.297897 | 1.930885 | 1.003767
8 | 0.149615 | 0.445557 | -0.552403 | -0.256427 | -0.600788
9 | 1.306989 | 0.742940 | 0.734419 | -0.756281 | 0.654875

10 rows × 5 columns

We want to read in a csv file with the population data of all countries (July 2014). The delimiter of the file a a space and commas are used to separate groups of thousands in the numbers:

    pop = pd.read_csv("countries_population.csv",
                      quotechar="'",
                      sep=" ",
                      thousands=",")
    pop

Output:

| China | 1,355,692,576
--- | --- | ---
0 | India | 1236344631
1 | European Union | 511434812
2 | United States | 318892103
3 | Indonesia | 253609643
4 | Brazil | 202656788
5 | Pakistan | 196174380
6 | Nigeria | 177155754
7 | Bangladesh | 166280712
8 | Russia | 142470272
9 | Japan | 127103388
10 | Mexico | 120286655
11 | Philippines | 107668231
12 | Ethiopia | 96633458
13 | Vietnam | 93421835
14 | Egypt | 86895099
15 | Turkey | 81619392
16 | Germany | 80996685
17 | Iran | 80840713
18 | Congo, Democratic Republic of the | 77433744
19 | Thailand | 67741401
20 | France | 66259012
21 | United Kingdom | 63742977
22 | Italy | 61680122
23 | Burma | 55746253
24 | Tanzania | 49639138
25 | Korea, South | 49039986
26 | South Africa | 48375645
27 | Spain | 47737941
28 | Colombia | 46245297
29 | Kenya | 45010056
30 | Ukraine | 44291413
31 | Argentina | 43024374
32 | Algeria | 38813722
33 | Poland | 38346279
34 | Uganda | 35918915
35 | Sudan | 35482233
36 | Canada | 34834841
37 | Morocco | 32987206
38 | Iraq | 32585692
39 | Afghanistan | 31822848
40 | Nepal | 30986975
41 | Peru | 30147935
42 | Malaysia | 30073353
43 | Uzbekistan | 28929716
44 | Venezuela | 28868486
45 | Saudi Arabia | 27345986
46 | Yemen | 26052966
47 | Ghana | 25758108
48 | Korea, North | 24851627
49 | Mozambique | 24692144
50 | Taiwan | 23359928
51 | Madagascar | 23201926
52 | Cameroon | 23130708
53 | Cote dIvoire' | 22848945
54 | Australia | 22507617
55 | Sri Lanka | 21866445
56 | Romania | 21729871
57 | Angola | 19088106
58 | Burkina Faso | 18365123
59 | Syria | 17951639
| ... | ...

237 rows × 2 columns

Previous Chapter: Image Processing Techniques
Next Chapter: Linear Combinations