1
$\begingroup$

I have some tsRNA count data in excel which I need to add selectively in blocks based on the count

refID   Count
1) tRNA-Ala-AGC-1   1
tRNA-Ala-AGC-1  2
tRNA-Ala-AGC-1  2
tRNA-Ala-AGC-1  1
tRNA-Ala-AGC-1  3
tRNA-Ala-AGC-1  300
tRNA-Ala-AGC-1  51
tRNA-Ala-AGC-1  86
tRNA-Ala-AGC-1  6
tRNA-Ala-AGC-1  2
tRNA-Ala-AGC-1  1
tRNA-Ala-AGC-1  5

2)tRNA-Ala-AGC-11 1
tRNA-Ala-AGC-11 2
tRNA-Ala-AGC-11 1
tRNA-Ala-AGC-11 64
tRNA-Ala-AGC-11 1
tRNA-Ala-AGC-11 1
tRNA-Ala-AGC-11 2
tRNA-Ala-AGC-11 3
tRNA-Ala-AGC-11 1
tRNA-Ala-AGC-11 1
tRNA-Ala-AGC-11 1

3) tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3
tRNA-Ala-AGC-3

I am using sum and subtotal functions in excel to get the total counts of each of the blocks. I have about 22,000 values and several hundred groups like this. Is there a way to sum up all such blocks all at once based on the groups or at least a shorter way to sum things work other than autosum and subtotal?

Any help will be useful.

Improve this question
$\endgroup$
2
  • $\begingroup$ Please don't do bioinformatics in excel. $\endgroup$
    – terdon ♦
    Oct 12, 2022 at 8:55
  • $\begingroup$ Thanks for including the input here, but it doesn't look like what I'd expect to see in a well-formatted Excel spreadsheet. Are those X) really in the file? Why are there no counts for tRNA-Ala-AGC-3? Does your input table have empty lines? $\endgroup$
    – gringer ♦
    Oct 12, 2022 at 10:42

2 Answers 2

Reset to default
1
$\begingroup$

Suppose the header is on row 1 and starts at column 'A'. Then, for the above example, just point to a cell at say C1 to C3 and write ...

=sum(B2:B12)
=sum(B14:B25)
=sum(B27:B38)

This gives the subtotals.

For the total sum point the mouse at D1 and write:

=sum(B2:B38)

... except of course the real value is ...

=sum(B2:B22000)

In all cases you must put the = to start the cell.

Its better to try out R, or Python pandas or just a Python list print (sum([1,2,3,4,5]) but Excel's okay.

The issue is its slow, its taking hours to calculate the sum of 22 000 cells - thats not a lot even for Excel. In Python that wouldn't even exceed 1 second, literally. What you are facing is a RAM bottleneck - there is literally no other explanation.

Two suggestions firstly, look at Windows Excel, particularly if you are using OSX. This is because historically Windows was much more RAM efficient than OSX. Now however, I don't know. Secondly, simply switch to a desktop with 4 Gig RAM or more. Literally thats all it will take.

The drop back position is to do the calculation in two halves on the low RAM machine, i.e. split the data set in two. Not a cool solution but it will probably work. The final solution is to look at the stuff below the line.

The solutions are to shift to VB which none of us code in these days, but its the code underneath Excel, or the alternative is to write a custom script in a different language that will do the adding such as Python, R ... even bash.

Improve this answer
$\endgroup$
3
  • $\begingroup$ I have already done that but since there are so many cells I have to do the sums as you suggested for each subgroup individually and it takes several hours to do such sums, hence I am looking for a quick work around. $\endgroup$
    – Aranyak Goswami
    Oct 12, 2022 at 3:41
  • $\begingroup$ Hi @AranyakGoswami what you are up against is a RAM bottleneck. Excel shouldn't have trouble with the calculation on a standard desktop. $\endgroup$
    – M__ ♦
    Oct 12, 2022 at 15:27
  • $\begingroup$ I suspect @AranyakGoswami is saying that it takes several hours to select regions around the counts for each subgroup, and add the associated formula into the correct cell near the gene counts. At about 1 minute per gene, several hours could mean about 10,000 genes. $\endgroup$
    – gringer ♦
    Oct 13, 2022 at 23:34
0
$\begingroup$

It looks like what you want to do is a pivot table, which provides summary statistics for fields, grouped by specified categories. As a prerequisite, this requires an input dataset that is well formatted; more generally:

  • Put each observation or sample in its own row; don't leave row gaps between samples or groups. Leaving gaps makes bulk analysis more difficult and time consuming. It can still be done, it just means that the data needs to be pre-cleaned, or alternatively analysed.
  • Put all your variables in columns - the thing that vary between samples, like ‘strain’ or ‘DNA-concentration’
  • Have column names be explanatory, but without spaces
  • Do not combine multiple pieces of information in one cell

For more information, see this Data Carpentries Workshop on the recommended way to structure data in spreadsheets:

https://datacarpentry.org/organization-genomics/01-tidiness/index.html#structuring-data-in-spreadsheets

If the data is prepared in this way, then selecting the whole table and creating a pivot table should work. Unfortunately, the programs keep changing where in the menu that can be found. Currently in LibreOffice, it's Data -> Pivot Table -> Insert or Edit..., wheareas on my version of Excel on Mac OSX, it's found at Data -> Summarise With Pivot Table.

This will load up a visual interface that allows you to drag and drop fields to summarise. For this data, you'd probably want RefID in rows, and sum(Count) as the data field:

Pivot table customisation dialog, showing RefID in rows, and sum(Count) as the data field

After setting this up and clicking Ok, you should be greeted with a smaller table containing summarised variables:

Summary table, showing counts per RefID

Improve this answer
$\endgroup$
2
  • $\begingroup$ I have purposely added a blank space after each block so that I may avoid errors for all the sums made. $\endgroup$
    – Aranyak Goswami
    Oct 13, 2022 at 19:02
  • $\begingroup$ It may be that the pivot table will work with gaps, but bear in mind that leaving gaps in general makes bulk analysis more difficult. It can still be done, it just means that the data needs to be pre-cleaned, or alternatively analysed. I've added this clarification to my answer. $\endgroup$
    – gringer ♦
    Oct 13, 2022 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.