logo
down
shadow

R - count maximum number of consecutive dates


R - count maximum number of consecutive dates

By : user2173624
Date : October 20 2020, 08:10 AM
wish helps you Let's first make a function that would find the maximum number of consecutive days, given a vector:
code :
gl <- function(x) {
  y <- c(unclass(diff(x)))  # c and unclass -- preparing it for rle
  r <- rle(y)
  with(r, max(lengths[values==1]))
}
df %>% group_by(key) %>% summarise(max.consecutive = gl(Date))

#  A tibble: 2 x 2
#     key max.consecutive
#   <int>           <int>
# 1     1               2
# 2     2               3


Share : facebook icon twitter icon
How do you count the number of consecutive dates in a table?

How do you count the number of consecutive dates in a table?


By : mccuenbr
Date : March 29 2020, 07:55 AM
it fixes the issue Here is an SQL only solution that I thought was really cool. Assuming the dates in your table are unique (not that it would be too hard to just group on them) you can use the method adapted from here http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. I ran through the example and there are some syntax errors, so hopefully I didn't repeat them in my answer below. I probably used some reserved keywords, so you may need to adapt that.
First create a table of Dates that is significantly large to cover your needs. I'm not sure what the best method for SQLite is, but in SQL Server you can insert integers into a Datetime field and it will do an implicit conversion of integers to dates. There are many methods to insert integers into tables...
code :
SELECT Dates.Date,
       CASE 
         WHEN StreakTable.DATE IS NULL THEN 0 
         ELSE 1 
       END AS Result 
FROM   Dates 
       LEFT JOIN StreakTable 
         ON Dates.DATE = StreakTable.DATE 
WHERE  Dates.DATE BETWEEN (SELECT MIN(DATE) AS MinDate 
                           FROM   StreakTable) AND (SELECT MAX(DATE) AS MaxDate 
                                                    FROM   StreakTable) 
SELECT Date, 
       Result, 
       (SELECT COUNT(*) 
        FROM   (SQL 0) S 
        WHERE  S.Result <> SV.Result 
               AND S.GameDate <= SV.GameDate) AS RunGroup 
FROM   (SQL 0) SV 
SELECT Result, 
       MIN(Date) AS StartDate, 
       MAX(Date) AS EndDate, 
       COUNT(*)      AS Days 
FROM   (SQL 1) A 
GROUP  BY Result, 
          RunGroup 
ORDER  BY MIN(Date) 
SELECT TOP 1 * 
FROM   (SQL 2) A 
WHERE  Result = 1 
ORDER  BY Games DESC 
SELECT *  
FROM (SQL2) A 
WHERE EndDate = (SELECT Max(Date) 
FROM Streak)
SELECT Result, 
  COUNT(*) as NumberOfStreaks 
FROM (SQL 2) A 
GROUP BY Result 
WHERE Days >= 3
how to get maximum consecutive row count using sql

how to get maximum consecutive row count using sql


By : CollisionOfConcepts
Date : March 29 2020, 07:55 AM
help you fix your problem You can get the maximum consecutive row count by subtracting a sequence number from row_number and assigning a group. For instance, the following gets information about all consecutive values in the data:
code :
select group_name, yes_no, min(row_number), max(row_number), count(*)
from (select t.*,
             (row_number - row_number() over (partition by group_name, yes_no
                                              order by row_number)
             ) as grp
      from table t
     ) t
group by group_name, grp, yes_no;
insert into table(group_name, row_number, yes_no)
    select group_name, maxrn + 1, max(cnt)
    from (select group_name, yes_no, count(*) as cnt, max(row_number) as maxrn
          from (select t.*,
                       (row_number - row_number() over (partition by group_name, yes_no
                                                        order by row_number)
                       ) as grp
                from table t
               ) t
          group by group_name, grp, yes_no
         ) t
    group by group_name
   ) t
Count the maximum date between consecutive dates and corresponding value using 3 columns

Count the maximum date between consecutive dates and corresponding value using 3 columns


By : Alex0101
Date : March 29 2020, 07:55 AM
like below fixes the issue I have this data: , You can try the following:
code :
library(plyr)

maxWindDate <- function(row) {
  in.range <- row$Dates <= df$Dates & df$Dates - row$duration < row$Dates
  winds <- df$Wind[in.range]
  dates <- df$Dates[in.range]
  if (length(winds) == 0) {
    data.frame(maxWind=NA, maxDate=NA)
  } else {
    maxWind <- max(winds)
    maxDate <- dates[which(winds == maxWind)[1]]
    data.frame(maxWind=maxWind, maxDate=maxDate)
  }
}

