# Create quantile slices from dataframe columns

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

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)

• Thank you. When I run the code, I receive ValueError: arrays must all be same length. I added various print statements to troubleshoot, and the error seems to arise from the for-loop at the end of create_csvs(). I changed the loop to: for label in enumerate(quantile_labels): for i in range(quantiles): which executed without error, but the csv files are empty. Digging a little more, I added a for-loop to print pd.DataFrame(ports[label]) just prior to the final for-loop, and the printed output is a succession of Empty DataFrame Columns: [] Index: []. Any ideas on what's going wrong? – CodingNewb Sep 18 '17 at 22:15
• Hi CodingNewb. There were two things wrong with my code: (1) my definition of period_columns in create_csvs was wrong (resulting in strange numbers of rows in the first few columns), this is now changed, and; (2) the ports[label] dictionary would contain lists of different lengths due to columns towards the end of the dataset having insufficient information to complete the column. The lines starting max_len in my edited answer add padding to each column that has fewer elements than the total for any column in that quantile. The code now works as expected on my computer. – act Sep 19 '17 at 10:27
• Thanks act, and sorry it's taking me some time to process this. Now that I have everything running, I'm having two main issues: First, it appears that add_quantiles calculates the quantile of the value of the identifier as opposed to the identifier's position in the appropriate column (the identifiers are numeric, but their values do not relate to their rank). Second, I think there may still be an issue with period_columns that I'm struggling to fix. An example to explain: Suppose permnos has monthly columns ranging from 20131231 to 20120131 and hld_per is 12. (...cont) – CodingNewb Sep 19 '17 at 22:27
• (cont) In each of the quantiles output files, the column for 20131231 should hold IDs from columns 20131231 to 20130131 of d. The column for 20131130 should hold IDs from columns 20131130 to 20121231 (i.e. 12 columns of d including column[i]). Currently, column 20131231 of each csv has data from 13 columns of d, column 20131130 of each csv has 12, column 20131031 has 11, etc. I’m struggling to make the logic work and would appreciate any help. – CodingNewb Sep 19 '17 at 22:28