Transforming pandas dataframe to array of fixed-size segments

Admin 1
Viewed 267

I am working to transform my dataframe into an array of fixed-sized segments that I should feed to a convolutional neural net. Specifically, I would like to transfrom the df to list of m arrays each containing segments sized (1,5,4). So at last, I would have an (m,1,5,4) array.

To clarify my question, I explain using this MWE. Suppose this is my df:

df = {
    'id': [1,1,1,1,1,1,1,1,1,1,1,1],
'speed': [17.63,17.63,0.17,1.41,0.61,0.32,0.18,0.43,0.30,0.46,0.75,0.37],
'acc': [0.00,-0.09,1.24,-0.80,-0.29,-0.14,0.25,-0.13,0.16,0.29,-0.38,0.27],
'jerk': [0.00,0.01,-2.04,0.51,0.15,0.39,-0.38,0.29,0.13,-0.67,0.65,0.52],
'bearing': [29.03,56.12,18.49,11.85,36.75,27.52,81.08,51.06,19.85,10.76,14.51,24.27],
'label' : [3,3,3,3,3,3,3,3,3,3,3,3] }

df = pd.DataFrame.from_dict(df)

To do this, I use this function:

def df_transformer(dataframe, chunk_size=5):
    
    grouped = dataframe.groupby('id')

    # initialize accumulators
    X, y = np.zeros([0, 1, chunk_size, 4]), np.zeros([0,])

    # loop over segments (id)
    for _, group in grouped:

        inputs = group.loc[:, 'speed':'bearing'].values
        label = group.loc[:, 'label'].values[0]

        # calculate number of splits
        N = len(inputs) // chunk_size

        if N > 0:
            inputs = np.array_split(inputs, [chunk_size]*N)
        else:
            inputs = [inputs]
        
        # loop over splits
        for inpt in inputs:
            inpt = np.pad(
                inpt, [(0, chunk_size-len(inpt)),(0, 0)], 
                mode='constant')
            # add each inputs split to accumulators
            X = np.concatenate([X, inpt[np.newaxis, np.newaxis]], axis=0)
            y = np.concatenate([y, label[np.newaxis]], axis=0) 

    return X, y

The df above has 12 rows, so if transformed correctly to the intended form, I should get an array of shape (3,1,5,4). In the above function, segments with less than 5-rows are zero-padded, to make segment shaped (1,5,4).

Currently, I have two problems with this function:

  1. The function works well for rows less than 10 only in my df.

Like so (last row should then be zero-padded in the following):

X , y = df_transformer(df[:9])
X
array([[[[ 1.763e+01,  0.000e+00,  0.000e+00,  2.903e+01],
         [ 1.763e+01, -9.000e-02,  1.000e-02,  5.612e+01],
         [ 1.700e-01,  1.240e+00, -2.040e+00,  1.849e+01],
         [ 1.410e+00, -8.000e-01,  5.100e-01,  1.185e+01],
         [ 6.100e-01, -2.900e-01,  1.500e-01,  3.675e+01]]],


       [[[ 3.200e-01, -1.400e-01,  3.900e-01,  2.752e+01],
         [ 1.800e-01,  2.500e-01, -3.800e-01,  8.108e+01],
         [ 4.300e-01, -1.300e-01,  2.900e-01,  5.106e+01],
         [ 3.000e-01,  1.600e-01,  1.300e-01,  1.985e+01],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00]]]])

But introduces an all-zero array (segment) in this case:

