pandas: Reshaping and Grouping

More about processing one table.

long-and-wide table conversion

divide a table into groups

More practice examples

source: https://link.springer.com/chapter/10.1007/978-3-030-76394-7_3

stack and unstack switches the information by changing the index and columns but the contents will remain unchanged. In other words, only the way they are presented changes (from a 6-by-2 table to a 3-by-4 table but the numbers do not change).

We now introduce methods that interchange index/column and table contents.

We say a table is (relatively) long when the table contains more rows than we want. A table is (relatively) wide when the table contains more columns than we want.

We pivot long tables to wider ones

We melt wide tables to longer ones

Let’s make a long table for pivoting.

import numpy as np
import pandas as pd

["2020-01-03","2020-01-04","2020-01-05"] * 4
["A"] * 3 + ["B"] * 3 + ["C"] * 3 + ["D"] * 3

data = {
   "score": [90, 91, 92, 80, 81, 82, 60, 61, 62, 50, 51, 52],
   "grades": ["A"] * 3 + ["B"] * 3 + ["C"] * 3 + ["D"] * 3,
   "date": pd.to_datetime(["2020-01-03","2020-01-04","2020-01-05"] * 4)
}
df = pd.DataFrame(data)

We use .pivot_table to convert a long table to wide.

The table we want is like:

pivoted = df.pivot_table(index="date", # whose values will be indices?
                         columns="grades", # whose values will be colnames?
                         values="score")
pivoted

In-class exercise 1

Pivot the this table:

data = {
   "value": range(13),
   "variable": ["A"] * 4 + ["B"] * 3 + ["C"] * 3 + ["D"] * 3,
   "date": pd.to_datetime(["2020-01-03"] * 5 + ["2020-01-04"] * 4 + ["2020-01-05"] * 4)
}
df = pd.DataFrame(data)

The .pivot_table method is more powerful than simply change the locations of data. Its aggfunc parameter can summarize information for each group.

pivoted = df.pivot_table(index="date",
                         columns="variable",
                         values="value",
                         aggfunc="sum") # aggregate

In-class exercise 1

Read in the MallSales.csv data.

Pivot the table to compute the sum of Sales by Year and Category.

What issues do you encounter?

Pivot the table to compute the average Sales by Year.

Pivot the table to see the average rating by Product.

For string operations, use the .str accessor; convert values first with .astype("string") if needed.

To remove a trailing character, use .str.rstrip(character); to remove exactly one final character, use .str[:-1]

Pivot the table to show both sum and mean of Sales by Year and Category.

Nest Product under Category and redo question 4.

We use .melt to convert a wide table to long.

When a data frame contains too many columns, it's considered "messy" because useful information becomes harder to find. For example:

messydata1 = pd.read_csv("messydata1.csv")
messydata1

We melt it with

Then we can make smaller and neater DataFrames:

wide_messy = messydata1.melt(id_vars=["Name"])

ids = wide_messy.loc[lambda df: df['variable'] == 'ID', :]
ages = wide_messy.loc[lambda df: df['variable'] == 'Age', :]
genders = wide_messy.loc[lambda df: df['variable'] == 'Gender', :]
...

Another example is about French fries:

We are looking for a table like:

ffm = pd.read_csv("french_fries.dat", delimiter=' ')

ffm.melt(id_vars=['time', 'treatment', 'subject', 'rep'],
         var_name='scale',
         value_name='score')

In-class exercise 2

Read in the cake.dat data and melt it to a long table:

source: https://towardsdatascience.com/pandas-groupby-vs-sql-group-by-39ccd7d2b779/

The last data frame operation is to separate the rows into different groups. For example, students from grade 1 form a group, students from grade 2 form a group, etc. This uses the .groupby method.

Let's use the iris data for an example and put different flowers into groups according to their type.

irisdata = pd.read_csv("./iris/iris.data",
                       names=["sepal_length", "sepal_width",
                              "petal_length", "petal_width",
                              "type"], header=None)
iris_group = irisdata.groupby("type")
iris_group.size()

