C RUBY-ON-RAILS MYSQL ASP.NET DEVELOPMENT RUBY .NET LINUX SQL-SERVER REGEX WINDOWS ALGORITHM ECLIPSE VISUAL-STUDIO STRING SVN PERFORMANCE APACHE-FLEX UNIT-TESTING SECURITY LINQ UNIX MATH EMAIL OOP LANGUAGE-AGNOSTIC VB6 MSBUILD

# Merge table by aggregating a 3 year average from second df

By : user2949162
Date : November 16 2020, 06:23 AM
it fixes the issue I am modelling the effects of a variety of variables on the growth of fish within a particular haul. I want to calculate a three year average catch rate for each haul based on its geographical grid location. I need a formula to take the 'Stat_sq' (geographic grid square) and the 'Year' of the fishing trip (contained in the first data frame) and generate an average catch rate for the three preivous years by aggregating the mean catch rates by 'Stat_sq' and 'Year' from the second data frame. , May be this helps
code :
`````` df1\$Yr <- with(df1, as.numeric(ifelse(as.numeric(Year)>=93,
paste0(19,Year), paste0(20,Year))))

df2\$Yr <-  with(df2, as.numeric(ifelse(as.numeric(Year)>=93,
paste0(19,Year), paste0(20,Year))))

res <-   unsplit(lapply(split(df1, df1\$Stat_sq),
function(x) {
x1 <- df2[df2\$Stat_sq %in% unique(x\$Stat_sq),]
x\$Avg <- sapply(seq_len(nrow(x)), function(i) {
x2 <- x[i,]
indx <- x1\$Yr %in% seq(x2\$Yr-2, x2\$Yr)
if(length(indx)>0) mean(x1\$Catch_Rate[indx], na.rm=TRUE)
else NA})
x}),
df1\$Stat_sq)

#   Trip_Id Stat_sq Year     Avg
#1  0113A_1    48E8   13 10.5679
#2 0113A_10    49E8   13      NA
``````
`````` df1 <- structure(list(Trip_Id = c("0113A_1", "0113A_10", "0113A_11",
"0113A_12", "0113A_13", "0113A_15", "0113A_16", "0113A_18", "0113A_19"
), Stat_sq = c("48E8", "49E8", "49E8", "49E8", "49E8", "49E8",
"49E8", "50E8", "50E8"), Year = c("13", "13", "12", "13", "12",
"11", "10", "13", "12")), .Names = c("Trip_Id", "Stat_sq", "Year"
), class = "data.frame", row.names = c(NA, -9L))

df2 <- structure(list(Stat_sq = c("48E8", "48E8", "48E8", "42E8", "50E8",
"50E9", "50E8", "44E7", "44E8", "45E6", "45E7", "45E8"), Year = c("13",
"12", "11", "10", "13", "12", "11", "00", "00", "00", "00", "00"
), Catch_Rate = c(12.353719, 16.508482, 2.841493, 12.721584,
12.419484, 22.461538, 28.141433, 29.68111, 5.42743, 13.25977,
18.250903, 62.222222)), .Names = c("Stat_sq", "Year", "Catch_Rate"
), class = "data.frame", row.names = c(NA, -12L))
``````

Share :

## want to insert average values in to average table only year 2008 from regular values

By : Benjamin Brtton
Date : March 29 2020, 07:55 AM
I hope this helps . want to insert average values in to average table only year 2008 from regular values table as a average but code has error how i overcome. without "where year(DATE_FORMAT(date, '%y-%m-%d'))= 2008" query work well , Simply do
code :
``` ```
WHERE year(date)= 2008
GROUP BY year(date), month(date)
``````
``````
GROUP BY year(str_to_date(date, '%Y-%m-%d')),
month(str_to_date(date, '%Y-%m-%d'))
WHERE year(DATE_FORMAT(date, '%y-%m-%d'))= 2008
``````

## Merge two rows in a same table after calculating average values

By : Laha Ale
Date : March 29 2020, 07:55 AM
Hope this helps I have data in my table named prices as below; , You seem to be asking for:
code :
``````select ID, avg(E5) as E5, avg(E10) as E10, avg(DIESEL) as DIESEL
from prices
group by ID;
``````

## How do I calculate an updating average speed for each year in a table using c++?

By : Lukas Lohse
Date : March 29 2020, 07:55 AM
will help you I think a good way to think about it is that you will have to print the table row by row. So your first for loop seems to be doing that.
In each row, you have to, first, print the year (starting with the current year) up until the maximum year. Making the first for loop iterate over the years is a good choice (i.e. making i go from 2018 until maxYears + 2018). Second, you have to print the speed for each year after calculating the improvement via the provided equation. (I'm assuming that in the problem description it was given that the first speed is 10000? If not, what is the starting value?) Because you will only print a number, you don't need a second for loop. Just calculate the new speed and print it. As for the third and fourth column, I'm not sure what is asked exactly, so for now it will be blank in the code.
code :
``````#include <iostream>
//--1
int main()
{
//--2
const float speedLight = 299792;
const int startingYear = 2018;
//--3
float percentIncrease = 0;
while ((percentIncrease <= 0) || (percentIncrease >= 100))
{
std::cout << "What percentage do rocket speeds increase by each year?" << std::endl;
std::cin >> percentIncrease;
}
//--4
int maxYears = -1;
while (maxYears < 1)
{
std::cout << "What is the maximum number of years you are willing to wait on earth before you leave? " << std::endl;
std::cin >> maxYears;
}

std::cout << "Year|\tAvg Speed|\tEarth ETA|\tYour ETA" << std::endl;
//--5
float currentSpeed = 10000;
for (int year = startingYear; year <= (maxYears + startingYear); ++year)
{
//--6
std::cout << year << "\t" << currentSpeed << std::endl;
currentSpeed = currentSpeed + (speedLight - currentSpeed) * (percentIncrease / 100);
}
//--7
system("pause");
return 0;
}
``````

## SQL Server - get last year average, current year monthly average, and current year total average

By : Bernat Orellana
Date : March 29 2020, 07:55 AM
hope this fix your issue I'm running a simple query in SQL Server to get current year average grouped by month, so it returns me 12 rows. I need to put in the first row, the TOTAL average from last year, and in the last row, the TOTAL average from current year, so it would become 14 rows. , Try in this way:
code :
``````  SELECT COUNT(*) AS mes, null
FROM WorkOrder
WHERE workOrderSource = '02'
AND YEAR(workOrderDate) = 2018
AND conclusionDate IS NULL
UNION ALL
SELECT COUNT(*), MONTH(workOrderDate)
FROM WorkOrder
WHERE workOrderSource = '02'
AND YEAR(workOrderDate) = 2019
AND conclusionDate IS NULL
GROUP BY ROLLUP(MONTH(workOrderDate))
ORDER BY COALESCE(MONTH(workOrderDate), 13)
``````

## Tableau - Current year, moving average and previous year figures, for the last 2 months

By : Melanie
Date : March 29 2020, 07:55 AM