Contributor
Posts: 21

# Running average by group

I am trying to create a running average by group

For example, i want to start creating the average of 2 continuous numbers when it starts with value of 170. However, if it first hits 170 and then >=200 then another >=200, i want to start average of those three. if not, just the average of two is fine.

Here's an example

 ID value seq_id 1 150 1 1 190 2 1 205 3 1 210 4 1 150 5 2 169 1 2 175 2 2 150 3 2 144 4 3 200 1 3 201 2 3 199 3

Here's the output I am looking for:

 ID value seq_id avg 1 150 1 1 190 2 202 1 205 3 1 210 4 1 150 5 2 169 1 2 175 2 163 2 150 3 2 144 4 3 190 1 196 3 201 2 3 185 3

Thank you!

Posts: 3,162

## Re: Running average by group

Rules seem not entirely clear to me.

1. What if you have more than 2 groups within the same id, do you only count the first one?

2.  What if you have more than 2 obs that are >=200 after the first >=170. Are they going to the first group or you kick off a new group/just ignore it?

3. What if first >=170 hitting the bottom, do you consider itself as the average or ignore it?

Contributor
Posts: 21

## Re: Running average by group

Hi!

1. Each ID is unique but have multiple entries. so we count the first occurence of >=170 onwards

2. Yes, it'd be helpful to extract them into a new dataset if first >=170 then second >=200 and then the third is >=200

3. If the last row for that ID is >=170, new dataset would be helpful!

Thank you!!

Discussion stats
• 2 replies
• 171 views
• 0 likes
• 2 in conversation