Method to construct an count matrix (in integers) from a matrix of strings with pandas (Python)

user_5683067 0
Viewed 37

Could someone please help me find a way to solve my query below? I'm more so looking for the terms to search for to solve the problem but if you know a quick-and-dirty method that would also be appreciated.

I have a matrix like the one below:

        sample_1.             sample_2. sample_3.     sample_4.
G1  inc_1,inc_1A.                    *.    inc_1.        inc_1.
G2         inc_2.                    *.        *.            *.
G3             *.  inc_3,inc_3A,inc_3B.    inc_3.  inc_3,inc_3A

I am looking to convert this to a matrix of counts like the one below

    sample_1.   sample_2.    sample_3.    sample_4.
G1    2   0   1   1
G2    1   0   0   0
G3    0   3   1   2

This database is quite large (about 10,000 columns and 3,000,000 rows) so I want to try avoid df.iterrows() where possible. Does anyone know how I may begin to implement this?

The "_" in the cells could be counted as all incidents have this nomenclature and a "*" means not detected (or 0).

Any help, advice, or constructive criticism is highly appreciated.

pandas dataframe matrix python
2 Answers

You can use DataFrame.apply + Series.str.count to count the occurrences of _ in each string in the columns of dataframe:

df.apply(lambda s: s.str.count(pat='_'))

    sample_1.  sample_2.  sample_3.  sample_4.
G1          2          0          1          1
G2          1          0          0          0
G3          0          3          1          2
user_128331660

This could be a way to post the questions so it becomes very easy for other people to work on:

import pandas as pd
d = {'col1': ['inc_1,inc_2.', 'inc_2', '*.'], 'col2': ['inc_1.', '*.', 'inc_1,inc_3.']}
df = pd.DataFrame(data=d)

Here a solution. Notice the applymap is the key:

df_new = df.copy()
df_new = df_new.applymap(lambda x: x.count('_'))
print(df_new)
user_71275190
Related