What is a DataFrame?

Build a table from a dictionary of lists, select columns from it, add a computed column, sort it, and filter its rows.

A DataFrame is a table: several Series stacked side by side, all sharing one index. Row 2 means the same row in every column, so a ticker, its price and its share count stay together.

In this lesson I build a holdings table from a dictionary of lists, add a position value, and turn those values into portfolio weights.

Step 1. A table from a dictionary of lists

Each key becomes a column name and each list becomes that column. The lists must be the same length, because they line up row by row. The smallest table that works is two keys and two short lists.

import pandas as pd

nums = pd.DataFrame({"a": [1, 2, 3], "b": [4, 5, 6]})    # two keys, two equal length lists

print(nums)
# ->    a  b
# -> 0  1  4
# -> 1  2  5
# -> 2  3  6

print(nums.shape)   # -> (3, 2)    <- 3 rows, 2 columns

The same call builds a holdings table, with a text column and two number columns.

df = pd.DataFrame({                                 # one dictionary in, one table out
    "Ticker": ["AAA", "CCC", "DDD", "EEE"],     # each key is a column name
    "Close":  [185.40, 410.20, 130.75, 178.30],     # each list is one column
    "Shares": [10, 5, 20, 8],                       # a third list, same length again
})

print(df)
# ->   Ticker   Close  Shares
# -> 0    AAA  185.40      10
# -> 1    CCC  410.20       5
# -> 2    DDD  130.75      20
# -> 3    EEE  178.30       8

  Ticker   Close  Shares
0    AAA  185.40      10
1    CCC  410.20       5
2    DDD  130.75      20
3    EEE  178.30       8

The numbers down the left are the index. I did not supply one, so pandas numbered the rows 0 to 3.

Step 2. What the table knows about itself

.shape, .columns, .index and .dtypes are facts about the DataFrame, not data in it. None of them take brackets. .shape reads as (rows, columns).

print(df.shape)         # -> (4, 3)    <- 4 rows, 3 columns
print(len(df))          # -> 4         <- len() counts rows
print(df.columns)       # -> Index(['Ticker', 'Close', 'Shares'], dtype='object')
print(df.index)         # -> RangeIndex(start=0, stop=4, step=1)

(4, 3)
4
Index(['Ticker', 'Close', 'Shares'], dtype='object')
RangeIndex(start=0, stop=4, step=1)

Every column carries one type, and .dtypes lists them.

print(df.dtypes)    # one type per column, in column order
# -> Ticker     object
# -> Close     float64
# -> Shares      int64
# -> dtype: object

Ticker     object
Close     float64
Shares      int64
dtype: object

Ticker holds text, which pandas reports as object. Close holds decimals, float64. Shares holds whole numbers, int64.

Step 3. One column is a Series, two columns are a DataFrame

Square brackets with one name hand back that column on its own. That is a Series, the object from Lesson 14, and it carries the same index as the table it came from.

close = df["Close"]     # one name hands back that column alone

print(type(close))      # -> <class 'pandas.core.series.Series'>
print(close)
# -> 0    185.40
# -> 1    410.20
# -> 2    130.75
# -> 3    178.30
# -> Name: Close, dtype: float64

<class 'pandas.core.series.Series'>
0    185.40
1    410.20
2    130.75
3    178.30
Name: Close, dtype: float64

Square brackets with a list of names hand back a smaller table.

print(df[["Ticker", "Close"]])          # a list of names gives back a table
# ->   Ticker   Close
# -> 0    AAA  185.40
# -> 1    CCC  410.20
# -> 2    DDD  130.75
# -> 3    EEE  178.30

print(type(df[["Ticker", "Close"]]))    # -> <class 'pandas.core.frame.DataFrame'>

  Ticker   Close
0    AAA  185.40
1    CCC  410.20
2    DDD  130.75
3    EEE  178.30
<class 'pandas.core.frame.DataFrame'>

One name gives a Series, a list of names gives a DataFrame, even a list of one name.

Step 4. A column computed from two others

Close * Shares multiplies the two Series row by row, and assigning the result to a name that does not exist yet adds it as a new column on the right.

