CODEWITHSUNDEEP
rdplyr
Cauder
Cauder

Reputation: 2597

Is there a way to pull from the last non-blank value in a column?

I have a table with a list of actions:

   name         person         timestamp           open_count               
 Opened            A      2019-07-01 00:00:34      1
 Action            A      2019-07-01 00:00:34      
 Action            A      2019-07-01 00:00:34      
 Opened            A      2019-07-02 00:00:34      2
 Action            A      2019-07-01 00:00:34      
 Action            A      2019-07-01 00:00:34      
 Opened            A      2019-07-08 00:00:34      3
 Action            A      2019-07-01 00:00:34      
 Action            A      2019-07-01 00:00:34      
 Action            A      2019-07-01 00:00:34      
 Opened            A      2019-07-11 00:00:34      4

I'd like to fill in the value for open_count to the last non-null value in the column. My goal is to create a session so that I can look at the number of actions for each time a user took the open action.

How do I fill in the last non-null value? You can imagine the timestamps are in ascending order.

Upvotes: 1

Views: 607

Answers (1)

akrun
akrun

Reputation: 887108

Assuming the blank is ""

df1[tail(which(colSums(df1 != "") == nrow(df1)), 1)]

or if it is NA

df1[tail(which(colSums(!is.na(df1)) == nrow(df1)), 1)]

If we need to fill the 'open_count'

library(dplyr)
library(tidyr)
df1 %>%
   mutate(open_count = na_if(open_count, "")) %>% 
   fill(open_count)

Upvotes: 3

Related Questions