Dear all,

In my master thesis, I want to examine the relation between CEO turnover and innovation. The first step is to identify 'why a CEO turnover takes place'. One of the variables are the consecutive loss years. Which means in words: After multiple consecutive loss years, it is more likely dat a CEO turnover takes place.

Now i have the data like this:
Company Name Year Net Income Loss1 Loss2
X 2010 10 1 0
X 2011 6 1 0
X 2012 8 1 0
X 2013 5 1 0
X 2014 -10 1 0
Y 2012 14 0 1
Y 2013 18 0 1
Y 2014 22 0 1
Y 2015 8 0 1
Y 2016 -4 0 1
Y 2017 -8 0 1
What I want is this:

A company belongs in Loss 1 if the last available year has a negative Net Income, profits in all other years. So in the example above, Company X has a net income < 0 in 2014 and profits in 2010-2013, which means that Company X belongs to Loss 1.

Company Y has losses in the two most recent years available, 2017 and 2016, and profits in the other years, which means 2 consecutive loss years --> LOSS2. Logically, if the company had losses for 2017 till 2012, it will belong to the group: LOSS6.

Now I also have this examples in the data:
Company Name Year Net Income Loss... Loss...
Z 2010 4 0 0
Z 2011 2 0 0
Z 2012 -10 0 0
Z 2013 1 0 0
W 2010 4 0 0
W 2011 -1 0 0
W 2012 3 0 0
W 2013 -12 0
Now the companies do NOT fall into the groups, because the defintion of the loss variables are:

Loss 1: a loss in the most recent year of observation, profits in the other years (for company W means a loss in 2013, profits in 2010-2012, but it also has a loss in 2011 which means it does not follow the definition so it would not be grouped into a loss group).
Loss2: a loss in the two most recent years of observation, profits in the other years.

The data in my dataset is at most from 2010-2019, with some companies only having data from 2012-2019, some companies have data from 2010-2018 and so on.

I'm wondering if it is easier to only keep the data in a certain period. So lets say: Keeping only the companies with available data in the year 2010-2016.
Then I'm still struggling how to achieve the above situation.

I really hope you can help me out with this, since i've been working on it a lot but don't came to a solution. Hopefully you can!

Thanks in advance,

Remy