2

Working with data that I want to pivot. Note that I am limited to only numpy and am unable to use pandas. The original data looks like this:

data = [
  [ 1, a, [<metric1>, <metric2>] ],
  [ 1, b, [<metric1>, <metric2>] ],
  [ 2, b, [<metric1>, <metric2>] ],
  [ 2, c, [<metric1>, <metric2>] ],
  [ 3, a, [<metric1>, <metric2>] ],
  [ 3, c, [<metric1>, <metric2>] ],
  ...etc
]

Pivoting my data with numpy:

rows, row_pos = np.unique(data[:, row_index], return_inverse=True)
cols, col_pos = np.unique(data[:, col_index], return_inverse=True)
pivot_table = np.zeros((len(rows), len(cols)), dtype=object)
pivot_table[row_pos, col_pos] = data[:, pivot_index]

The resulting format is:

cols = [a, b, c, ...]
rows = [1, 2, 3, ...]
pivot_table = [
  [ [<metric1>, <metric2>], [<metric1>, <metric2>], 0, ... ],
  [ 0, [<metric1>, <metric2>], [<metric1>, <metric2>], ... ],
  [ [<metric1>, <metric2>], 0, [<metric1>, <metric2>], ... ],
  ...
]

The pivoted table is eventually rendered, where it notes where the zeros are, and will create the correct number of cells so that the table is correctly formatted.

This is just a temporary workaround, as originally I tried just replacing the zeros with a numpy array (i.e., [0,0])

pivot_table[pivot_table == 0] = [0,0]

But I got the following error:

TypeError: NumPy boolean array indexing assignment requires a 0 or 1-dimensional input, input has 2 dimensions

My temporary fix was sufficient, but am limited when I want to do something such as having a row of column sums. I have a number of approaches but don't know how to execute them:

  1. As mentioned above, replacing zeros after the fact with a list of zeros
  2. When initially creating the table using the indexes from np.unique, have a default value to fill the table, instead of zeros.
  3. Pull out the metrics from the list into the array i.e., [ 1, a, <metric1>, <metric2> ]. This is likely the best solution to simplify aggregate functions.

Any solutions for either of the approaches mentioned?

Improve this question
3
  • Look at pivot_table[pivot_table == 0]. It's probably a 1d array of 0s. Assigning a scalar, or object like None to those locations should work fine. But assigning a list will be tricky. numpy will convert the list to ndarray and then try to apply broadcasting. In general. Assigning a list to a single element of an object array works fine, but assigning to multiple ones is hard. Commented Nov 23, 2018 at 17:52
  • It's not a 1d array of 0's though. They display as a list([metric1, metric2]) for each item. I've tried just having a standard 2D array, but then I would only be able to pivot one of the metrics. I suppose a solution would be to create individual tables for each metric, then merge them into one afterwards. Commented Nov 23, 2018 at 18:14
  • Boolean masking of any array produces a 1d array. Commented Nov 23, 2018 at 19:15

3 Answers 3

Reset to default
2

Trying to recreate your case:

In [182]: a,b,c = 0,1,2
In [183]: metric1, metric2 = 100,200
In [186]: data = [
     ...:   [ 1, a, [metric1, metric2] ],
     ...:   [ 1, b, [metric1, metric2] ],
     ...:   [ 2, b, [metric1, metric2] ],
     ...:   [ 2, c, [metric1, metric2] ],
     ...:   [ 3, a, [metric1, metric2] ],
     ...:   [ 3, c, [metric1, metric2] ],
     ...: ]
In [187]: 
In [187]: data
Out[187]: 
[[1, 0, [100, 200]],
 [1, 1, [100, 200]],
 [2, 1, [100, 200]],
 [2, 2, [100, 200]],
 [3, 0, [100, 200]],
 [3, 2, [100, 200]]]

In [189]: data = np.array(data,object)
In [190]: rows, row_pos = np.unique(data[:, 0], return_inverse=True)
     ...: cols, col_pos = np.unique(data[:, 1], return_inverse=True)
     ...: pivot_table = np.zeros((len(rows), len(cols)), dtype=object)

In [191]: pivot_table
Out[191]: 
array([[0, 0, 0],
       [0, 0, 0],
       [0, 0, 0]], dtype=object)
