pandasdatagriddatatemplategeneticsgridview-sorting

Sorting a string of numbers into a grid


I'm needing to sort a long list of ID numbers into 'grids' of 8 ID numbers down (8 cells/rows), 6 ID numbers across (or 6 columns long etc), sorted from smallest to largest ID number. When one 'grid' is 'full', the numbers which cannot fit in the first grid should go on to form a second one and so on. The last 4 cells of the last row should be blank. (This is a template for a lab procedure).

ie this is the data I have: column of ID numbers

and this how I want it to be (but like, 6 of these) example 'grid'


Solution

  • Here's one method.

    Sample data

    import pandas as pd
    import numpy as np
    
    # Sorted list of string IDs
    l = np.arange(0, 631, 1).astype('str')
    

    Code

    N = 44
    # Ensure we can reshape last group
    data = np.concatenate((l, np.repeat('', N-len(l)%N))) 
    
    # Split array, make a separate `DataFrame` for each grid.
    data = [
        pd.DataFrame(np.concatenate((x, np.repeat('', 4))).reshape(8,6)) 
        for x in np.array_split(data, np.arange(N, len(l), N))
        ]
    
    df = pd.concat(data, ignore_index=True)  # If want a single df in the end
    

    Output df:

           0    1    2    3    4    5
    0      0    1    2    3    4    5
    1      6    7    8    9   10   11
    2     12   13   14   15   16   17
    3     18   19   20   21   22   23
    4     24   25   26   27   28   29
    5     30   31   32   33   34   35
    6     36   37   38   39   40   41
    7     42   43                    
    8     44   45   46   47   48   49
    9     50   51   52   53   54   55
    10    56   57   58   59   60   61
    11    62   63   64   65   66   67
    12    68   69   70   71   72   73
    13    74   75   76   77   78   79
    14    80   81   82   83   84   85
    15    86   87                    
    16    88   89   90   91   92   93
    ...
    110  608  609  610  611  612  613
    111  614  615                    
    112  616  617  618  619  620  621
    113  622  623  624  625  626  627
    114  628  629  630               
    115                              
    116                              
    117                              
    118                              
    119