Count values per year and based on other column

» r » Count values per year and based on other column

By : Seshagiri

Date : November 24 2020, 04:01 AM

Hope that helps I have data like this , Yet another possibility:

code :

library(dplyr)
library(tidyr)
#library(lubridate) # you can choose to import it or not


dat %>% 
  mutate(date = lubridate::dmy(format(date, format="%d-%m-%Y"))) %>% # correct date format
  mutate(year = lubridate::year(date)) %>%  # extract the year
  group_by(year, Chemical) %>% 
  mutate(exceed = concentration > limit) %>% # TRUE/FALSE
  summarise(tot_exceed = sum(exceed)) %>%  # count each T/F
  spread(Chemical, tot_exceed) # Spread the results by Chemical
# # A tibble: 2 x 4
# # Groups:   year [2]
# year     A     B     C
# <dbl> <int> <int> <int>
# 1  2016     2     1     1
# 2  2017     1     2     0

tt <- "  Chemical  date      concentration  limit
   A     01-01-2016     0.2         0.01
A     01-02-2016     0.2         0.01
A     01-01-2017     0.005       0.01
A     01-02-2017     0.2         0.01
B     01-01-2016     0.3         0.1
B     01-02-2016     0.05        0.1
B     01-01-2017     0.2         0.1
B     01-02-2017     0.2         0.1
C     01-01-2016     1.2         1
C     01-02-2016     0.8         1
C     01-01-2017     0.9         1
C     01-02-2017     0.9         1"

dat <- read.table(text = tt, header = T)

Share :

SSRS - Count values in a column based upon distinct values in another column in SharePoint List

By : Jurc

Date : March 29 2020, 07:55 AM

wish help you to fix your issue You can use matrix to easily achieve this.
Use Column A (red, blue, green) for column grouping

code :

=SUM(IIF(ISNOTHING(Fields!ColumnA.VALUE) OR ISNOTHING(Fields!ColumnB.VALUE), 0, 1)

PHP Average values in column based on year date

By : Standford Reid

Date : March 29 2020, 07:55 AM

this will help Im working on webpage that should average values in a given column based on the current year. Ive got the following code: , You basically answered the question yourself:

code :

$year = date("Y");
$year = "$year%";
$stmt = $this->dbconn->prepare("SELECT AVG(ElecUsage), DateAdded FROM 
elec_readings  WHERE AccountNumber = '" . $_SESSION['user_session'] . "' 
AND DateAdded LIKE '$year'");

Dynamically select a column using month and year, then sumif the values based on criteria in another column

By : Silvestre Gil

Date : March 29 2020, 07:55 AM

I hope this helps . I would "cheat"
I have my months list was in AI1:AI12, and year list in AJ1:AJ2 (for the dropdowns in A1 and B1 respectively). Move them elsewhere for more years.

code :

=MATCH(A1,AI1:AI12,0)

=IF(MATCH(B1,AJ1:AJ2,0)=1,0,12 *( MATCH(B1,AJ1:AJ2,0)-1))

=SUMIF($C$3:$C$8,"Plan",D3:D8)

=OFFSET(D10,,B2+A2-1,1,1)

Count the number of times two values appear in a column based on the unique values of another column

By : Julija Wilson

Date : November 01 2020, 04:01 AM

should help you out We can use count and spread to get the df format and use fill = 0 in spread to fill in the 0s:

code :

library(tidyverse)
YG %>%
  group_by(year) %>%
  count(gender) %>%
  spread(gender, n, fill = 0)

# A tibble: 3 x 3
# Groups:   year [3]
  year      F     M
  <fct> <dbl> <dbl>
1 2000      1     2
2 2001      0     1
3 2002      1     0

Cumulative count based on year column in pandas

By : Kaito002

Date : March 29 2020, 07:55 AM

hop of those help? I have a data frame as shown below , you can try this:

code :

df_new = df.groupby(['Unit_Create_Year']).agg({'Unit_ID':['count','unique']}).reset_index()
df_new.columns = ['Year','Number_of_Unit_Since_Year','List_of_Units']
df_new['Number_of_Unit_Since_Year'] = df_new['Number_of_Unit_Since_Year'].cumsum()
df_new['List_of_Units'] = df_new['List_of_Units'].apply(lambda x : x.tolist()).cumsum()

df_new


   Year  Number_of_Unit_Since_Year                List_of_Units
0  2011                          2                       [1, 2]
1  2012                          4                 [1, 2, 3, 5]
2  2014                          5              [1, 2, 3, 5, 4]
3  2015                          6           [1, 2, 3, 5, 4, 6]
4  2017                          9  [1, 2, 3, 5, 4, 6, 7, 8, 9]