In [192]: pivot_table[row_pos, col_pos] = data[:, 2]
In [193]: pivot_table
Out[193]: 
array([[list([100, 200]), list([100, 200]), 0],
       [0, list([100, 200]), list([100, 200])],
       [list([100, 200]), 0, list([100, 200])]], dtype=object)
In [194]: pivot_table[row_pos, col_pos]
Out[194]: 
array([list([100, 200]), list([100, 200]), list([100, 200]),
       list([100, 200]), list([100, 200]), list([100, 200])], dtype=object)
In [195]: _.shape
Out[195]: (6,)
In [196]: data[:,2].shape
Out[196]: (6,)

This assignment works between the source shape (and dtype) matches the target's (6,).

In [197]: mask = pivot_table==0
In [198]: mask
Out[198]: 
array([[False, False,  True],
       [ True, False, False],
       [False,  True, False]])
In [199]: pivot_table[mask]
Out[199]: array([0, 0, 0], dtype=object)
In [200]: pivot_table[mask] = [0,0]
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-200-83e0a7422802> in <module>()
----> 1 pivot_table[mask] = [0,0]

ValueError: NumPy boolean array indexing assignment cannot assign 2 input values to the 3 output values where the mask is true

Different error message (different numpy version?), but this says I'm trying to put 2 values into 3 slots. It doesn't treat the [0,0] as a single item, but as 2.

No problem assigning a scalar element:

In [203]: pivot_table[mask] = None
In [204]: pivot_table
Out[204]: 
array([[list([100, 200]), list([100, 200]), None],
       [None, list([100, 200]), list([100, 200])],
       [list([100, 200]), None, list([100, 200])]], dtype=object)

In the past I've had success using frompyfunc to create object dtype arrays. Define a little function. I could have tested for 0 or type, but since I've already inserted None, let's test for that:

In [205]: def fun(x):
     ...:     if x is None: return [0,0]
     ...:     return x

Apply it to each element of pivot_table, producing a new array.

In [230]: arr1 = np.frompyfunc(fun,1,1)(pivot_table)
In [231]: arr1
Out[231]: 
array([[list([100, 200]), list([100, 200]), list([0, 0])],
       [list([0, 0]), list([100, 200]), list([100, 200])],
       [list([100, 200]), list([0, 0]), list([100, 200])]], dtype=object)

Another approach, let's try to assign a list of lists:

In [240]: pivot_table[mask] = [[0,0] for _ in range(3)]    
TypeError: NumPy boolean array indexing assignment requires a 0 or 1-dimensional input, input has 2 dimensions

But if I try the same thing with where, it works:

In [241]: pivot_table[np.where(mask)] = [[0,0] for _ in range(3)]
In [242]: pivot_table
Out[242]: 
array([[list([100, 200]), list([100, 200]), list([0, 0])],
       [list([0, 0]), list([100, 200]), list([100, 200])],
       [list([100, 200]), list([0, 0]), list([100, 200])]], dtype=object)

With where it's more like your original assignment to pivot_table.

In [243]: np.where(mask)
Out[243]: (array([0, 1, 2]), array([2, 0, 1]))

This array indexing still can have problems with broadcasting, 

In [244]: pivot_table[np.where(mask)] = [0,0]
ValueError: cannot copy sequence with size 2 to array axis with dimension 3

Usually boolean mask index behaves like the equivalent np.where(mask) indexing, but evidently here, the interplay of object dtype, and broadcasting messes with the boolean indexing.

Out[231] is still a (3,3) array, even though all elements a len 2 lists. To turn it into a numeric array we have to do something like:

In [248]: p = np.stack(pivot_table.ravel()).reshape(3,3,2)
In [249]: p
Out[249]: 
array([[[100, 200],
        [100, 200],
        [  0,   0]],

       [[  0,   0],
        [100, 200],
        [100, 200]],

       [[100, 200],
        [  0,   0],
        [100, 200]]])

np.concatenate (and *stack versions) can join lists into an array, but it has to start with a list or flat array, hence the need for ravel and reshape.

np.array(pivot_table.tolist()) also works.

If instead you'd constructed a structured data array (assuming the metric values are numeric):

