Pandas: Cell frequency count by index

Question

My dataframe is a long list of 4 letters, 'A', 'T', 'G','C', I need to count the frequency of each letter by index

df = pd.DataFrame({'cases': ['ACCTTGTAGTGTATTTTATGACCAAATGACTTTTTCCCCCCAGTGGCTAATTTGTCTCAGGCCTGCGTCTTAAAGAGACACGGTAATGAGTAGGAAGTCCAGCGTGGTCTGGA','ACCTTGTACTGTATCTTATGACCAGATGACTTTTTCCACCCAGTGGCTAATTTGTCTCAGGCCTCCGTCTTAAAGAGACACGGTAATGAGTAGGAAGTCCAACGTGGTCTAGA','GCCTTGTACTGTATATTATGACCAAATGACTTTTTCCACCCATTGGCTAATTTGTCTCAGGCCTCCGTCTTAAAGAGACACGGAAATGAGTAGGAAGTCCAGCGTGGTCTAGA','ACCTTGTACTGTATATTATGACCAGATGACTTTTTCCACCCAGTGGCTAATTTGTCTCAGGCCTCCGTCTTAAAGAGACACGGTAATGAGTAGGAAGTCCAGCGTGGTCTAGA']})

                                               cases
0  ACCTTGTAGTGTATTTTATGACCAAATGACTTTTTCCCCCCAGTGG...
1  ACCTTGTACTGTATCTTATGACCAGATGACTTTTTCCACCCAGTGG...
2  GCCTTGTACTGTATATTATGACCAAATGACTTTTTCCACCCATTGG...
3  ACCTTGTACTGTATATTATGACCAGATGACTTTTTCCACCCAGTGG...
4  ACCTTGTACTGTATATTATGACCAGATGACTTTTTCCACCCAGTGG...
5  ACCTTGTAGTGTATTTTATGACCAAATGACTTTTTCCCCCCAGTGG...
6  ACCTTGTACTGTATCTTATGACCAGATGACTTTTTCCACCCAGTGG...
7  GCCTTGTACTGTATATTATGACCAAATGACTTTTTCCACCCATTGG...
8  ACCTTGTACTGTATATTATGACCAGATGACTTTTTCCACCCAGTGG...
9  ACCTTGTACTGTATATTATGACCAGATGACTTTTTCCACCCAGTGG...

The result would be a new df of shape 4x113, i cannot figure out a pandas way to do this. Below is my non-pandas solution

def freq_lists(dna_list):
    n = len(dna_list[0])
    A = [0]*n
    T = [0]*n
    G = [0]*n
    C = [0]*n
    for dna in dna_list:
        for index, base in enumerate(dna):
            if base == 'A':
                        A[index] += 1
            elif base == 'C':
                C[index] += 1
            elif base == 'G':
                        G[index] += 1
            elif base == 'T':
                T[index] += 1
    return {'A': A, 'C': C, 'G': G, 'T': T}

fdf = pd.DataFrame(freq_lists(df['cases'].to_list()))

     A  C  G  T
0    3  0  1  0
1    0  4  0  0
2    0  4  0  0
3    0  0  0  4
4    0  0  0  4
..  .. .. .. ..
108  0  4  0  0
109  0  0  0  4
110  3  0  1  0
111  0  0  4  0
112  4  0  0  0

To clarify the first row is obtained by summing up the counts of the first str in the case column which is AAGA -> A: 3, C:0, G:1 T:0

user_7964527 · Answer

Let us do explode with crosstab

s = df.cases.map(list).explode()
out = pd.crosstab(s.groupby(level=0).cumcount(),s)
Out[583]: 
cases  A  C  G  T
row_0            
0      3  0  1  0
1      0  4  0  0
2      0  4  0  0
3      0  0  0  4
4      0  0  0  4
   .. .. .. ..
108    0  4  0  0
109    0  0  0  4
110    3  0  1  0
111    0  0  4  0
112    4  0  0  0

user_12833166 · Answer

Let us try extractall, then value_counts on level=1 followed by unstack to reshape

l = df['cases'].str.extractall('(\w)')
l[0].groupby(level=1).value_counts().unstack(fill_value=0)

       A  C  G  T
match            
0      3  0  1  0
1      0  4  0  0
2      0  4  0  0
3      0  0  0  4
4      0  0  0  4
5      0  0  4  0
6      0  0  0  4
7      4  0  0  0
8      0  3  1  0
9      0  0  0  4
10     0  0  4  0
...
...
110    3  0  1  0
111    0  0  4  0
112    4  0  0  0

user_16343464 · Answer

Use collections.Counter:

from collections import Counter
df['cases'].apply(lambda x: pd.Series(Counter(x)))

output:

    A   C   T   G
0  27  24  34  28
1  29  26  33  25
2  30  25  33  25
3  29  25  33  26

The other way around it not as sexy:

