import pandas as pd
from datetime import datetime

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

'remove uneeded rows'
df = df[df.Trans != 'GROWRedemption']
df = df[df.Trans != 'GROWApplication']

# maybe adjust values to negative if not already ??
def calc_Amt(row):
if 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'] == '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()
#create an Action column depending on sign
def calc_new_col(row):
if row['Amount'] <0:
return 'Member Outflow Switch'
else:
return 'Member Inflow Switch'

dfz["Action"] = dfz.apply(calc_new_col, axis=1)
dfg = dfz[['Option','Action','EDate','Amount','Units','Curr','Sector']]
#tidy up format
dfg['EDate']= pd.to_datetime(dfg['EDate'])
dfg['EDate']=dfg['EDate'].dt.strftime('%Y/%m/%d')
dfg['Amount'] = abs(dfg['Amount'])
dfg['Units'] = abs(dfg['Units'])
print(dfg)