Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
bzanos
Level II

How to Get Row Number When There is Missing Data

There is missing value in my data.

I'm trying to create script for new column 'Row Number' to get row number of lagged value of Col1(non-missing data).

I'm facing problem when missing data is encountered.

Below is my data with required row number outcome.

How to create script for this?

 

Row#Col1Row Number(outcome)
111 
2181
3342
4 3
5 3
6 3
7203
8 7
9 7
10247
111410
121711
13 12
14 12
151612
162515

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
XanGregg
Staff

Re: How to Get Row Number When There is Missing Data

I think this will do what you're looking for. Lag() without an argument will give the lag of the current column.

 

New Column( "r",
	Formula(
		If(
			Row() == 1, .,
			Is Missing( Lag( :Col1 ) ), Lag(),
			Row() - 1
		)
	)
)

View solution in original post

2 REPLIES 2
Highlighted
XanGregg
Staff

Re: How to Get Row Number When There is Missing Data

I think this will do what you're looking for. Lag() without an argument will give the lag of the current column.

 

New Column( "r",
	Formula(
		If(
			Row() == 1, .,
			Is Missing( Lag( :Col1 ) ), Lag(),
			Row() - 1
		)
	)
)

View solution in original post

Highlighted
bzanos
Level II

Re: How to Get Row Number When There is Missing Data

It works.

Thank you.

Article Labels