Computing the conditional probability based on consecutive occurences of numbers in dataframe

user_4289641 0
Viewed 84

I have a dataframe that has a multi index (stock ticker and date) with a column that counts for each stock, in each row how many times the 1s or 0s have occurred in the 'Dummy" column. I have an example below.

df = pd.DataFrame(  {
'stock': ['AAPL', 'AAPL', 'AAPL','AAPL', 'MSFT', 'MSFT','MSFT', 'MSFT'], 
'datetime': ['2015-01-02', '2015-01-03', '2015-01-04', '2015-01-05', '2015-01-02', '2015-01-03', '2015-01-04', '2015-01-05'],
'Dummy': [0, 0, 1, 1, 1,1, 0, 1],
'Counter': [-1, -2, 1, 2, 1, 2, -1, 1]})
df['datetime'] = pd.to_datetime(df['datetime'])
df.set_index(['stock', 'datetime'], inplace =True)

I would like to compute a conditional probability (for each number), that answer this question:

Given that I observe a row with a 1 in the Counter column, how many times was it followed by a 2, and how many times was it followed by -1.

In the example above there are 3 instances of 1s. The last one is not followed by anything so it should be ignored, so technically have only 2 instances. Both of them are followed by 2, so the output for 1 should look sth like this:

result = pd.DataFrame(  {
'cond prob': ['1', '2', '-1','-2'],
'1': [0, 2, 0,0],
'2': [0, 0, 1,0],
'-1': [1, 0, 0, 1],
'-2': [1, 0, 0, 0]})

 result.set_index(['cond prob',], inplace =True)

Basically, I would like to know for each number, how many times it was followed by any of the other numbers (grouped by stock).

This problem is a follow up question related to this post:

Counting the number of consecutive occurences of numbers in dataframe with multi index daily data

pandas-groupby pandas dataframe
2 Answers

We can groupby and shift the Counter column then using crosstab create a frequency table to count the number of times when a certain number is followed by other number

table = pd.crosstab(df['Counter'], df.groupby(level=0)['Counter'].shift(-1))

>>> table

Counter  -2.0  -1.0   1.0   2.0
Counter                        
-2          0     0     1     0
-1          1     0     1     0
 1          0     0     0     2
 2          0     1     0     0

If you also need to calculate probabilities we can do so by first calculating the total possible outcomes using values_counts then dividing favourable outcomes by all possible outcomes

probs = table.div(df['Counter'].value_counts(), axis=0)

>>> probs

Counter  -2.0  -1.0   1.0       2.0
-2        0.0   0.0   1.0  0.000000
-1        0.5   0.0   0.5  0.000000
 1        0.0   0.0   0.0  0.666667
 2        0.0   0.5   0.0  0.000000
user_128331660

I'm not sure if there is a built in way in pandas to do that, but using plain python, it would look something like this: \

two_counter, negative_counter = 0, 0

for idx, value in enumerate(df['Counter'].values):
    if idx == len(df) - 1:
        continue
    if value == 1:
        if df.iloc[idx+1, 1] == 2:
            two_counter += 1
        if df.iloc[idx+1, 1] == -1:
            negative_counter += 1
user_105434330
Related