pandas: One Table

Data organization

pandas.DataFrame = 2D numpy array + row info + col info

Fetching --- taking out rows of a table

Fetching --- taking out columns of a table

Summarizing information in a table

Sort to prioritize rows

Aggregation to see a common trend

MultiIndexed DataFrame

The loc and iloc property, making new columns

pic source: https://zebrabi.com/how-to-pull-information-from-another-sheet-in-excel/

We now officially start our journey to work with datasets. A data set is where data are stored. Because most datasets appear as tables, we use the terms dataset and data table interchangeably. The pandas module specializes in analyzing data tables.

Data tables are the most important carrier of information. But the information doesn't always speak for itself. So we need to analyze the data to reveal the information.

By looking at one data table, you’ll be able to explain

how a data table is organized (what the rows and columns of that table represent)

how to apply some basic manipulations (get the information from the table).

These manipulations help us look at the data from different perspectives by changing the format of the table.

They help us find the specific character of a specific person by locating it.

These help us divide the data table into smaller ones called groups and summarize information in each group.

In economics, we often need to work with data. From data, we can gain insights about how the economic world operates. We can also verify our economic theory with data. Such data include a country's GDP, a person's consumption details, the price of an item, etc. As computing power improves, economic data is growing ever larger. It's now common to see data sets of several GB or even TB.

In computers, we often store data in a (real) table. A table is mostly a 2D matrix. It contains rows (each horizontal element) and columns (each vertical element). For example:

GDP

The common principals include:

each row represents an observational unit (a person, a country, etc.: an entity)

each column exhibits a perspective or character of the rows (the persons' height, the countries' GDP, etc.)

the top row is reserved for the name of the columns and is called the header. It's not part of the "contents" of the table

the first column is reserved for the index (name) of the rows. It's not part of the "contents."

A pandas Series is a one-dimensional labeled array.

• Each element has an index label.
• The Series has one dtype, but that dtype can be object, which may hold different Python object types.
• Values can be selected by label or position using the appropriate indexing operation.

A DataFrame brings multiple Series together as columns sharing an index.

The type we use to store a data table is called pandas.DataFrame.

It's basically a matrix with index and columns:

index contains the name of each row

columns contains the name of each column

For example, in the following table, the boldfaced numbers on the leftmost side are the index. The boldfaced letters on top are the columns.

import numpy as np
import pandas as pd

df1 = pd.DataFrame(
    np.random.randn(6, 4), # N(0,1)
    index=range(6),
    columns=list("ABCD")
)

pic source: https://keydifferences.com/difference-between-rows-and-columns.html

We can look up (take out) the rows and columns with their names. But the indexing differs:

Although look similar, rows and columns are completely different. We can check their types. The type “pandas.core.series.Series” means that col1 is a Series.

A Series is a 1-D object to store information of one aspect.

pandas.DataFrame is a combination of multiple Series.

row1 = df1[0:1]
col1 = df1['A']

The DataFrame provides us with structured information. For example,

You’ll see that 1 and 2 do not appear as integers. Why?

We can get the analytical information of each column with the .describe method and the storage information with the .dtypes attribute.

df2 = pd.DataFrame(
    [[1, 2], [1.1, 2.1]],
    index=range(2),
    columns=['col1', 'col2']
)

We can look at a table from different angles. Although the information will not change, we can highlight the information in which we are more interested.

The first method of highlighting is to put the most important information on top of the table.

This is done with the .sort_values or the .sort_index methods.

When we want to take a bird-eye view of the table, we summarize information. That is, we do not care about individual data record, but want to know how the overall data look like.

Such summarizing is done by the .agg method.

An example for sorting.

race_score = pd.DataFrame(
    [['Alice', 15.6], ['Bob', 17.3], ['Charlie', 14.5]],
)

race_score.sort_values(1, ascending=True)

race_score.index = [1002, 1001, 1003]

race_score.sort_index()

An example for aggregation

student_name = ['Alice', 'Bob', 'Charlie', 'David', 'Eva']
langauge_score = [99, 100, 35, 60, 71]
math_score = [25, 89, 36, 40, 91]
score = pd.DataFrame(
    np.array([langauge_score, math_score]).T,
    index=student_name,
    columns=['language', 'math']
)

score.agg("mean", axis=0)

score.agg("sum", axis=1)

pandas provides a much simpler API for reading the csv file: the pd.read_csv function.

irisdata = pd.read_csv("./iris/iris.data", header=None)

In-class exercise 1

1. Read in the data and take a quick look. What are the column names?

