Rolling occurences from two colums

user_13535575 0
Viewed 53

in pandas i have two series of x rows and i want to add a column in which i get the rolling count of times that the value in col1 appears from the first row till the x-1 one.

The df is like this:

   col1 col2
0  B    A
1  B    C
2  A    B
3  A    B
4  A    C
5  B    A

The desired output is

   col1 col2 freq
0  B    A    0
1  B    C    1
2  A    B    1
3  A    B    2
4  A    C    3    #A appears 3 times in the two columns from row 0 to 3
5  B    A    4    #B appears 4 times in the two columns from row 0 to 4

Thanks in advance from a beginner, G

countif rolling-computation pandas python
3 Answers

Let use some dataframe reshaping, groupby and cumcount:

dfs = df.stack()
df['freq'] = dfs.groupby(dfs).cumcount().unstack()['col1']
print(df)

Output:

  col1 col2  freq
0    B    A     0
1    B    C     1
2    A    B     1
3    A    B     2
4    A    C     3
5    B    A     4
user_63615310 
from collections import defaultdict

def fn():
    d1, d2 = defaultdict(int), defaultdict(int)
    x = yield
    while True:
        x = yield d1[x.col1] + d2[x.col1]
        d1[x.col1] += 1
        d2[x.col2] += 1

f = fn()
next(f)
df['freq'] = df[['col1', 'col2']].apply(lambda x: f.send(x), axis=1)

print(df)

Prints:

  col1 col2  freq
0    B    A     0
1    B    C     1
2    A    B     1
3    A    B     2
4    A    C     3
5    B    A     4

EDIT (solution for arbitrary number of columns):

from collections import defaultdict

def fn(cols):
    dd = [defaultdict(int) for _ in cols]
    x = yield
    while True:
        x = yield sum(d[x[0]] for d in dd)
        for i, d in enumerate(dd):
            d[x[i]] += 1

cols = ['col1', 'col2']
f = fn(cols)
next(f)
df['freq'] = df[cols].apply(lambda x: f.send(x), axis=1)

print(df)
user_100359850

This will solve irrespective of number of columns in the df

import pandas as pd
import numpy as np

def add(d1,d2):
    # adding two dictionary
    for i in d2.keys():
        if i in d1.keys():
            d1[i] = d1[i] +d2[i]
        else:
            d1[i] = d2[i]
    return d1

if __name__ == '__main__':
    counts = {}
    df = pd.DataFrame({"a":[1, 2, 3, 1, 2], "b":[2, 1, 2, 3, 1]})
    col = list(df)
    for ind, it in df.iterrows():
        unique,count = np.unique(it,return_counts=True)
        unique_dict = dict(zip(unique, count))
        counts = add(counts,unique_dict)

        df.loc[ind, "freq"] = counts[it[col[0]]]
    df["freq"] =df["freq"]-1
user_85881080
Related