CODEWITHSUNDEEP
pythonpandasgrid
Josh Fox
Josh Fox

Reputation: 101

Place data from a Pandas DF into a Grid or Template

I have process where the end product is a Pandas DF where the output, which is variable in terms of data and length, is structured like this example of the output.

9   80340796
10  80340797
11  80340798
12  80340799
13  80340800
14  80340801
15  80340802
16  80340803
17  80340804
18  80340805
19  80340806
20  80340807
21  80340808
22  80340809
23  80340810
24  80340811
25  80340812
26  80340813
27  80340814
28  80340815
29  80340816
30  80340817
31  80340818
32  80340819
33  80340820
34  80340821
35  80340822
36  80340823
37  80340824
38  80340825
39  80340826
40  80340827
41  80340828
42  80340829
43  80340830
44  80340831
45  80340832
46  80340833

I need to get the numbers in the second column above, into the following grid format based on the numbers in the first column above.

    1   2   3   4   5   6   7   8   9   10  11  12
A   1   9   17  25  33  41  49  57  65  73  81  89
B   2   10  18  26  34  42  50  58  66  74  82  90
C   3   11  19  27  35  43  51  59  67  75  83  91
D   4   12  20  28  36  44  52  60  68  76  84  92
E   5   13  21  29  37  45  53  61  69  77  85  93
F   6   14  22  30  38  46  54  62  70  78  86  94
G   7   15  23  31  39  47  55  63  71  79  87  95
H   8   16  24  32  40  48  56  64  72  80  88  96

So the end result in this example would be

enter image description here

Any advice on how to go about this would be much appreciated. I've been asked for this by a colleague, so the data is easy to read for their team (as it matches the layout of a physical test) but I have no idea how to produce it.

Upvotes: 1

Views: 895

Answers (1)

Andreas
Andreas

Reputation: 9197

pandas pivot table, can do what you want in your question, but first you have to create 2 auxillary columns, 1 determing which column the value has to go in, another which row it is. You can get that as shown in the following example:

import numpy as np
import pandas as pd
df = pd.DataFrame({'num': list(range(9, 28)), 'val': list(range(80001, 80020))})

max_rows = 8

df['row'] = (df['num']-1)%8
df['col'] = np.ceil(df['num']/8).astype(int)

df.pivot_table(values=['val'], columns=['col'], index=['row'])


         val                  
col        2        3        4
row                           
0    80001.0  80009.0  80017.0
1    80002.0  80010.0  80018.0
2    80003.0  80011.0  80019.0
3    80004.0  80012.0      NaN
4    80005.0  80013.0      NaN
5    80006.0  80014.0      NaN
6    80007.0  80015.0      NaN
7    80008.0  80016.0      NaN

Upvotes: 1

Related Questions