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]})

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({
    "Ticker": ["AAPL", "MSFT", "NVDA", "AMZN"],     # 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],
})

print(df)
# ->   Ticker   Close  Shares
# -> 0   AAPL  185.40      10
# -> 1   MSFT  410.20       5
# -> 2   NVDA  130.75      20
# -> 3   AMZN  178.30       8

  Ticker   Close  Shares
0   AAPL  185.40      10
1   MSFT  410.20       5
2   NVDA  130.75      20
3   AMZN  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='str')
print(df.index)         # -> RangeIndex(start=0, stop=4, step=1)

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

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

print(df.dtypes)
# -> Ticker        str
# -> Close     float64
# -> Shares      int64
# -> dtype: object

Ticker        str
Close     float64
Shares      int64
dtype: object

Ticker holds text, which pandas 3 reports as str. 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"]

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

<class 'pandas.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"]])
# ->   Ticker   Close
# -> 0   AAPL  185.40
# -> 1   MSFT  410.20
# -> 2   NVDA  130.75
# -> 3   AMZN  178.30

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

  Ticker   Close
0   AAPL  185.40
1   MSFT  410.20
2   NVDA  130.75
3   AMZN  178.30
<class 'pandas.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"]

print(df)
# ->   Ticker   Close  Shares   Value
# -> 0   AAPL  185.40      10  1854.0
# -> 1   MSFT  410.20       5  2051.0
# -> 2   NVDA  130.75      20  2615.0
# -> 3   AMZN  178.30       8  1426.4

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

  Ticker   Close  Shares   Value
0   AAPL  185.40      10  1854.0
1   MSFT  410.20       5  2051.0
2   NVDA  130.75      20  2615.0
3   AMZN  178.30       8  1426.4
(4, 4)

MSFT 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 NVDA.

print(df.sort_values("Value", ascending=False))
# ->   Ticker   Close  Shares   Value
# -> 2   NVDA  130.75      20  2615.0
# -> 1   MSFT  410.20       5  2051.0
# -> 0   AAPL  185.40      10  1854.0
# -> 3   AMZN  178.30       8  1426.4

  Ticker   Close  Shares   Value
2   NVDA  130.75      20  2615.0
1   MSFT  410.20       5  2051.0
0   AAPL  185.40      10  1854.0
3   AMZN  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

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

print(df[mask])
# ->   Ticker   Close  Shares   Value
# -> 1   MSFT  410.20       5  2051.0
# -> 2   NVDA  130.75      20  2615.0

0    False
1     True
2     True
3    False
Name: Value, dtype: bool
  Ticker   Close  Shares   Value
1   MSFT  410.20       5  2051.0
2   NVDA  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()
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   AAPL  185.40      10  1854.0  0.233313
# -> 1   MSFT  410.20       5  2051.0  0.258104
# -> 2   NVDA  130.75      20  2615.0  0.329080
# -> 3   AMZN  178.30       8  1426.4  0.179503

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

7946.4
  Ticker   Close  Shares   Value    Weight
0   AAPL  185.40      10  1854.0  0.233313
1   MSFT  410.20       5  2051.0  0.258104
2   NVDA  130.75      20  2615.0  0.329080
3   AMZN  178.30       8  1426.4  0.179503
1.0

NVDA 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]

print(small[["Ticker", "Weight"]])
# ->   Ticker    Weight
# -> 0   AAPL  0.233313
# -> 3   AMZN  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.