0

I'm trying to create the total column in Pandas. How it is calculated is as follows - If nb_months = 1 then take the trans_amt as the value if not set to 0.

Trans_amt Nb_months Total
12 1 12
0 5 0
0 7 0
24 9 0

df['Total'] = [df['Trans_amt '] if x == 1 else 0 for x in df['nb_months']]

My code above returns the entire trans_amt series in the total column. Any help would be great.

Himansu Odedra
  • 71
  • 7

1 Answers1

1
df['Total'] = np.where(df['nb_months'] == 1,df['Trans_amt'] , 0)

syntax:

np.where(condition, True, False)

Piotr Żak
  • 2,046
  • 5
  • 18
  • 30
  • How would I have multiple conditions ? thank you – Himansu Odedra Oct 08 '21 at 10:06
  • 1
    https://numpy.org/doc/stable/reference/routines.logic.html – Piotr Żak Oct 08 '21 at 10:07
  • 1
    np.logical_and() - and add those conditions inside – Piotr Żak Oct 08 '21 at 10:08
  • 1
    @HimansuOdedra - Check [dupe](https://stackoverflow.com/questions/19913659/pandas-conditional-creation-of-a-series-dataframe-column). – jezrael Oct 08 '21 at 10:10
  • Thank you - is there anyway to groupby and sum by different columns. For example, total (based off of 1 month) total_1 (based off of 2 month) etc. Sum these columns and produce an array with account number and sum(total_1) and sum(total_2) – Himansu Odedra Oct 08 '21 at 10:30
  • sure - there is function group_by - it's often used - also there is agg function: https://pandas.pydata.org/docs/reference/api/pandas.core.groupby.DataFrameGroupBy.aggregate.html – Piotr Żak Oct 08 '21 at 10:31
  • If U provide your data as df - i can group it – Piotr Żak Oct 08 '21 at 10:31
  • df = pd.DataFrame({'ID':[123,123,123,123], 'trans_amt':[12,0,0,24], 'Nb_months':[1,5,7,9]}) df['OL_FEE_1'] = np.where(df['Nb_months'] == 1,df['trans_amt'] , 0) df['OL_FEE_3'] = np.where((df['Nb_months'] <= 3) & (df['Nb_months'] != 0) & (df['Nb_months'] > 0),df['trans_amt'] , 0) df['OL_FEE_6'] = np.where((df['Nb_months'] <= 6) & (df['Nb_months'] != 0) & (df['Nb_months'] > 0),df['trans_amt'] , 0) I need one row which has the ID and the sum of each of the OL columns: 12,12,12 – Himansu Odedra Oct 08 '21 at 10:38
  • 1
    grouped = df.groupby(['ID'])["OL_FEE_1", "OL_FEE_3", "OL_FEE_6"].sum() – Piotr Żak Oct 08 '21 at 10:45