Python: Merge/Combine two dataframe under multiple conditions listed

Question

Have got two dataframe which look like below: grpdf

item        space   ID      row_no      item_order  aisle_side
Apple       0.25    3       Row 1           1           Front
Apple       0.25    3       Row 1           1           Front
Lime        0.125   3       Row 1           3           Front
Grape       0.25    3       Row 1           6           Front       
Grape       0.25    3       Row 1           6           Front
Kiwi        0.125   3       Row 1           7           Front
PP Orange   0.25    3       Row 1           8           RightEnd
PP Orange   0.25    3       Row 1           8           RightEnd
PP Kiwi     0.125   3       Row 1           9           Back
PP Apple    0.125   3       Row 1           10          Back

deddf

Store   Aisle       TableNo     TableBit    dedicated_table     Direction   
11      33              1           1                           RightEnd    
11      33              1           2                           RightEnd    
11      33              2           1       Nuts                Front
11      33              2           2                           Front           
11      33              2           3                           Front           
11      33              3           1                           Front       
11      33              3           2                           Front       
11      33              3           3                           Front       
11      33              4           1                           Back        
11      33              4           2                           Back        
11      33              4           3                           Back

Scenario: Need to combine 'grpdf' dataframe with 'deddf' dataframe under certain conditions which leads to the resultant dataframe 'resdf'.

Note: Have provided sample data for single Store '11' and single row_no 'Row 1' and single Aisle '33'. But the original dataset of mine contains multiple of these which follows the same condition as well.

Condition 1: Have to first combine Item having prefix 'PP %' considering in the order of item_order. This to be done under two conditions as below.

Cond 1.1: To check whether any 'PP %' item having aisle_side like '%End' in dataframe grpdf. If so, that item's row has to be combined first with deddf with same value in Direction column the number of times it occurs in grpdf.

Cond 1.2: The remaining 'PP %' items must always be combined with deddf only with Direction = 'Back' in the reverse order of same Aisle,TableNo,TableBit.

Condition 2: Then the remainingitems left out in grpdf need to be combined with deddf starting from the Direction = 'Front' w.r.t. Aisle,TableNo,TableBit ascending order. If no table left out in Direction='Front' for remainingitems, those can take up table from left out Direction='Back'.

Condition 3: Only Rows with deddf['dedicated_table'] == '' to be considered for combining grpdf rows. If deddf['dedicated_table'] got any value in it that row, that row need to be neglected as in resdf below.

On applying all these condition, below is the resultant dataframe resdf.

Store   Aisle       TableNo     TableBit    dedicated_table     Direction   item        space   ID  row_no  item_order
11      33              1           1                           RightEnd    PP Orange   0.25    3   Row 1   8
11      33              1           2                           RightEnd    PP Orange   0.25    3   Row 1   8
11      33              2           1       Nuts                Front
11      33              2           2                           Front       Apple       0.25    3   Row 1   1               
11      33              2           3                           Front       Apple       0.25    3   Row 1   1               
11      33              3           1                           Front       Lime        0.125   3   Row 1   3
11      33              3           2                           Front       Grape       0.25    3   Row 1   6
11      33              3           3                           Front       Grape       0.25    3   Row 1   6
11      33              4           1                           Back        Kiwi        0.125   3   Row 1   7
11      33              4           2                           Back        PP Apple    0.125   3   Row 1   10
11      33              4           3                           Back        PP Kiwi     0.125   3   Row 1   9

Tried below code but no idea on how to apply those conditions.

idx = deddf[deddf['dedicated_table'] == ''].index
if (idx.nunique() == grpdf.shape[0]) and (idx.nunique()!=0) and (grpdf.shape[0]!=0):
    output = pd.concat([deddf, grpdf.set_axis(idx[:len(grpdf)])], axis=1)

Any help will be much appreciated as it's really hard time for me to figure out this.

qaziqarta · Answer

You can add some helper and filter functions, get dataframe for each condition separately, and then combine them all in one resdf:

# First let's rename `aisle_side` to `Direction` cause it's easier to merge on common named columns
grpdf = grpdf.rename(columns={'aisle_side': 'Direction'})

# Define filter functions
is_pp = grpdf['item'].str.contains('^PP .*')
is_end = lambda x: x['Direction'].str.contains('.*End$')
not_dedicated = deddf['dedicated_table'].isna()
is_front = lambda x: x['Direction'] == 'Front'

# Define helper function to generate row numbers
rn = lambda x: range(1, 1+len(x))

# Now what follows is your logic defined in terms of pandas code with the help of filter functions.
cond_1_1 = (
  grpdf
  .loc[lambda x: is_pp & is_end(x)]
  .assign(TableBit=rn)
  .merge(
    deddf[lambda x: not_dedicated & is_end(x)], 
    on=['Direction','TableBit'], 
    how='left'))

cond_1_2 = (
  grpdf
  .loc[lambda x: is_pp & ~is_end(x)]
  .assign(TableBitR=rn)
  .merge(
    deddf.loc[not_dedicated & (deddf['Direction']=='Back')].assign(TableBitR=lambda x: rn(x)[::-1]), 
    on=['Direction','TableBitR'], 
    how='left'))

cond_2 = (
  grpdf
  .loc[~is_pp]
  .assign(rn=rn)
  .merge(
    deddf.loc[not_dedicated & (deddf['Direction'].isin(['Front','Back']))].assign(rn=rn), 
    on='rn', 
    suffixes=('','_y'), 
    how='left'))

cond_3 = deddf.loc[~not_dedicated].reindex(columns=deddf.columns.append(grpdf.columns.drop('Direction')))

# At this stage, the columns from all condition dataframes must be the same, only order differs.
# We stick to one common column order here:
columns = cond_3.columns

# Concat all the results and sort the resulting dataframe:
resdf = (
  pd.concat([
              cond_1_1[columns],
              cond_1_2[columns],
              cond_2[columns],
              cond_3
            ])
  .sort_values(['Store', 'Aisle', 'TableNo', 'TableBit']))

#    Store  Aisle  TableNo  TableBit dedicated_table Direction       item  space   ID row_no  item_order
# 0     11     33        1         1             NaN  RightEnd  PP Orange  0.250  3.0  Row_1         8.0
# 1     11     33        1         2             NaN  RightEnd  PP Orange  0.250  3.0  Row_1         8.0
# 2     11     33        2         1            Nuts     Front        NaN    NaN  NaN    NaN         NaN
# 0     11     33        2         2             NaN     Front      Apple  0.250  3.0  Row_1         1.0
# 1     11     33        2         3             NaN     Front      Apple  0.250  3.0  Row_1         1.0
# 2     11     33        3         1             NaN     Front       Lime  0.125  3.0  Row_1         3.0
# 3     11     33        3         2             NaN     Front      Grape  0.250  3.0  Row_1         6.0
# 4     11     33        3         3             NaN     Front      Grape  0.250  3.0  Row_1         6.0
# 5     11     33        4         1             NaN     Front       Kiwi  0.125  3.0  Row_1         7.0
# 1     11     33        4         2             NaN      Back   PP Apple  0.125  3.0  Row_1        10.0
# 0     11     33        4         3             NaN      Back    PP Kiwi  0.125  3.0  Row_1         9.0

Python: Merge/Combine two dataframe under multiple conditions listed

1 Answers