Count multi range + Same Column
2
1
Entering edit mode
5 weeks ago
shabbas12 ▴ 10

Dear all,

I have an excel data file like; enter image description here

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?

python Excel • 286 views
ADD COMMENT
0
Entering edit mode

countif ??

Please do not post images of the data.

ADD REPLY
0
Entering edit mode

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

ADD REPLY
0
Entering edit mode
5 weeks ago
ronald • 0

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.

ADD COMMENT
0
Entering edit mode
5 weeks ago
zorbax ▴ 390

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 |
ADD COMMENT

Login before adding your answer.

Traffic: 709 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6