Note that these groups don't mean we have multiple new data frames. It simply records which rows are in which group. And we don't directly use this information. But there are many methods that can be applied to each group. Try them out and summarise your findings.

We’ll not introduce these methods in detail, but please read the documents and do the exercises.

dropna

drop_duplicates

reset_index

In-class exercise 3

An example: We want to count how many people selected two courses from different channels (online or onsite) from the course_form.csv file. We rely on the .count method:

In this example, the .count is applied to each group as if they were different data frames. And then the counts are put back into a new data frame.

course_form = pd.read_csv("./course_form.csv")
course_form.groupby(["class", "form"]).count()

In-class exercise 4

Now, please

read in the animal.csv data and find the fastest animal in each class using .first.

read in the animal.csv data and find the fastest _and the second fastest_ animal in each class using .nth.

read in the animal.csv data and find the index of the fastest animal in each class using .idxmax.

read in the animal.csv data and get the bird group using the get_group method.

read in the race.csv file containing information of a competition. The column id shows the athletes' id and the column `time` records their times spent for several tries. Find the average time for each athlete.

The Bureau of Statistics of our country provides rich information about the country’s economy, especially macroeconomic balances. For example, we can easily find GPD data by province and by sector.

Previously, we have seen the consumption and income data. However, the data we used then were already processed. Now let’s turn to the raw data.

In-class exercise 6

We will need four files: consumption.csv, cpi.csv, gpd.csv, and population.csv

1. Please read in these data and make necessary changes so we can have one data frame with all the information.

Hint: when setting new columns, you need to make sure that the order is correct.

2. Please compute the total savings of each province in each year and store the DataFrame in the “long” format.

Hint: you can also store them with the “wide” format but it would cause trouble.

In-class exercise 6 (Cont’d)

3. We know that the price of products changes every year, as indicated by the consumer price index (CPI). The file cpi.csv contains the price index relative to 2013 (in 2013 Yuan) over the years. Please adjust the total savings of each province by the price index. We call the adjusted savings “real savings.”

4. What’s the average real savings of each province across the years?

5. Which province saves the most on average?

⚠This exercise is more difficult!

The file stock_utf.csv contains daily information of 10 listed stocks on the Chinese stock market from 1990 to 2000. The data is a subsample of the whole market but can serve as an example.

The file contains 75 variables (columns) but we cannot use all of them.

In-class exercise 7

What are the variables?

We focus on PrevClPr and Clpr. Please create a new DataFrame to only include relevant columns.

We have seen how to construct momentum using monthly return data. Please create returns from the new DataFrame. It’s defined as

$\text{𝐶𝑙𝑝𝑟}\text{/}\text{𝑃𝑟𝑒𝑣𝐶𝑙𝑃𝑟}$

Please compute the cumulative return every 5 days for each stock.

The file Chiense_market.csv contains all Chinese listed firms but due to privacy concerns, we have added tremendous noise to the original data. It contains four columns: stkcd is the stock code; trdmnt indicates the trading month; mclsprc shows the closing price; msmvttl stands for the total market values.

We need to compute the weighted (by total market value) average of closing price of all the firms in each month. This is a weighted average price level, not a portfolio return. A return analysis must use changes in prices and appropriately timed portfolio weights.

We use Chinese_market.csv again. This time, we have another data FT50.xlsx, which contains the composite of Financial Times 50 index.

Now, please take a subset of the FT50 composites from Chinese_market and compute the weighted average of these firms.

Now, let’s compute by far the most complex returns: the portfolio returns. We need first to construct some portfolios. To do so, let’s first divide the stock universe into ten parts. According to the log of the market value of stocks. The largest 10% of stocks are put together, and we compute the simple average return of them (called a portfolio and portfolio returns). The following 10% to 20% stocks are put together, and we compute their simple average returns, etc.

Please construct ten portfolios according to the market value in each month and compute the simple average of the price of each portfolio.

Please find the gross return of each stock in each month.

Please construct ten portfolios according to the log of market value in each month and compute the simple average of return of each portfolio.

Please find the average return of each portfolio series.