import pandas as pd

df = pd.read_csv('D:\Python\GROW NoHeads.csv',
names = ['HESTA','Instruction','C','D','Option','Trans','EDate','Amount','Units','Curr','Sector'])

# maybe adjust values to negative if not already ??
def calc_Amt(row):
if row['Trans'] == 'GROWRedemption' or row['Trans'] == 'GROWSwitchOut':
return row['Amount'] * -1
else: return row['Amount']

df["Amount"] = df.apply(calc_Amt, axis=1)

def calc_Unit(row):
if row['Trans'] == 'GROWRedemption' or row['Trans'] == 'GROWSwitchOut':
return row['Units'] * -1
else:
return row['Units']

df["Units"] = df.apply(calc_Unit, axis=1)

dfg = df.groupby(['Option','EDate','Sector','Curr']).sum()
dfz = dfg.reset_index()
dfz['Fund']= 'HESTA'
dfz['Inst']= 'Instruction'
dfz['C']= ""
dfz['D']= ""

#create an Action column depending on sign
def calc_new_col(row):
if row['Amount'] <0:
return 'Member Outflow'
else:
return 'Member Inflow'
dfz["Action"] = dfz.apply(calc_new_col, axis=1)

dfg = dfz[['Fund','Inst','C','D','Option','Action','EDate','Amount','Units','Curr','Sector']]
# maybe convert to absolute values
dfg['Amount'] = abs(dfg['Amount'])
dfg['Units'] = abs(dfg['Units'])
print(dfg)
#fg.to_csv('D:\Python\GROW\GROW File1A.csv',header=False, index=False)