Search code examples
pythonpandasdataframeif-statementmultiple-columns

How to get values from a Column in pandas if another column in same dataframe matches a condition?

     Tax_Amount Rate    SGST    CGST    IGST    TDS Tax_Term

0   5697.0  9.0 NaN NaN NaN NaN CGST
1   954.0   9.0 NaN NaN NaN NaN TDS
2   1305.0  9.0 NaN NaN NaN NaN CGST
3   2724.0  9.0 NaN NaN NaN NaN SGST
4   18000.0 9.0 NaN NaN NaN NaN IGST

i Have a Dataframe as above, I want to have the Tax_Amount in all respective column if it matches the Colum Tax_Term. Expected Output:-

    Tax_Amount  Rate    SGST    CGST    IGST    TDS Tax_Term
0   5697.0  9.0 NaN 5697.0  NaN NaN CGST
1   954.0   9.0 NaN 954.0   NaN 954.0   TDS
2   1305.0  9.0 NaN 1305.0  NaN NaN CGST
3   2724.0  9.0 2724.0  NaN NaN NaN SGST
4   18000.0 9.0 NaN NaN 18000.0 NaN IGST

I tried doing the same with below code however i didn't get the desired result.

final_df['SGST'] = final_df.query('Tax_Term == SGST')['Tax_Amount']
final_df['CGST'] = final_df.query('Tax_Term == CGST')['Tax_Amount']
final_df['IGST'] = final_df.query('Tax_Term == IGST')['Tax_Amount']
final_df['TDS'] = final_df.query('Tax_Term == TDS')['Tax_Amount']

any help will be appretiated. Thanks.

Solution

  • If there are few values in the Tax_Term column, you can simply use a series of assignments using .loc:

    final_df.loc[final_df['Tax_Term'] == 'CGST','CGST'] =final_df['Tax_Ammount']
final_df.loc[final_df['Tax_Term'] == 'TDS', 'TDS'] = final_df['Tax_Ammount']
final_df.loc[final_df['Tax_Term'] == 'SGST', 'SGST'] = final_df['Tax_Ammount']
final_df.loc[final_df['Tax_Term'] == 'IGST', 'IGST'] = final_df['Tax_Ammount']

    Or, you can create a loop that iterates over unique values in Tax_term:

    for term in final_df.Tax_Term.unique():
    final_df.loc[final_df['Tax_Term'] == term,
                 term] = final_df['Tax_Ammount']