Skip to content
Advertisement

Pandas oversampling ragged sequential data

Trying to use pandas to oversample my ragged data (data with different lengths).

Given the following data samples:

import pandas as pd

x = pd.DataFrame({'id':[1,1,1,2,2,3,3,3,3,4,5,6,6],'f1':[11,11,11,22,22,33,33,33,33,44,55,66,66]})
y = pd.DataFrame({'id':[1,2,3,4,5,6],'target':[1,0,1,0,0,0]})

Data (groups are separated with --- for convince):

    id  f1
0    1  11
1    1  12
2    1  13
-----------
3    2  22
4    2  22
-----------
5    3  33
6    3  34
7    3  35
8    3  36
-----------
9    4  44
-----------
10   5  55
-----------
11   6  66
12   6  66

Targets:

   id  target
0   1       1
1   2       0
2   3       1
3   4       0
4   5       0
5   6       0

I would like to balance the minority class. In the sample above, target 1 is the minority class with 2 samples, for ids 1 & 3.

I’m looking for a way to oversample the data so the results would be:

    id  f1
0    1  11
1    1  12
2    1  13
-----------
3    2  22
4    2  22
-----------
5    3  33
6    3  34
7    3  35
8    3  36
-----------
9    4  44
-----------
10   5  55
-----------
11   6  66
12   6  66
-----------------
13   7  11
14   7  12 Replica of id 1
15   7  13
-----------------
16   8  33
17   8  34 Replica of id 3
18   8  35
19   8  36

And the targets would be balanced:

   id  target
0   1       1
1   2       0
2   3       1
3   4       0
4   5       0
5   6       0
6   7       1
8   8       1

With exactly 4 positive and 4 negative samples.

Advertisement

Answer

You can use:

x = pd.DataFrame({'id':[1,1,1,2,2,3,3,3,3,4,5,6,6],
                  'f1':[11,11,11,22,22,33,33,33,33,44,55,66,66]})

#more general sample
y = pd.DataFrame({'id':[1,2,3,4,5,6,7],'target':[1,0,1,0,0,0,0]})

#repeat values 1 or 0 for balance target
s = y['target'].value_counts()
s1 = s.rsub(s.max())
new = s1.index.repeat(s1).tolist()

#create helper df and add to y
y1 = pd.DataFrame({'id':range(y['id'].max() + 1,y['id'].max() + len(new) + 1), 
                   'target':new})
y2 = y.append(y1, ignore_index=True)
print (y2)


#filter by first value of new
add = y[y['target'].eq(new[0])]

#repeat values by np.tile or is possible change to np.repeat
#add helper column by y1.id and merge to x
add = (add.loc[np.tile(add.index, (len(new) // len(add)) + 1), ['id']]
          .head(len(new))
          .assign(new = y1['id'].tolist())
          .merge(x, on='id', how='left')
          .drop('id', axis=1)
          .rename(columns={'new':'id'}))

#add to x
x2 = x.append(add, ignore_index=True)
print (x2)

Solution above working only for non balanced data, if possible sometimes balanced:

#balanced sample
y = pd.DataFrame({'id':[1,2,3,4,5,6],'target':[1,1,1,0,0,0]})

#repeat values 1 or 0 for balance target
s = y['target'].value_counts()
s1 = s.rsub(s.max())
new = s1.index.repeat(s1).tolist()

if len(new) > 0:

    #create helper df and add to y
    y1 = pd.DataFrame({'id':range(y['id'].max() + 1,y['id'].max() + len(new) + 1),
                       'target':new})
    y2 = y.append(y1, ignore_index=True)
    print (y2)
    
    
    #filter by first value of new
    add = y[y['target'].eq(new[0])]
    
    #repeat values by np.tile or is possible change to np.repeat
    #add helper column by y1.id and merge to x
    add = (add.loc[np.tile(add.index, (len(new) // len(add)) + 1), ['id']]
              .head(len(new))
              .assign(new = y1['id'].tolist())
              .merge(x, on='id', how='left')
              .drop('id', axis=1)
              .rename(columns={'new':'id'}))
    
    #add to x
    x2 = x.append(add, ignore_index=True)
    print (x2)
    
else:
    print ('y is already balanced')
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement