0

I am trying to tag the change in state of different records month on month.

In my data-set, i have is structured as below:

enter image description here

For state change columns, it must be 0, if Paid(Y/N) flag changes from past month.

I have tried using grouping the data but with grouping i am getting only one entry consolidated for all months. However i want to keep every months entry of same record intact.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
SKB
  • 189
  • 1
  • 13
  • 2
    Welcome to stack overflow, please read these guidelines on how to ask a good question: https://stackoverflow.com/a/5963610/8675075 (you're much more likely to find the help you're looking for). – Paul Dec 02 '19 at 06:31
  • 1
    Hi Saurabh, welcome to stackoverflow. To have us correctly help you with your question, it is advised not to include images, but an example of your data. have a look here https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – Arcoutte Dec 02 '19 at 06:33

1 Answers1

1

I have created a dataframe that is alike to yours, for ease of explanation, try to provide a reproducible example yourself next time :-). 

df <- data.frame( "id" = c(1,2,3,4,5,5,5,5,6,6,6,6), # id values
        "month" = c(6,7,8,9,6,7,8, 9,6,7,8,9), # month values
        "outstanding" = c(500, 300, 30, 40, 56, 32, 35, 40, 56, 32, 32, 59), #outstanding amount
        "paid" = c("n", "y", "y", "y", "y", "y","n", "y", "y", "n", "n", "y"), # paid yes or no
        "state" = '', #state: filling next
        stringsAsFactors = FALSE # not having characters as factors, not really needed here.
        )

This dataframe is grouped on id and than mutated by using the lagfunction from dplyr. This function compares, within your grouping, the current value with the value above. lead does the opposite.

df %>%
  group_by( id) %>% #group observations by their id
  mutate( state = ifelse( paid == lag(paid), "stable", "change")) # if the current observations, grouped by id, differs from the previous (lagged), we observe a change.

Note that for your data, it might be needed to transform your month of payment to an ordered factor or date, to correctly find a change in state

Arcoutte
  • 810
  • 4
  • 16