CODEWITHSUNDEEP
pandasdataframeconditional-statements
Md Rahat Mahmud
Md Rahat Mahmud

Reputation: 23

Conditional mapping among columns of two data frames with Pandas Data frame

I needed your advice regarding how to map columns between data-frames:

I have put it in simple way so that it's easier for you to understand:

df = dataframe

EXAMPLE:

df1 = pd.DataFrame({
        "X": [],
        "Y": [],
        "Z": []
    })
df2 = pd.DataFrame({
        "A": ['', '', 'A1'],
        "C": ['', '', 'C1'],
        "D": ['D1', 'Other', 'D3'],
        "F": ['', '', ''],
        "G": ['G1', '', 'G3'],
        "H": ['H1', 'H2', 'H3']
    })

Requirement:

1st step:

We needed to track a value for X column on df1 from columns A, C, D respectively. It would stop searching once it finds any value and would select it.

2nd step:

If the selected value is "Other" then X column of df1 would map columns F, G, and H respectively until it finds any value.

Result:

    X
0   D1
1   H2
2   A1

Thank you so much in advance

Upvotes: 1

Views: 80

Answers (1)

Code Different
Code Different

Reputation: 93181

Try this:

def first_non_empty(df, cols):
    """Return the first non-empty, non-null value among the specified columns per row"""
    return df[cols].replace('', pd.NA).bfill(axis=1).iloc[:, 0]
    
col_x = first_non_empty(df2, ['A','C','D'])
col_x = col_x.mask(col_x == 'Other', first_non_empty(df2, ['F','G','H']))
df1['X'] = col_x

Upvotes: 1

Related Questions