Iterate over dask dataframe with if-else condition

Admin 1
Viewed 304

I have a datset of around 15 million rows on which pandas is kind of unable to execute for-loop. I am trying to dask dataframe to speed up the execution time, however, itertation isn't working.

Initial dataframe example:

cols = ['id', 'cur_age', 'EGI0', 'EXP0', 'PEGI', 'PExp', 'gEGI', 'TotExp']
data = [[12003, 1, 446499.51, 214319.76, np.nan, np.nan, 0.00228, 0.00228],
        [12003, 2, 446499.51, 214319.76, np.nan, np.nan, 0.00228, 0.00228],
        [12003, 3, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183],
        [12003, 4, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183],
        [12003, 5, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183],
        [12003, 6, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183],
        [12003, 7, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183],
        [12003, 8, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183],
        [12003, 9, 446499.51, 214319.76, np.nan, np.nan, 0.00184, 0.00184],
        [12003, 10, 446499.51, 214319.76, np.nan, np.nan, 0.00184, 0.00184],
        [12014, 1, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183],
        [12014, 2, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183],
        [12014, 3, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183],
        [12014, 4, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183],
        [12014, 5, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183]
]
bookdf = pd.DataFrame(data, columns = cols)

Desired Output:

cols = ['id', 'cur_age', 'EGI0', 'EXP0', 'PEGI', 'PExp', 'gEGI', 'TotExp', 'position', 'check', 'egix', 'expx']
data = [[12003, 1, 446499.51923, 23.76, np.nan, np.nan, 0.00228, 0.00228, 0, 446499.51923],
        [12003, 2, 446499.51923, 32.76, np.nan, np.nan, 0.00228, 0.00228, 1, 447517.89163],
        [12003, 3, 446499.51923, 214319.76, np.nan, np.nan, 0.00183, 0.00183, 2, 448338.21855],
        [12003, 4, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183, 3, 449160.04918],
        [12003, 5, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183, 4, 449983.38628],
        [12003, 6, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183, 5, 450808.23260],
        [12003, 7, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183, 6, 451634.59091],
        [12003, 8, 446499.51, 214319.76, np.nan, np.nan, 0.00183, 0.00183, 7, 452462.46399],
        [12003, 9, 446499.51, 214319.76, np.nan, np.nan, 0.00184, 0.00184, 8, 453294.43921],
        [12003, 10, 446499.51, 214319.76, np.nan, np.nan, 0.00184, 0.00184, 9, 454127.94424],
        [12014, 1, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183, 0, 163392.40385],
        [12014, 2, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183, 1, 163765.06788],
        [12014, 3, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183, 2, 164065.25900],
        [12014, 4, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183, 3, 164366.00038],
        [12014, 5, 163392.40, 78428.35, np.nan, np.nan, 0.00183, 0.00183, 4, 164667.29304]
]
bookdf = pd.DataFrame(data, columns = cols)

Working code in Pandas which works on small dataset only:

# 'check' column is being created to get first row of each  grouped data w.r.t 'id' column. 
# I need to take to take first row of each group and do the below calculation for rest of the rows of each group but ```bookdf.group('id).first()```  is not working with the below calculation which basically retains the last value and do the math.

bookdf['check'] =  bookdf.groupby(bookdf['id']).cumcount()
bookdf['egix']  = np.where((bookdf.check==0) & (bookdf.PEGI>0), bookdf.PEGI, bookdf.EGI0)
bookdf['expx']  = np.where((bookdf.check==0) & (bookdf.PExp>0), boodf.PExp, bookdf.EXP0)
for ind in bookdf.index:
    if boo1df['check'][ind]!=0:
        bookdf['egix'][ind] = bookdf['egix'][ind-1]*(1 + bookdf['gEGI'][ind])
        bookdf['expx'][ind] = bookdf['expx'][ind-1]*(1 + bookdf['TotExp'][ind])

If I try to run the same code with dask dataframe, It's giving below mentioned error:

for ind in range(0, len(book1df)):
    if boo1df['check'][ind]!=0:
        bookdf['egix'][ind] = bookdf['egix'][ind-1]*(1 + bookdf['gEGI'][ind])
        bookdf['expx'][ind] = bookdf['expx'][ind-1]*(1 + bookdf['TotExp'][ind])

**Error** : Series getitem in only supported for other series objects with matching partition structure.

Is there any way to implement this in dask dataframe or another best way to get the desired output with large Dataset.
dask-dataframe dask-distributed dask pandas python
3 Answers

One option is to get rid of the loop altogether.

# this creates the mask of interest
mask = bookdf['check'] != 0

# now we can apply the mask with .loc
bookdf.loc[mask, 'egix'] = bookdf.loc[mask, ['egix']].shift(-1) * (bookdf.loc[mask, ['gEGI']])

bookdf.loc[mask, 'expx'] = bookdf.loc[mask, ['expx']].shift(-1) * (bookdf.loc[mask, ['TotExp']])

This should work for both pandas and dask dataframes.

user_106935960

Since you need to access the previous values of 'egix' and 'expx' in each group, create two new columns to store these so that your calculations are done efficiently. And then use the apply method across all the columns of the df:

bookdf['egix_prev'] = bookdf.groupby('id')['egix'].shift(1)
bookdf['expx_prev'] = bookdf.groupby('id')['expx'].shift(1)

bookdf['egix'] = bookdf.apply(lambda x:x['egix_prev']*(1+x['gEGI']) if x['check']!=0 else x['egix'],axis=1)
bookdf['expx'] = bookdf.apply(lambda x:x['expx_prev']*(1+x['TotExp']) if x['check']!=0 else x['expx'],axis=1)
user_93613910

If you want to efficiently iterate on rows of a large table, it is almost always better to use df.iterrows(). The code below should be about 20x faster than using an external for loop.

row_list = []
for row in bookdf.iterrows():
    if row[1]['check']!=0:
        row[1]['egix'] = row_list[-1][1]['egix']*(1 + row[1]['gEGI'])
        row[1]['expx'] = row_list[-1][1]['expx']*(1 + row[1]['TotExp'])
    row_list.append(row)
rebuilt_df = pd.DataFrame([row[1] for row in row_list])
user_93613910
Related