df <- adply(df, 1, maxWindDate)
Count maximum number of consecutive 1s per group

Count maximum number of consecutive 1s per group


By : Antis
Date : March 29 2020, 07:55 AM
should help you out Use the shifting cumsum trick to get all groups of consecutive 1s, and then use value_counts to find the largest group.
code :
u = df1['value'].eq(1)
v = u.ne(u.shift()).cumsum().where(u)

v.groupby(level=0).value_counts().max(level=0).reset_index(name='max_consec_wks')

   userid  max_consec_wks
0     465               1
1     999               3
Count maximum consecutive row out of total row for each day Ms-Sql

Count maximum consecutive row out of total row for each day Ms-Sql


By : LostStudent
Date : March 29 2020, 07:55 AM
Hope this helps I Want total number of row for each and for each day want consecutive max row count where condition numb>2 and calibration!=1 from table in pic below , I think you want conditional aggregation:
code :
select todaydate, count(*) as totalrow,
       sum(case when (numb > 2 and calibration <> 1) then 1 else 0 end) as [max]
from table t 
group by todaydate;
Related Posts Related Posts :
  • R 'cowplot' neatly produce gridded plot with shared (common) legends and unique legends
  • Repeat R script for many times and save results to text file
  • How to negative lookbehind for special characters
  • data.table inner join produces error when no match is found
  • Create a new column base on existing column, but row above
  • Is there a way to visualize the process of source() in RStudio?
  • google places api consumes 10 request but I am doing only 1
  • Statistical mode of a categorical variable in R (using mlv)
  • Using for-loop to mutate a data.frame in r
  • Make plot with regression line for mixed model
  • Shortcut to select matces cases in R studio
  • vectoriced norm/matrix multiplication
  • Negative log10 transformation in R
  • Plot data with duplicate points
  • Visualizing crosstab tables with a plot in R - changing colours
  • How to manually modify automated numbers and labels in plot
  • How can I follow any redirections of a url in R?
  • Add jitter to box plot using markers in plotly
  • Adding an extra item to the legend
  • ggplot fills in data in the wrong order
  • Convert list to data frame
  • R: filtering by list(s) of strings and returning all results that start with the content of the lists
  • R:How to attach parts of a data frame with different headers and/or an overflowing piece of the dat frame
  • How to use 'par' for manipulating plot margins?
  • Can dplyr::case_when return mix of NAs and non-NAs?
  • Text preprocessing and topic modelling using text2vec package
  • Uploading multiple files in Shiny, process the files, rbind the results and return a download
  • R levelplot: color green-white-red (white on 0) according to one variable, but show the values of another variable
  • Why [i] doesn't point to the starting point in a vector
  • In R after generating a mvrnorm distribution, Y, what does Y[,1] do?
  • expand a data frame to have as many rows as range of two columns in original row
  • Getting started with R and CFA
  • Re order x-axis in ggplot so time goes from 12AM to 11PM in R
  • R - Automatically stack every nth column of a data frame and save them as new objects
  • How to format dplyr output in R into doubles (or other workable format)?
  • Dataframe to matrix conversion using tapply turns zeros to NAs
  • Smallest multiple of 1:20 - How can I make it quicker?
  • How to specify the size of a graph in ggplot2 independent of axis labels
  • How can I find the number of a vector's elements in another vector?
  • ROC curve from train/test set in caret R package
  • Random Forest for a mixture of categorical,numeric and "unwanted" variables which include missing values
  • extract certain data from multiple excel files with R
  • Matrix with counts of wins and losses between methods in R
  • Grouping string variables from a dataframe by best string match to make subsets
  • Reorder does not work after adding second geom_points
  • cover POS data formate to the one can apply Arules (Apriori)
  • Matching values between data frames based on overlapping dates
  • Grouped bar chart turns into stacked bar chart ggplot
  • R: How to fill in NA Values within a Column based on grouping?
  • Two action buttons, but only the first one, that is written in the server file, works?
  • Barchart grouped by variable both count up to 100 percent
  • Converting time in R to 24 hours
  • R - Web scrapping and downloading multiple zip files and save the files without overwriting
  • Find month and year inside string
  • Append multiple csv files into one file using R
  • Use `purrr::map` with k-means
  • R - 'data' is not an exported object from 'namespace:my_package'
  • Sum vector with number by dinamic intervals without looping
  • Issues with ave function in R: error "cannot allocate vector of size 419 kb."
  • Shiny system call with continuous updates
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org