将数据帧列切割为十分位并附加到矩阵

2 回答

• 1

  如果我理解正确，那么是的， qcut() 可以为你找到你正在寻找的分裂 .

  我们将从构建示例 permnos 数据框开始 . 这基于OP permnos.head() ，加上一些额外的行来说明列长度的异质性 .

  import pandas as pd
  
  data = {'20130630': {0: 91274.0, 1: 93353.0, 2: 85576.0, 3: 10294.0, 4: 47387.0, 5: np.nan, 6: np.nan},
          '20130731': {0: 82486.0, 1: 88359.0, 2: 47387.0, 3: 10294.0, 4: 90710.0},
          '20130831': {0: 89169.0, 1: 47387.0, 2: 85576.0, 3: 81148.0, 4: 11285.0},
          '20130930': {0: 13264, 1: 81148, 2: 11513, 3: 81562, 4: np.nan},
          '20131031': {0: 13264, 1: 90710, 2: 88810, 3: 81562, 4: 82281},
          '20131130': {0: 93044.0, 1: 91515.0, 2: 85724.0, 3: 89169.0, 4: 90710.0, 5: 80000., 6: 900000.},
          '20131231': {0: 93044.0, 1: 79702.0, 2: 85751.0, 3: 85576.0, 4: 13264.0, 5: np.nan}}
  
  permnos = pd.DataFrame(data)
  permnos
     20130630  20130731  20130831  20130930  20131031  20131130  20131231
  0   91274.0   82486.0   89169.0   13264.0   13264.0   93044.0   93044.0
  1   93353.0   88359.0   47387.0   81148.0   90710.0   91515.0   79702.0
  2   85576.0   47387.0   85576.0   11513.0   88810.0   85724.0   85751.0
  3   10294.0   10294.0   81148.0   81562.0   81562.0   89169.0   85576.0
  4   47387.0   90710.0   11285.0       NaN   82281.0   90710.0   13264.0
  5       NaN       NaN       NaN       NaN       NaN   80000.0       NaN
  6       NaN       NaN       NaN       NaN       NaN  900000.0       NaN
  

  考虑一下 qcut() returns：

  out：如果标签为False，则为分类或系列或整数数组返回类型（分类或系列）取决于输入：如果输入是Series else Categorical，则为类型类别系列 .

  我们正在传递系列数据，因此我们将获得一系列类型 category 作为输出 . 例如：

  n_bin = 3
  out = pd.qcut(permnos["20130630"].dropna(), n_bin)
  out
  0      (89374.667, 93353.0]
  1      (89374.667, 93353.0]
  2    (60116.667, 89374.667]
  3    (10293.999, 60116.667]
  4    (10293.999, 60116.667]
  Name: 20130630, dtype: category
  Categories (3, interval[float64]): [(10293.999, 60116.667] < (60116.667, 89374.667] < (89374.667, 93353.0]]
  

  我们可以看到它如何处理我们的不均匀分裂;这三个类别分别制作了2个，1个和2个数据点的桶 . 现在，我们可以构造一个布尔掩码，只获取我们关心的存储桶中的条目：

  out == out.cat.categories[2] # categories are ordered small to large
  0     True
  1     True
  2    False
  3    False
  4    False
  Name: 20130630, dtype: bool
  

  通过这种方法，我们应该能够通过仅获取在每次迭代中在目标分位数中被分块的条目来从每列中获取我们想要的切片 .

  我们可以通过将核心操作包装在一个函数_390250中来压缩一些东西 .

  UDPATED
  （针对多个矩阵推广，每条评论）

  hld_per = 5
  my_headers = list(permnos.columns)
  total_cols = len(permnos.columns)
  
  def construct_matrix(df, hld_per, total_cols, n_bin, colnames, start_pos, end_cat):
      ports = []
      started = start_pos
  
      for i in range(total_cols-(hld_per-1)):
          permlist = []
          for j in range(hld_per):
              out = pd.qcut(df.iloc[:,i+j].dropna(), n_bin)
              if not bool(started):
                  end_pos = (out == out.cat.categories[start_pos]).sum()
              else:
                  end_pos = start_pos + (out == out.cat.categories[end_cat]).sum()
              col_slice = df.iloc[start_pos:end_pos, i+j].dropna().tolist()
              permlist += col_slice
          ports.append(permlist)
  
      matrix = pd.DataFrame(ports).T
      matrix.columns = colnames[0:len(matrix.columns)]
  
      return matrix, end_pos
  

  鉴于我们可能想要基于 n_bin 构建任意数量的矩阵，我们设置了一个 construct_matrix() 循环，其中第一个 start_pos 之后的每个新 start_pos 都是 start_pos end_pos ，在上一次迭代的函数内设置 . 我们将生成的矩阵存储在一个列表中， matrices .

  matrices = []
  start_pos = 0
  # number of qcut bins
  n_bin = 3
  
  for i in range(1, n_bin):
  
      end_cat = n_bin - i
  
      print("matrix: {}, start_pos: {}, end_cat: {}".format(i, start_pos, end_cat))
  
      matrix, start_pos = construct_matrix(permnos, hld_per, total_cols, n_bin, my_headers, 
                                           start_pos=start_pos, end_cat=end_cat)
      matrices.append(matrix)
  
      print(matrix)
      print()
  

  输出：

  matrix: 1, start_pos: 0, end_cat: 2
      20130630  20130731  20130831
  0    91274.0   82486.0   89169.0
  1    93353.0   88359.0   47387.0
  2    82486.0   89169.0   13264.0
  3    88359.0   47387.0   81148.0
  4    89169.0   13264.0   13264.0
  5    47387.0   81148.0   90710.0
  6    13264.0   13264.0   93044.0
  7    81148.0   90710.0   91515.0
  8    13264.0   93044.0   85724.0
  9    90710.0   91515.0   93044.0
  10       NaN   85724.0   79702.0
  
  matrix: 2, start_pos: 2, end_cat: 1
     20130630  20130731  20130831
  0   85576.0   47387.0   85576.0
  1   47387.0   85576.0   11513.0
  2   85576.0   11513.0   88810.0
  3   11513.0   88810.0   85724.0
  4   88810.0   85724.0   89169.0
  5       NaN   89169.0   85751.0
  

  这应该可以获得您在列和分位数之间寻找的（大致）偶数分割 .

  回复于 2024-05-14T00:41:51+08:00
• 0

  我从来没有能够使用 qcut 为此制作解决方案，但我已经提出了一个符合我的规格的替代解决方案 . 希望其他人也会发现这个用途 .

  import pandas as pd
  
  hld_per = 12
  quantiles = 10
  permnos = pd.read_csv('Ranks.csv')
  my_headers = list(permnos.columns)
  total_cols = len(permnos.columns)
  
  def slice_range(col_length, quantile):
      increment = col_length // 10
      remainder = col_length % 10 + 1
      addon = 0
      for i in range(quantile-1):
          remainder = max(0, remainder - 1)
          if remainder > 0:
              addon += 1
      start = (quantile - 1) * increment + addon  
      return start
  
  for i in range(quantiles):
      ports = []
      for j in range(total_cols-(hld_per-1)):
          permlist = []
          for k in range(hld_per):
              col_len = permnos.iloc[:,j+k].count()
              start = slice_range(col_len, i+1)
              end = slice_range(col_len, (i+2))
              col_slice = permnos.iloc[start:end, 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("portstst5_" + str(i+1) + ".csv", sep=',', index=False, header=True)
  

  回复于 2024-05-14T00:41:51+08:00