Break a column of rows and count symbol frequencies

Question

I have a dataset with two columns that look like this:

|group| |sequence|
A        BX
A        X
B        SFS
B        BCX
B        BSS*B1S
A        BBX

I would like you to be able to group and find the frequency of each character in order to get something like this:

 |group| |char| |freq|
 A       B       3
 A       X       3
 B       S       5
 ...

Answer 1

You can use an effective solution repeat, followed by groupby:

from itertools import chain

# Step 1 - flatten your dataframe
df = pd.DataFrame({
    'group' : df['group'].repeat(df.sequence.str.len()), 
    'char' : list(chain.from_iterable(df.sequence.tolist()))
})
# Step 2 - filter out characters and groupby on `group`
df[df.char.str.isalpha()].groupby(['group', 'char']).size().reset_index(name='freq')

  group char  freq
0     A    B     3
1     A    X     3
2     B    B     3
3     B    C     1
4     B    F     1
5     B    S     5
6     B    X     1

Answer 2

Here are a few different ways to present your results.

Application groupby.sumin lines combines them, after which we can apply pd.value_counts.

df = pd.DataFrame({'group': ['A', 'A', 'B', 'B', 'B', 'A'],
                   'sequence': ['BX', 'X', 'SFS', 'BCX', 'BSS*B1S', 'BBX']})

res = df.groupby('group')['sequence'].sum().map(list).apply(pd.value_counts)\
        .fillna(0).astype(int).reset_index()

res = res.loc[:, res.columns.str.isalpha()]

print(res)

#   group  B  C  F  S  X
# 0     A  3  0  0  0  3
# 1     B  3  1  1  5  1

res2 = pd.melt(res, id_vars=['group']).sort_values(['group', 'variable'])
res2 = res2[res2['value'] != 0].set_index(['group', 'variable'])

#                 value
# group variable       
# A     B             3
#       X             3
# B     B             3
#       C             1
#       F             1
#       S             5
#       X             1

print(res2)

---

A more effective change is possible with collections.Counter:

from collections import Counter

res = df.groupby('group')['sequence'].sum().apply(Counter).apply(pd.Series)\
        .fillna(0).astype(int).reset_index()

Answer 3

Use comprehension to create a list of tuples.

c = pd.value_counts([(g, s) for g, S in df.values for s in S if s.isalpha()])

pd.DataFrame(
    np.column_stack([c.index.tolist(), c.values]),
    columns=df.columns.tolist() + ['freq']
)

  group sequence freq
0     B        S    5
1     A        B    3
2     B        B    3
3     A        X    3
4     B        F    1
5     B        C    1
6     B        X    1