CODEWITHSUNDEEP
pythondataframecount
Faizul
Faizul

Reputation: 15

Conditional Count in DataFrame with Python

DataFrame showing trend respective to date

I want to count how many times there is:

  1. "Increase" to "Increase"
  2. "Increase" to "Decrease"
  3. "Increase" to "Unchanged"
  4. "Decrease" to "Increase"
  5. "Decrease" to "Decrease"
  6. "Decrease" to "Unchanged"
  7. "Unchanged" to "Increase"
  8. "Unchanged" to "Decrease"
  9. "Unchanged" to "Unchanged"

Code must be written in python.

Upvotes: 0

Views: 274

Answers (2)

Muhammad Taha
Muhammad Taha

Reputation: 36

I'm creating a sample dataframe to work on this problem.

import pandas as pd
import numpy as np
df = pd.DataFrame({'id': np.arange(0,16), "trend": ['in','de', 'in', 
'in','in','un','de','de','un','un','de','de','in','de','in','in']})

sample dataframe

1st: Make a list of the column named 'trend'

val = list(df['trend'])

2nd: Create a new list and add each pair of tuples into the list. Each tuple contains the first value and its consecutive next value according to the iteration.

f = val[0]
list_trend = []

for x in val[1:]:
    list_trend.append((f,x))
    f = x

The output list will be like this 👆 list of pairs

3rd: By using 'Counter', you can count occurances of each unique pair from that list.

from collections import Counter
c = Counter(list_trend)
count = c.items()

The output will be like this 👆 count of each unique pair

And that's it.....

Upvotes: 2

Arnav Mangla
Arnav Mangla

Reputation: 132

I can think of one approach which is not going to be very efficient but will get the job done:

So the basic idea is to iterate over the trend column. You go making tuples like (new_df.trend[i], new_df.trend[i+1]). Then you use the counter to get a dictionary which would be something like this:

{("Increase", "Increase"): 131, ("Increase", "Decrease"): 317, ...}

The actual implementation should look something like this:

from collections import Counter

list_of_tuples = [] 
for i in range(len(new_df.trend) - 1):
    list_of_tuples.append((new_df.trend[i], new_df.trend[i+1]))
occurrences = dict(Counter(list_of_tuples))

Upvotes: 0

Related Questions