Count multi range + Same Column

Question

[shabbas12]

Dear all,

I have an excel data file like;

I need to count Yes/No from Row # A2 to A6, A7 to A11 and A12 to A20.

Is this possible in excel or Python?

[cpad0112]

countif ??

Please do not post images of the data.

[bioinformatics2020]

Here's an R version if you're interested. Assuming this is saved as a .csv

library(tidyverse)

read_csv("sample.csv") %>%
    mutate(group = c(rep("Group one", 5), rep("Group two", 5), rep("Group three", 9))) %>%
    group_by(group) %>%
    tally()

If you want it counted

Answer 1

[ronald]

I assume there is a way in excel but I honestly don't know. But I would rather use python just because you can use the same code in case you need to perform the same task again in the future or if you need to query a different range. It's just a matter of using xlrd: https://pypi.org/project/xlrd/ and iterating the columns, updating a counter variable as you get hits.

Answer 2

[zorbax]

You can use Pandas to read your Excel file with the function pd.read_excel(), for example with this dataframe:

 | ID              | Gene   | Answer   |
 |-----------------|--------|----------|
 | ENSG00000000004 | YBGRXF | Yes      |
 | ENSG00000000542 | BPHRXT | No       |
 | ENSG00000000320 | OLRGZB | No       |
 | ENSG00000000668 | OQC62N | No       |
 | ENSG00000003876 | PWP8ME | Yes      |
 | ENSG00000003979 | URP4GF | Yes      |
 | ENSG00000001919 | 4HBVVA | No       |
 | ENSG00000006510 | MECXQH | Yes      |
 | ENSG00000005306 | V5SGVX | No       |
 | ENSG00000004722 | HSVPJ8 | No       |
 | ENSG00000007613 | A2WNR4 | Yes      |
 | ENSG00000002934 | TDF8QK | Yes      |
 | ENSG00000002125 | 5MVEY1 | No       |
 | ENSG00000005561 | U6Y4P3 | No       |
 | ENSG00000001621 | V111IT | No       |
 | ENSG00000003284 | O3GICF | No       |
 | ENSG00000004562 | BKE142 | Yes      |
 | ENSG00000005886 | CRSRYC | Yes      |
 | ENSG00000004493 | P20JCS | Yes      |
 | ENSG00000004127 | TMP83G | No       |

You can use this code:

 res_A2_A6 = (df.set_index("ID")["Answer"]
                .iloc[0:4]
                .value_counts()
                .to_frame(df["ID"].loc[0])
                .T)

 res_A7_A11 = (df.set_index("ID")["Answer"]
                 .iloc[5:9]
                 .value_counts()
                 .to_frame(df["ID"].loc[5])
                 .T)

 res_A12_A20 = (df.set_index("ID")["Answer"]
                  .iloc[10:18]
                  .value_counts()
                  .to_frame(df["ID"].loc[10])
                  .T)

To select specific columns and different ranges [0:4] to count your Answer column. In the end you can concatenate your results in a new dataframe:

results = pd.concat([res_A2_A6, res_A7_A11, res_A12_A20], axis=0)

Something like this:

 |                 |  No  |  Yes |
 |-----------------|------|------|
 | ENSG00000000004 |   3  |    1 |
 | ENSG00000003979 |   2  |    2 |
 | ENSG00000007613 |   4  |    4 |