Creating a count for date in column in POSIT

General
1

Hi,

I am trying to create a count column such as the following. i.e. if there is a date in the date column then have a count of 1, if it's blank then zero or empty.

|date | count|

|blank | 0|
|blank | 0|
|01/01/2021| 1|
|02/02/2023| 1|

Thanks,
John

2

To make life easier for us could you supply some sample data? It's hard to extrapolate from your example just what format the date column has.

A handy way to supply data is to use the dput() function. Do dput(mydata) where "mydata" is the name of your dataset. For really large datasets probably dput(head(mydata, 100)) will do. Paste the output between
```

```

With a user name like johnnyk32uk are you from Vancouver?

3

Hope this works OK? I would like a count of 1 against the ref_date that have dates and a count of 0 that have blanks.

Thanks

LINK_NO	ref_date	Count
1	 	 
2	 	 
3	 	 
4	 	 
5	 	 
6	 	 
7	 	 
8	 	 
9	 	 
10	 	 
11	 	 
12	 	 
13	 	 
14	 	 
15	 	 
16	 	 
17	 	 
18	 	 
19	 	 
20	 	 
21	 	 
22	 	 
23	 	 
24	 	 
25	 	 
26	 	 
27	 	 
28	 	 
29	 	 
30	 	 
31	 	 
32	 	 
33	 	 
34	 	 
35	 	 
36	 	 
37	 	 
38	 	 
39	 	 
40	 	 
41	 	 
42	24/03/2022	 
43	24/03/2022	 
44	24/03/2022	 
45	24/03/2022	 
46	24/03/2022	 
47	24/03/2022	 
48	24/03/2022	 
49	24/03/2022	 
50	24/03/2022	 
51	24/03/2022	 
52	24/03/2022	 
53	24/03/2022	 
54	24/03/2022	 
55	24/03/2022	 
56	24/03/2022

[/quote]

4

I think it may work but do you have that data in a data.frame? If so the dput() format is much better. It is an exact copy of your data and we don't have to guess at the data formats.

I'll poxe around at it and see what I can do.

5
6

Thanks, I was just thinking I could do something but this should make life easier.

1 Like
7

Okay I think I've got something but I am doing it with {data.table}. You will need to convert your tibble to a data.table or read in your raw data as I did.

To convert

setDT(dat1)         ; setDF(dat1)

Actual script

# Load {data.table} -------------------------------------------------------

suppressMessages(library(data.table))
suppressMessages(library(tidyverse))

# Load data and drop "Count" as it is logical. -----------------------------

DT <-  fread("raw.data.csv")
DT[, !c("Count")]


# Use fcase() to evaluate "ref_date ---------------------------------------
DT[, Count := fcase(
  is.na(ref_date), 0,
  default = 1
)] 

# Check Results -----------------------------------------------------------
DT[, table(Count)]
1 Like
8

I changed this a bit and got it working thank you. :grinning:

df <- df %>%
mutate(Count := fcase(
is.na(ref_date), 0,
default = 1))

9

Looks good, I just don't like {dplyr} :grinning:

I should have mentioned it has a fcase function.

1 Like
10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.