pd.DataFrame([Counter(i)
              for i in list(zip(*df['cases'].apply(list).values))]
            ).fillna(0).astype(int)

or

(df['cases'].apply(lambda x: pd.Series(list(x)))
            .apply(pd.value_counts)
            .T.fillna(0).astype(int)
)

output:

     A  G  C  T
0    3  1  0  0
1    0  0  4  0
2    0  0  4  0
...
111  0  4  0  0
112  4  0  0  0

user_9857631 · Answer

Try with pd.get_dummies:

>>> df['cases'].apply(lambda x: pd.get_dummies(list(x))).sum()
     A  C  G  T
0    3  0  1  0
1    0  4  0  0
2    0  4  0  0
3    0  0  0  4
4    0  0  0  4
..  .. .. .. ..
108  0  4  0  0
109  0  0  0  4
110  3  0  1  0
111  0  0  4  0
112  4  0  0  0

[113 rows x 4 columns]

user_1387346 · Answer

I suggest .str.split('', expand=True) as a way to separate the letters into columns, then we could simply aggregate using value_counts:

>>> df['cases'].str.split('', expand=True)
  0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
0     A  C  C  T  T  G  T  A  G  T  G  T  A  T  T  T  T  A  T  G  A  C  C  A  A  A  T  G  A  C  T  T  T  T  T  C  C  C  C  C  C  A  G  T  G  G   
1     A  C  C  T  T  G  T  A  C  T  G  T  A  T  C  T  T  A  T  G  A  C  C  A  G  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  G  T  G  G   
2     G  C  C  T  T  G  T  A  C  T  G  T  A  T  A  T  T  A  T  G  A  C  C  A  A  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  T  T  G  G   
3     A  C  C  T  T  G  T  A  C  T  G  T  A  T  A  T  T  A  T  G  A  C  C  A  G  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  G  T  G  G   
4     A  C  C  T  T  G  T  A  C  T  G  T  A  T  A  T  T  A  T  G  A  C  C  A  G  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  G  T  G  G   
5     A  C  C  T  T  G  T  A  G  T  G  T  A  T  T  T  T  A  T  G  A  C  C  A  A  A  T  G  A  C  T  T  T  T  T  C  C  C  C  C  C  A  G  T  G  G   
6     A  C  C  T  T  G  T  A  C  T  G  T  A  T  C  T  T  A  T  G  A  C  C  A  G  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  G  T  G  G   
7     G  C  C  T  T  G  T  A  C  T  G  T  A  T  A  T  T  A  T  G  A  C  C  A  A  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  T  T  G  G   
8     A  C  C  T  T  G  T  A  C  T  G  T  A  T  A  T  T  A  T  G  A  C  C  A  G  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  G  T  G  G   
9     A  C  C  T  T  G  T  A  C  T  G  T  A  T  A  T  T  A  T  G  A  C  C  A  G  A  T  G  A  C  T  T  T  T  T  C  C  A  C  C  C  A  G  T  G  G   
>>> counts = df['cases'].str.split('', expand=True).agg(pd.Series.value_counts)
>>> counts
     0    1     2     3     4     5     6     7     8    9     10    11    12    13    14   15    16    17    18    19    20    21  ...    26    27    28    29    30    31    32    33    34    35    36    37   38    39    40    41    42   43    44    45    46    47
   10.0  NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  NaN   NaN   NaN   NaN   NaN   NaN  NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  NaN   NaN   NaN   NaN   NaN  NaN   NaN   NaN   NaN  10.0
A   NaN  8.0   NaN   NaN   NaN   NaN   NaN   NaN  10.0  NaN   NaN   NaN   NaN  10.0   NaN  6.0   NaN   NaN  10.0   NaN   NaN  10.0  ...  10.0   NaN   NaN  10.0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  8.0   NaN   NaN   NaN  10.0  NaN   NaN   NaN   NaN   NaN
C   NaN  NaN  10.0  10.0   NaN   NaN   NaN   NaN   NaN  8.0   NaN   NaN   NaN   NaN   NaN  2.0   NaN   NaN   NaN   NaN   NaN   NaN  ...   NaN   NaN   NaN   NaN  10.0   NaN   NaN   NaN   NaN   NaN  10.0  10.0  2.0  10.0  10.0  10.0   NaN  NaN   NaN   NaN   NaN   NaN
G   NaN  2.0   NaN   NaN   NaN   NaN  10.0   NaN   NaN  2.0   NaN  10.0   NaN   NaN   NaN  NaN   NaN   NaN   NaN   NaN  10.0   NaN  ...   NaN   NaN  10.0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  NaN   NaN   NaN   NaN   NaN  8.0   NaN  10.0  10.0   NaN
T   NaN  NaN   NaN   NaN  10.0  10.0   NaN  10.0   NaN  NaN  10.0   NaN  10.0   NaN  10.0  2.0  10.0  10.0   NaN  10.0   NaN   NaN  ...   NaN  10.0   NaN   NaN   NaN  10.0  10.0  10.0  10.0  10.0   NaN   NaN  NaN   NaN   NaN   NaN   NaN  2.0  10.0   NaN   NaN   NaN

