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
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
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
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
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]