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
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
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
Question 3: Given a method to determine the slices, is my second for-loop an efficient way to create
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.