df["Value"] = df["Close"] * df["Shares"]    # multiplied row by row into a new column

print(df)
# ->   Ticker   Close  Shares   Value
# -> 0    AAA  185.40      10  1854.0
# -> 1    CCC  410.20       5  2051.0
# -> 2    DDD  130.75      20  2615.0
# -> 3    EEE  178.30       8  1426.4

print(df.shape)     # -> (4, 4)    <- one column wider than before

  Ticker   Close  Shares   Value
0    AAA  185.40      10  1854.0
1    CCC  410.20       5  2051.0
2    DDD  130.75      20  2615.0
3    EEE  178.30       8  1426.4
(4, 4)

CCC has the highest price and the fewest shares, and its position is still the second largest.

Step 5. Sort the rows and keep some of them

.sort_values reorders the rows on one column and returns a new table. The index travels with each row, so 2 is still DDD.

print(df.sort_values("Value", ascending=False))    # largest position first
# ->   Ticker   Close  Shares   Value
# -> 2    DDD  130.75      20  2615.0
# -> 1    CCC  410.20       5  2051.0
# -> 0    AAA  185.40      10  1854.0
# -> 3    EEE  178.30       8  1426.4

  Ticker   Close  Shares   Value
2    DDD  130.75      20  2615.0
1    CCC  410.20       5  2051.0
0    AAA  185.40      10  1854.0
3    EEE  178.30       8  1426.4

A comparison on a column gives one True or False per row. Feeding that back into square brackets keeps the True rows.

mask = df["Value"] > 2000    # one True or False per row

print(mask)
# -> 0    False
# -> 1     True
# -> 2     True
# -> 3    False
# -> Name: Value, dtype: bool

print(df[mask])              # square brackets keep the True rows
# ->   Ticker   Close  Shares   Value
# -> 1    CCC  410.20       5  2051.0
# -> 2    DDD  130.75      20  2615.0

0    False
1     True
2     True
3    False
Name: Value, dtype: bool
  Ticker   Close  Shares   Value
1    CCC  410.20       5  2051.0
2    DDD  130.75      20  2615.0

Written in one line it is df[df["Value"] > 2000], and it reads as “the rows of df where Value is above 2000”.

Step 6. Weights

Each position over the total gives the share of the portfolio in that holding.

total = df["Value"].sum()                # the whole portfolio in one number
df["Weight"] = df["Value"] / total       # each row divided by one number

print(round(total, 2))                   # -> 7946.4

print(df)
# ->   Ticker   Close  Shares   Value    Weight
# -> 0    AAA  185.40      10  1854.0  0.233313
# -> 1    CCC  410.20       5  2051.0  0.258104
# -> 2    DDD  130.75      20  2615.0  0.329080
# -> 3    EEE  178.30       8  1426.4  0.179503

print(df["Weight"].sum())                # -> 1.0

7946.4
  Ticker   Close  Shares   Value    Weight
0    AAA  185.40      10  1854.0  0.233313
1    CCC  410.20       5  2051.0  0.258104
2    DDD  130.75      20  2615.0  0.329080
3    EEE  178.30       8  1426.4  0.179503
1.0

DDD is the cheapest share here and the largest weight, at 32.9% of the portfolio.

Your turn

Using the table from Step 6, print the ticker and weight of every holding under 25% of the portfolio, and print how many rows that leaves.

Show answer

small = df[df["Weight"] < 0.25]    # rows under 25% of the portfolio

print(small[["Ticker", "Weight"]])
# ->   Ticker    Weight
# -> 0   AAA  0.233313
# -> 3   EEE  0.179503

print(small.shape)      # -> (2, 5)    <- 2 rows, still all 5 columns

Pairing by label, not by position

df["Close"] * df["Shares"] does not pair the two columns by position. It pairs them by index label, then multiplies. Both columns come from the same table, so the labels are identical and the result is what you expect.

print(df["Close"].index.equals(df.index))    # -> True

True

Two Series from different tables can carry different labels, and pandas will still align them by label rather than by position. Lesson 24 covers what happens when the labels do not match.