X , y = df_transformer(df[:10])
X
array([[[[ 1.763e+01,  0.000e+00,  0.000e+00,  2.903e+01],
         [ 1.763e+01, -9.000e-02,  1.000e-02,  5.612e+01],
         [ 1.700e-01,  1.240e+00, -2.040e+00,  1.849e+01],
         [ 1.410e+00, -8.000e-01,  5.100e-01,  1.185e+01],
         [ 6.100e-01, -2.900e-01,  1.500e-01,  3.675e+01]]],


       [[[ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00]]],


       [[[ 3.200e-01, -1.400e-01,  3.900e-01,  2.752e+01],
         [ 1.800e-01,  2.500e-01, -3.800e-01,  8.108e+01],
         [ 4.300e-01, -1.300e-01,  2.900e-01,  5.106e+01],
         [ 3.000e-01,  1.600e-01,  1.300e-01,  1.985e+01],
         [ 4.600e-01,  2.900e-01, -6.700e-01,  1.076e+01]]]])
  1. The function fails if I pass a whole df (I don't understand the error but it seems to be related to the padding of segments less than 5 rows).

So in the case, I get index can't contain negative values error:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-5-1fc559db37eb> in <module>()
----> 1 X , y = df_transformer(df)

2 frames
<ipython-input-4-9e1c49985863> in df_transformer(dataframe, chunk_size)
     24             inpt = np.pad(
     25                 inpt, [(0, chunk_size-len(inpt)),(0, 0)],
---> 26                 mode='constant')
     27             # add each inputs split to accumulators
     28             X = np.concatenate([X, inpt[np.newaxis, np.newaxis]], axis=0)

<__array_function__ internals> in pad(*args, **kwargs)

/usr/local/lib/python3.6/dist-packages/numpy/lib/arraypad.py in pad(array, pad_width, mode, **kwargs)
    746 
    747     # Broadcast to shape (array.ndim, 2)
--> 748     pad_width = _as_pairs(pad_width, array.ndim, as_index=True)
    749 
    750     if callable(mode):

/usr/local/lib/python3.6/dist-packages/numpy/lib/arraypad.py in _as_pairs(x, ndim, as_index)
    517 
    518     if as_index and x.min() < 0:
--> 519         raise ValueError("index can't contain negative values")
    520 
    521     # Converting the array with `tolist` seems to improve performance

ValueError: index can't contain negative values

Expected output:

X , y = df_transformer(df)
X
array([[[[ 1.763e+01,  0.000e+00,  0.000e+00,  2.903e+01],
         [ 1.763e+01, -9.000e-02,  1.000e-02,  5.612e+01],
         [ 1.700e-01,  1.240e+00, -2.040e+00,  1.849e+01],
         [ 1.410e+00, -8.000e-01,  5.100e-01,  1.185e+01],
         [ 6.100e-01, -2.900e-01,  1.500e-01,  3.675e+01]]],

       [[[ 3.200e-01, -1.400e-01,  3.900e-01,  2.752e+01],
         [ 1.800e-01,  2.500e-01, -3.800e-01,  8.108e+01],
         [ 4.300e-01, -1.300e-01,  2.900e-01,  5.106e+01],
         [ 3.000e-01,  1.600e-01,  1.300e-01,  1.985e+01],
         [ 4.600e-01,  2.900e-01, -6.700e-01,  1.076e+01]]],

       [[[ 7.500e-01,  -3.800e-01,  6.500e-01,  1.451e+01],
         [ 3.700e-01,  2.700e-01,  5.200e-01,  2.427e+01],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00],
         [ 0.000e+00,  0.000e+00,  0.000e+00,  0.000e+00]]]])

Can someone help me fix this? The WME above works fine to reproduce this bug.

EDIT

The answer by RichieV also has a bug. Although it works in the given MWE it doesn't do the right task in the case below (extending the dftwice

its size):
df = {
    'id': [1]*12+[2]*12,
    'speed': [17.63,17.63,0.17,1.41,0.61,0.32,0.18,0.43,0.30,0.46,0.75,0.37]*2,
    'acc': [0.00,-0.09,1.24,-0.80,-0.29,-0.14,0.25,-0.13,0.16,0.29,-0.38,0.27]*2,
    'jerk': [0.00,0.01,-2.04,0.51,0.15,0.39,-0.38,0.29,0.13,-0.67,0.65,0.52]*2,
    'bearing': [29.03,56.12,18.49,11.85,36.75,27.52,81.08,51.06,19.85,10.76,14.51,24.27]*2,
    'label' : [3,3,3,3,3,3,3,3,3,3,3,3]*2 }
df = pd.DataFrame.from_dict(df)

X, y = df_transformer(df, chunk_size=5)
print(X[:3])

[[[[ 1.763e+01  0.000e+00  0.000e+00  2.903e+01]
   [ 0.000e+00  0.000e+00  0.000e+00  0.000e+00]
   [ 0.000e+00  0.000e+00  0.000e+00  0.000e+00]
   [ 0.000e+00  0.000e+00  0.000e+00  0.000e+00]
   [ 3.700e-01  2.700e-01  5.200e-01  2.427e+01]]]


 [[[ 7.500e-01 -3.800e-01  6.500e-01  1.451e+01]
   [ 3.000e-01  1.600e-01  1.300e-01  1.985e+01]
   [ 4.600e-01  2.900e-01 -6.700e-01  1.076e+01]
   [ 1.800e-01  2.500e-01 -3.800e-01  8.108e+01]
   [ 3.200e-01 -1.400e-01  3.900e-01  2.752e+01]]]


 [[[ 6.100e-01 -2.900e-01  1.500e-01  3.675e+01]
   [ 1.410e+00 -8.000e-01  5.100e-01  1.185e+01]
   [ 1.700e-01  1.240e+00 -2.040e+00  1.849e+01]
   [ 1.763e+01 -9.000e-02  1.000e-02  5.612e+01]
   [ 4.300e-01 -1.300e-01  2.900e-01  5.106e+01]]]]

