logo
down
shadow

Merge table by aggregating a 3 year average from second df


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)

 head(res[,-4],2)
 #   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 : facebook icon twitter icon
want to insert average values in to average table only year 2008 from regular values

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

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++?

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

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

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


By : Melanie
Date : March 29 2020, 07:55 AM
I wish this help you It's VERY simple.
Drag your date field to columns, and the measure you want to plot on rows.
Related Posts Related Posts :
  • get first entries in rows of list?
  • Conditionally removing rows from a matrix in R
  • Using a loop to find P(-1.5<Y<1.5) for a range of sample sizes
  • R-Count and list the maximum count row by row
  • Include Iverson Bracket in R documentation
  • update a data frame and environment in R
  • How to write dynamic cumulative multiple in R
  • format time using as.POSIX in R
  • Change the class of multiple columns
  • Remove period and spaces within column headings nested in a list of data frames
  • R: error message --- package error: "functionName" not resolved from current namespace
  • labels with geom_text ggplot2
  • Passing mongodb ISODate in R
  • Importing "csv" file with multiple-character separator to R?
  • Change row names of a table obtained from a lm regression using xtable function
  • R language iterate over R object
  • How do you delete the header in a dataframe?
  • Re coding in R using complicated statement
  • accumulating functions and closures in R
  • How do you combine two columns into a new column in a dataframe made of two or more different csv files?
  • Twitter authentication fails
  • Summing Values of One Vector Conditional on Values of Another Vector
  • draw cube into 3D scatterplot in RGL
  • lme4 translate formula to code in 3-level model
  • How to draw single axis plot in R
  • Combine geom_tile() and facet_grid/facet_wrap and remove space between tiles (ggplot2)
  • Use snpStats with R version 3.0.1
  • Makefile gives strange error while compiling markdown file into .docx file
  • How to determine whether a points lies in an ellipse
  • Summarize data already grouped in r
  • Is the bigvis package for R not available for R version 3.0.1?
  • Operator overloading in R reference classes
  • How to enable user to switch between ggplot2 and gVis graphs in R Shiny?
  • Is there an easy way to separate categorical vs continuous variables into two dataset in R
  • Correct previous year by id within R
  • Installation of rdyncall package for R
  • ggplot2 plot that evaluates the percentage and mean of a third variable at intersecting points
  • Error Handling with Lapply
  • data.table - split multiple columns
  • How to compute the overall mean for several files in R?
  • R: Graph Plotting: Subscripts in the legend like LaTeX
  • Restructuring data in R
  • Distance of pointsfrom cluster centers after K means clustering
  • R incorrect value of date function
  • Package "Imports" not loading in R development package
  • r - run a user defined function several times by taking column elements as parameters
  • Create input$selection to subset data AND radiobuttons to choose plot type in Shiny
  • Generate crosstabulations from dataframe of categorical variables in survey
  • Restructure output of R summary function
  • New behavior in data.table? .N / something with `by` (calculate proportion)
  • search certain number vector in R
  • R version doesn't support quartz graphic device - RStudio won't plot
  • Referencing a function parameter in R
  • How to synchronize signals using a cross-correlation and FFT in R?
  • Plotting coefficients and corresponding confidence intervals
  • passing expressions to curve() within a function
  • More effective merging of matched column with duplicates in data.table
  • Easy way to export multiple data.frame to multiple Excel worksheets
  • R Foreach Iterator - Walkforward
  • Table format and output in R
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org