Create quantile slices from dataframe columns

Question

I have the following, working code:

import pandas as pd

hld_per = 12    # Holding period in months
quantiles = 10  # Number of bins/buckets; Deciles, use 10; Quartiles, use 4; etc.
permnos = pd.read_csv('Ranks.csv')
my_headers = list(permnos.columns)
total_cols = len(permnos.columns)

pntls = permnos.copy(deep=True)

# Compute the percentile of each element based on position (each column in permnos is sorted best-to-worst)
for i in range(total_cols):
    for j in range(pntls.iloc[:,i].count()):
        pntls.iloc[j,i] = (j+1)/pntls.iloc[:,i].count()

# Create slices by column based on pntls values and choice of quantiles.
# Write resultant portfolios to files (# of portfolios = # in quantile).
for i in range(quantiles):
    ports = []
    for j in range(total_cols-(hld_per-1)):
        permlist = []
        for k in range(hld_per):
            slc = (pntls.iloc[:,j+k] > i/quantiles) & (pntls.iloc[:,j+k] <=(i+1)/quantiles)
            col_slice = permnos[slc].iloc[:,j+k].tolist()
            permlist += col_slice
        ports.append(permlist)

    matrix = pd.DataFrame(ports).T
    matrix.columns = my_headers[0:len(matrix.columns)]
    matrix.to_csv("portstst2_" + str(i+1) + ".csv", sep=',', index=False, header=True)

I built this code as a solution to a question I posted on stackoverflow in this thread. While it works, it is not very fast. Additionally, as someone new to Python, I imagine there are much better ways to write this.

The purpose of this code is to read in a csv file which is organized as follows: Column headers are dates in the format YYYYMMDD, descending monthly moving left to right (e.g. the first column is 20131231 followed by 20131130,etc). The data in the rows are identifiers which are sorted from "best" to "worst" in a separate program. The rows are of varying lengths, and the number of rows in each column determines an identifier's rank/percentile/bucket for the month/column.

The data in each column is broken into quantiles using the values calculated in the pntls dataframe. An example: If hld_per is 3 and quantiles is 4, the code takes the top 25% of identifiers in column 0 of permnos and places them in column 0 of the list ports. The top 25% of identifiers in column 1 of permnos is then appended to ports. Then the top 25% of identifiers in column 2 are appended to ports. This will populate the first column of matrix. The code next follows the same procedure on columns 1, 2, and 3 of permnos to populate the second column of matrix. And so forth. A csv file is written for each quantile.

My Specific Questions

As noted in the stackoverflow post, I initially tried to approach this using qcut(). I was unable to adapt the suggested solution to work, but perhaps it is a better method than my solution. So question 1 is: Is there a better method to generate my slices used in the second embedded for-loop (qcut() or otherwise)? The primary requirement is that the slices within a column are of roughly the same size for each quantile.

Question 2: Should I use a second dataframe (in this case, pntls) to do my conditioning, or should I append the calculations in the first loop to permnos?

Question 3: Given a method to determine the slices, is my second for-loop an efficient way to create ports and matrix?

Question 4: For my data, the dates that are the column headers of Ranks.csv change often. As such, I prefer to use selection by position as opposed to selection by label. However, most of the posts I found when writing this code use selection by label. Is there any reason to avoid selection by position/prefer selection by label?

Finally, any other points/critiques of my code are greatly appreciated. Please point out anything I've done in an inefficient manner, especially with regards to speed.

Answer 1

To answer your questions in order:

1. Yes: your code could be optimised by calculating .count() (with respect to each slice of pntls) only once per outer loop, instead of n + 1 times (where n is the value of .count()) as per your current implementation. In the full example below, I show how you can use pandas.qcut to make this much more efficient in the add_quantiles function.

2. Re-using calculations is more efficient, but a procedure that doesn't alter the dataframe is more flexible: in the exmaple below, the calculations are re-used, but the main dataset isn't changed (everything is done from a copy). This may not be desiarable if: (a) you don't intend on using this code for any other purpose (or for more than one use, where keeping the original dataset untouched means you can re-run the procedure), or (b) if the size of the data in memory is large.

3. It could be improved: if I have understood corretly, the results of calculating col_slice in the inner most loop is actually used hld_per number of times (except when the column being passed to col_slice is within hld_per columns from the end of the dataset). We can reduce the number of calculations of col_slice by a factor of hld_per, therefore, if we re-use the results. In the example I've given below, I show how you can do this to improve performance.

4. Only if you don't know your data structure ahead of time; you've used indexing by position rather than label to good effect.

5. Apart from the points above: your code was easy to read (thanks, too, for the detailed question!)!

My suggested re-write using the above suggestions is given below. The main function to run is create_csvs (nb: this uses label indexing instead of the positional indexes that you use - this could be switch out if you prefer). This will create csv files in the same format as your example code.

NB: I have assumed that the order of the rows in each column of the output CSVs doesn't matter. If this does matter, I can suggest a change.

    from collections import defaultdict

    colname = lambda col, suffix: '{}_{}'.format(suffix, col)

    def add_quantiles(data, columns, suffix, quantiles=4, labels=None):
        """ For each column name in columns, create a new categorical column with
            the same name as colum, with the suffix specified added, that
            specifies the quantile of the row in the original column using
            pandas.qcut().

            Input
            _____
            data:
                pandas dataframe
            columns:
                list of column names in `data` for which this function is to create
                quantiles
            suffix:
                string suffix for new column names ({`suffix`}_{collumn_name})
            labels:
                list of labels for each quantile (should have length equal to `quantiles`)

            Output
            ______
            pandas dataframe containing original columns, plus new columns with quantile
            information for specified columns.
        """
        d = data.copy()
        quantile_labels = labels or [
            '{:.0f}%'.format(i*100/quantiles) for i in range(1, quantiles+1)
        ]
        for column in columns:
            d[colname(column, suffix)] = pd.qcut(d[column], quantiles, labels=quantile_labels)
        return d


    def create_csvs(data, columns, hld_per=3, quantiles=4, suffix='quant'):
        """
            Input
            _____
            data:
                the full dataset
            columns:
                the ordered list of names of the date columns
            hld_per:
                holding period
            suffix:
                string suffix for quantile columns (no effect on output)
            quantiles:
                number of quantiles

            Output
            ______
            None

            Effect
            ______
            writes csv files
        """
        quantile_labels = ['{:.0f}%'.format(i*100/quantiles) for i in range(1, quantiles+1)]
        d = add_quantiles(data, columns, suffix, quantiles, quantile_labels)

        period_dict = lambda: defaultdict(list)
        ports = defaultdict(period_dict)
        for i in range(len(columns) - hld_per + 1):
            column = columns[i]
            period_columns = columns[max(i-hld_per, 0):i+1]
            for label in quantile_labels:
                for period in period_columns:
                    ports[label][period] += list(d[column][d[colname(column, suffix)] == label].values)

        for i, label in enumerate(quantile_labels):
            # Fill each list in `ports` with `np.null` so that they all have the same length of `maxlen`
            max_len = max(map(lambda v: len(v[1]), ports[label].items()))
            for c in ports[label]:
                ports[label][c] += [np.NaN]*(max_len-len(ports[label][c]))
            pd.DataFrame(ports[label]).to_csv("portstst2_" + str(i+1) + ".csv", sep=',', index=False, header=True)