Notice the first element isn't the same as in the answer (got an all-zero in the 2nd, 3rd and 4th rows.

pandas numpy-ndarray multidimensional-array python-3.x python
1 Answers

You can pad the df once instead of padding on each iteration.

Take this data with a second id

df = {
    'id': [1,1,1,1,1,1,1,1,1,2,2,2],
    'speed': [17.63,17.63,0.17,1.41,0.61,0.32,0.18,0.43,0.30,0.46,0.75,0.37],
    'acc': [0.00,-0.09,1.24,-0.80,-0.29,-0.14,0.25,-0.13,0.16,0.29,-0.38,0.27],
    'jerk': [0.00,0.01,-2.04,0.51,0.15,0.39,-0.38,0.29,0.13,-0.67,0.65,0.52],
    'bearing': [29.03,56.12,18.49,11.85,36.75,27.52,81.08,51.06,19.85,10.76,14.51,24.27],
    'label' : [3,3,3,3,3,3,3,3,3,3,3,3] }
df = pd.DataFrame.from_dict(df)
print(df)

    id  speed   acc  jerk  bearing  label
0    1  17.63  0.00  0.00    29.03      3
1    1  17.63 -0.09  0.01    56.12      3
2    1   0.17  1.24 -2.04    18.49      3
3    1   1.41 -0.80  0.51    11.85      3
4    1   0.61 -0.29  0.15    36.75      3
5    1   0.32 -0.14  0.39    27.52      3
6    1   0.18  0.25 -0.38    81.08      3
7    1   0.43 -0.13  0.29    51.06      3
8    1   0.30  0.16  0.13    19.85      3
9    2   0.46  0.29 -0.67    10.76      3
10   2   0.75 -0.38  0.65    14.51      3
11   2   0.37  0.27  0.52    24.27      3

And the code

def df_transformer(df, chunk_size=5):
    ### pad df with 0's so len(df) is exactly a multiple of chunk_size
    df = pd.concat([df,
        pd.DataFrame([[id] + [0] * 5 # add row with zeros
            for id, ct in df.groupby('id').size().iteritems() # for each id
            for row in range(chunk_size - ct % chunk_size)] # as many times as needed
            , columns=df.columns)
    ]).sort_values('id', kind='mergesort', ignore_index=True)
    # print(df)
    X, y = [], []
    for _, group in df.groupby(df.index//5):
        X.append(group.iloc[:, 1:-1].values[np.newaxis, ...])
        y.append(group.iloc[0, -1]) # not sure how you want y to be structured
    return np.array(X), np.array(y)


X, y = df_transformer(df, chunk_size=5)
print(X)

Output

[[[[ 1.763e+01  0.000e+00  0.000e+00  2.903e+01]
   [ 1.763e+01 -9.000e-02  1.000e-02  5.612e+01]
   [ 1.700e-01  1.240e+00 -2.040e+00  1.849e+01]
   [ 1.410e+00 -8.000e-01  5.100e-01  1.185e+01]
   [ 6.100e-01 -2.900e-01  1.500e-01  3.675e+01]]]

 [[[ 3.200e-01 -1.400e-01  3.900e-01  2.752e+01]
   [ 1.800e-01  2.500e-01 -3.800e-01  8.108e+01]
   [ 4.300e-01 -1.300e-01  2.900e-01  5.106e+01]
   [ 3.000e-01  1.600e-01  1.300e-01  1.985e+01]
   [ 0.000e+00  0.000e+00  0.000e+00  0.000e+00]]]

 [[[ 4.600e-01  2.900e-01 -6.700e-01  1.076e+01]
   [ 7.500e-01 -3.800e-01  6.500e-01  1.451e+01]
   [ 3.700e-01  2.700e-01  5.200e-01  2.427e+01]
   [ 0.000e+00  0.000e+00  0.000e+00  0.000e+00]
   [ 0.000e+00  0.000e+00  0.000e+00  0.000e+00]]]]

Notice how the first two sections are from id==1 and the last one is from id==2, each with its own zero-padding

user_66928980
Related