In [265]: data1 = np.array([tuple(x.tolist()) for x in data],'i,i,2i')
In [266]: data1
Out[266]: 
array([(1, 0, [100, 200]), (1, 1, [100, 200]), (2, 1, [100, 200]),
       (2, 2, [100, 200]), (3, 0, [100, 200]), (3, 2, [100, 200])],
      dtype=[('f0', '<i4'), ('f1', '<i4'), ('f2', '<i4', (2,))])
In [267]: data1['f2']
Out[267]: 
array([[100, 200],
       [100, 200],
       [100, 200],
       [100, 200],
       [100, 200],
       [100, 200]], dtype=int32)

these values could be assigned to a 3d pivot_table:

In [268]: p = np.zeros((len(rows), len(cols),2),int)
In [269]: p[row_pos, col_pos]=data1['f2']

With the fillvalue array that Paul Panzer defined, your initial masked assignment works:

In [322]: fillvalue = np.empty((), 'O')
     ...: fillvalue[()] = [0, 0]
     ...: 
In [323]: fillvalue
Out[323]: array(list([0, 0]), dtype=object)
In [324]: mask
Out[324]: 
array([[False, False,  True],
       [ True, False, False],
       [False,  True, False]])
In [325]: pivot_table[mask] = fillvalue

His full does a np.copyto(a, fill_value, casting='unsafe'), Our masked assignment could be written as: np.copyto(pivot_table, fillvalue, where=mask)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The error is different as my error came from dynamically producing np.zeros((1, len(numMetrics))). I get a similar error when directly assigning [0,0], although I am using an earlier version (due to Standard Google App Engine limitations). A handful of your initial solutions had issues with dynamic sized metrics, or constructed data types was not possible due to the data itself. The last solution with fillvalue did the trick.
You didn't provide a minimal reproducible example so I had to improvise, making data that roughly fits your description. As you see from the answers, we prefer to use real working code, not just generic descriptions. It's too easy to make errors when writing pseudocode.
That's fair, I appreciate the thorough and numerous answers you provided, just wanted to let you know what worked for me and what didn't. I did have to do some generalizations to my company data, but I'll make sure to provide examples that is representative to my problem in the future (I didn't really anticipate the dtype of the data to have as large of an effect as it did)
2

Here is how to get your approach 2 to work:

fillvalue = np.empty((), 'O')
fillvalue[()] = [0, 0]
pivot_table = np.full((len(rows), len(cols)), fillvalue)

etc.

Note that the [0, 0]s are all the same object, so if you want to change one of them you shouldn't do it by modifying the list object in place, but rather create a new list and assign it to the array position.

If you want a 3D numerical array instead of an array of lists, the quick fix is np.array(pivot_table.tolist()).

Improve this answer

1 Comment

With your object, the original masked assignment works, pivot_table[pivot_table == 0] = fillvalue. Same caution about in place modification.
1

Your entry data types are not clear, an can create inconvenience. Avoiding object type facilitate data structure analysis. Using structured array can help:

Sample raw data: 

n=10
data= [ [randint(5),'abcdef'[randint(6)],rand(2)] for _ in range(n)]

Manually typing and filling:

dt=np.dtype([('i', 'i4'), ('j', 'U1'), ('val', 'f8', 2)])
arr = ndarray(len(data),dtype=dt)
for k,(a,b,c) in enumerate (data):
    arr[k]['i']=a
    arr[k]['j']=b
    arr[k]['val']=c

Now all is easy:

row=arr['i']
col=arr['j']
val=arr['val']

(r,ri),(c,ci) = (np.unique(x,return_inverse=True) for x in (row,col))
res=zeros((len(r),len(c),2)) # the good shape
res[ri,ci]=val

res is now

[[[ 0.87  0.96]
  [ 0.03  0.92]
  [ 0.45  0.55]
  [ 0.    0.  ]
  [ 0.    0.  ]]

 [[ 0.27  0.84]
  [ 0.    0.  ]
  [ 0.41  0.05]
  [ 0.47  0.67]
  [ 0.    0.  ]]

 [[ 0.3   0.05]
  [ 0.    0.  ]
  [ 0.    0.  ]
  [ 0.    0.  ]
  [ 0.37  0.76]]

 [[ 0.    0.  ]
  [ 0.    0.  ]
  [ 0.    0.  ]
  [ 0.    0.  ]
  [ 0.4   0.07]]]
Improve this answer

1 Comment

This was useful, but unfortunately my first two columns is have variable length, so it doesn't work with all data use cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.