Adding Several Rows at Once Composed of Conditional Formulas

[Micaela]

I am attempting to add new rows to a data frame with column values that are calculated by using other data from different locations on the same date within my data frame (if possible). A simplified example of my data frame is provided below (I basically have monthly data for seven stations over 10 years, monthly flows (SAC, YOLO, ect) are constant each month and the same for every station). I want to add monthly data for new stations that are calculated using regression equations from the other stations. For example my new rows would include:

Station = C9
Month = same as D19 & D28 data
Year = same as D19 & D28 data
DissAmmonia = 0.43 * D19(DissAmmonia) + 0.03
DisssNitrateNitrate = 0.76 * D28A(DisssNitrateNitrate) + 0.13
TN = 0.73 *D28A(TN) + 0.30
SAC = same for month, year
YOLO = same for month, year

NMassBalance:

Station Month Year DissAmmonia DissNitrateNitrite TN SAC YOLO ...
C3A 10 2009 0.5 0.25 0.2 1000 2000
C10A 10 2009 0.4 0.28 0.1 1000 2000
MD10A 10 2009 0.5 0.25 0.2 1000 2000
D28A 10 2009 0.4 0.28 0.1 1000 2000
D19 10 2009 0.5 0.25 0.2 1000 2000
D4 10 2009 0.4 0.28 0.1 1000 2000
D26 10 2009 0.5 0.25 0.2 1000 2000
C3A 11 2009 0.4 0.28 0.1 1500 800
C10A 11 2009 0.5 0.25 0.2 1500 800
MD10A 11 2009 0.4 0.28 0.1 1500 800
D28A 11 2009 0.5 0.25 0.2 1500 800
D19 11 2009 0.4 0.28 0.1 1500 800
D4 11 2009 0.5 0.25 0.2 1500 800
D26 11 2009 0.4 0.28 0.1 1500 800
. . .
. . .
. . .

I am attempting to write some kind of if statement or filter that generates a row for each month of each year with other representative station data.

[Micaela]

If it is easier I already have written code to break this data frame into water years, in which case only 12 rows would have to be calculated and added for Station C9 (one for each month), I would just have to apply it to each water year data frame individually. (I also have equations for 3 other stations I need to write similar code for).

[DavoWW]

Hi @Micaela,
This problem certainly looks like it can be solved but the example you give is not clear. What links the new Station "C9" with Stations "D19", "D28", and "D28A" to be used in the calculations? There are multiple matches for the Month/Year combinations so that doesn't help provide a unique link. Also, "D28" does not appear in your sample data. Are the new Stations in a second data frame that can be used to merge the two and do the calculations?

HTH

[Micaela]

Excuse me, that was a typo. The only stations that need to be linked are "C9", "D19", and "D28A". They can be linked by both the month and year as each station has one sampling event per month per year. I just erased my most recent try but I am essentially trying to write an if statement that generates a new row based on the presence of data for "D19", repeating all data entries except for the three calculated variables.

[Micaela]

Also, the "SAC", "YOLO", and other monthly flow values can be added in after generating the new station data from the regression equations. I did a simple right join based on Month and Year from a different data frame to add this information in.