I have a dataset that looks as follows:
# Entity Entity Descr Scope TE CASH AR AP
1 1000 Lab S 100 120 30 10
2 1001 XYZ LLC R 50 20 40 70
The above is simplified, but there are about 40 fields in the actual dataset that continue after "AP". After each of the account columns (i.e. Cash, AR, AP, etc) I need to add a Scope Calculation column that designates what bucket the account will fall under (either A or T) based on the details in "scope" and "TE" columns.
The following are the rules for these new columns:
-Any row where the Scope Field = "R" or "O" would be always be assigned "A"
-Any row where the Scope Field = "F", would always be assigned "T"
-For rows where scope is "S" or "SP", the designation would be "A" if the value of the account is under "TE", and would be "T" if the value of account is above "TE"
So the following would be the end result of the simplified example. Could someone please help me figure out the easiest way to do this?
# Entity Entity Descr Scope TE CASH AR AP
1 1000 Lab S 100 120 T 30 A 10 A
2 1001 XYZ LLC R 50 20 A 40 A 70 A