[5 rows x 48 columns]

With some little cleanup, including a .shift(-1, axis='columns') to move from 1-indexing to 0-indexing:

>>> counts.fillna(0, downcast='infer').shift(-1, axis='columns').iloc[1:, :-2]
   0   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40  41  42  43  44  45
A   8   0   0   0   0   0   0  10   0   0   0   0  10   0   6   0   0  10   0   0  10   0   0  10   4  10   0   0  10   0   0   0   0   0   0   0   0   8   0   0   0  10   0   0   0   0
C   0  10  10   0   0   0   0   0   8   0   0   0   0   0   2   0   0   0   0   0   0  10  10   0   0   0   0   0   0  10   0   0   0   0   0  10  10   2  10  10  10   0   0   0   0   0
G   2   0   0   0   0  10   0   0   2   0  10   0   0   0   0   0   0   0   0  10   0   0   0   0   6   0   0  10   0   0   0   0   0   0   0   0   0   0   0   0   0   0   8   0  10  10
T   0   0   0  10  10   0  10   0   0  10   0  10   0  10   2  10  10   0  10   0   0   0   0   0   0   0  10   0   0   0  10  10  10  10  10   0   0   0   0   0   0   0   2  10   0   0

Alternately .str.split() can also be used with explode(), in which case @BENY’s cumcount grouping can be reused with value_counts():

>>> letters = df['cases'].str.split('').explode()
>>> counts = letters.groupby(letters.groupby(level=0).cumcount()).value_counts()
>>> counts.unstack('cases', fill_value=0).drop(columns='').shift(-1, fill_value=0).iloc[:-2].head(10)
cases   A   C   G   T
0       8   0   2   0
1       0  10   0   0
2       0  10   0   0
3       0   0   0  10
4       0   0   0  10
5       0   0  10   0
6       0   0   0  10
7      10   0   0   0
8       0   8   2   0
9       0   0   0  10

Or even closer to @BENY’s answer, using cumcount() and pd.crosstab after .str.split('').explode():

>>> letters = df['cases'].str.split('').explode()
>>> counts = pd.crosstab(letters.groupby(level=0).cumcount(), letters)
>>> counts.drop(columns='').shift(-1, fill_value=0).iloc[:-2].head(10)
cases   A   C   G   T
row_0                
0       8   0   2   0
1       0  10   0   0
2       0  10   0   0
3       0   0   0  10
4       0   0   0  10
5       0   0  10   0
6       0   0   0  10
7      10   0   0   0
8       0   8   2   0
9       0   0   0  10

Here’s a quick performance comparison on my system with different sizes:

So the fastest for all sizes is @BENY’s with .str.split('') instead of .map(list), which is also the last presented in this post.

Below the code to generate these benchmarks

import numpy as np, pandas as pd, timeit, collections

rows = 100_000
reps = 1_000_000 // rows
glob = {'cases': pd.DataFrame(np.random.choice(list('ABCD'), (113, rows))).agg(''.join).rename('cases'), 'pd': pd, 'Counter': collections.Counter}

print('list + crosstab:', timeit.timeit('s = cases.map(list).explode() ; pd.crosstab(s.groupby(level=0).cumcount(),s)', globals=glob, number=reps) / reps)
print('split-explode + crosstab:', timeit.timeit('s = cases.str.split('').str[1:-1].explode() ; pd.crosstab(s.groupby(level=0).cumcount(),s)', globals=glob, number=reps) / reps)
print('extractall:', timeit.timeit("cases.str.extractall(r'(\w)')[0].groupby(level=1).value_counts().unstack(fill_value=0)", globals=glob, number=reps) / reps)
print('split-expand + value_counts:', timeit.timeit("cases.str.split('', expand=True).agg(pd.Series.value_counts).fillna(0, downcast='infer').shift(-1, axis='columns').iloc[1:, :-2].T", globals=glob, number=reps) / reps)
print('split-explode + value_counts:', timeit.timeit("s = cases.str.split('').explode() ; s.groupby(s.groupby(level=0).cumcount()).value_counts().unstack('cases', fill_value=0).drop(columns='').shift(-1, fill_value=0).iloc[:-2]", globals=glob, number=reps) / reps)
print('dummies sum:', timeit.timeit("cases.apply(lambda x: pd.get_dummies(list(x))).sum()", globals=glob, number=reps // 10) / (reps / 10))
print('Counter:', timeit.timeit("cases.apply(lambda x: pd.Series(Counter(x)))", globals=glob, number=reps // 10) / (reps / 10))

Pandas: Cell frequency count by index

5 Answers