2. Change column names to "sepal_length," "sepal_width," "petal_length," "petal_width," and "type. "

3. Remove the last column "type" (hint: there's a method called drop).

4. Compute (aggregate) the mean and standard deviation of each of the first four columns.

5. Subtract the mean from each corresponding column and then divide by its standard deviation. The new data frame should be known as "irisdata_normed".

6. Check the mean and standard deviation of irisdata_normed.

7. Write the data to a .csv file (.to_csv).

pic source: https://medium.com/data-science/reshaping-a-dataframe-with-pandas-stack-and-unstack-925dc9ce1289

Section 3.2.1 is about changing the way the data contents appear. But we kept the rows as rows and columns as columns (there was no transposition or exchange between rows and columns).

The second way of highlighting the data in which we are interested is by swapping the rows and columns.

This is also an important way to organizing the data without changing the values of the contents (compared to aggregation).

This can be done with the .stack and .unstack methods.

The index in a data frame shows us the information on the identity of a person, but it is usual that we have multiple levels of identity. For example, if in class 1, grade 1, there is an Alice, it is equally possible that in class 1, grade 3, there is another Alice. So we need multiple indices to help identify one person.

Let's assume we select a student from each class and ask about their height and weight.

picture sources: https://depositphotos.com/vectors/little-girl-cartoon.html

https://www.vecteezy.com/free-png/cartoon-girl

pic source: https://www.linkedin.com/posts/govinda-bobade_choosing-between-loc-and-iloc-when-activity-7098315476195315712-no7b/

The easiest way to take out rows and columns is by the square brackets. However, these methods have limitations.

We have to use different indexing for rows and columns

A list passed directly to df[...] selects columns, not row labels

Use .loc or .iloc to express row and column slices explicitly

Multi-step selection (advanced selection) is prone to errors

Use .loc for label-based selection and .iloc for integer-position-based selection. Both select rows and columns; neither name stands for “label of column.”

In-class exercise 2

1. The files income.csv and consumption.csv contain income per capita (means “per person”) and consumption per capita by province, along with the population.

2. Please make new columns to find the total income and total consumption of each province in each year and then save the files with new names (income_total.csv and comsumption_total.csv). What are the units?

3. How do you compute the savings ratio (savings defined as the amount that is not used for consumption)?

In-class exercise 3

Another useful operation with one data set is lead/lag. Let’s consider the production function:

${\text{𝑌}}_{\text{𝑡}}\text{=}{\text{𝐴}}_{\text{𝑡}}{\text{𝐾}}_{\text{𝑡}}^{\text{𝛼}}{\text{𝐿}}_{\text{𝑡}}^{\text{1−}\text{𝛼}}$

Here ${\text{𝐾}}_{\text{𝑡}}$is the capital that is used to generate the output ${\text{𝑌}}_{\text{𝑡}}$. In accounting, you have learned that firms will disclose financial statements at the end of the fiscal year. The output is the total output over the year, the labor is the number of employees at the end of the year. The capital is also at the end of the year. This generates misalignment between the input and output. We use the shift function to align them.

In terms of labor, since we are not sure which one gives the most accurate measure, we typically take the average of labor of the current year and the previous year.

In-class exercise 3 (Cont’d)

The file cdprod.xlsx contains the capital, labor, and output information collected from a fake capital-intensive firm from 2000 to 2021. But the output of 2000 is missing.

1. Please write a function to find the return-to-scale on capital without correcting for the alignment.

Hint: you can write a for loop to see which value of $\text{𝛼}$ produces the output that is closest to the observed.

2. Please correctly align capital and labor and then redo step 1.

In-class exercise 4

In the theory of asset pricing, an important idea is called momentum, which gives clues on how the stock returns in the future using the past return information. By definition, momentum is

${\text{𝑚}}_{\text{𝑡}}\text{=}\prod _{\text{𝑡}\text{=2}}^{\text{12}}{\text{𝑟}}_{\text{𝑡}\text{−}\text{𝑗}}$

Here ${\text{𝑟}}_{\text{𝑡}}$ is the return at time $\text{𝑡}$.

The file return1.csv contains information about the returns of one firm. Please compute its momentum when data are available.

Hint: you probably will find it useful to look at the rolling method.

In-class exercise 4 (Cont’s)

The file return4.csv contains return information of four firms.

Look at the data and find the cross-sectional average returns. What is the average of the cross-sectional average returns?

Please read in the data and check if any necessary changes are needed.

Please compute the momentum of these four firms when data are available.

In-class exercise 5

The file country_sector.xlsx contains sector-level GDP of four countries. Please find the average per country and the average per sector.