I have code that creates my desired output; however, it is painfully slow. I have two input data sets(metaClustering_perCell, data_clean). Each row index of data_clean corresponds to the index position of metaClustering_per cell. here is an example of the two data sets.

dput(head(data_clean[1:5],10))

structure(
  list(
    `NA` = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
    EGFP.A = c(326, 314, 341, 0, 198, 295, 325, 309, 400, 328),
    CD43.PE.A = c(435, 402, 469, 283, 303, 371, 442, 363, 444, 358),
    CD45.PE.Vio770.A = c(399, 385, 379, 438, 384, 331, 402, 392, 354, 430),
    CD235a_41a.APC.A = c(412, 618, 239, 562, 661, 193, 363, 385, 408, 265),
    APC.Vio770.A = c(447, 491, 444, 437, 477, 328, 453, 326, 353, 0)
  ),
  row.names = c(NA, -10L),
  class = "data.frame"
)

dput(head(metaClustering_perCell,10))

c("1 Population", "1 Population", "1 Population", "1 Population", "1 Population",
"1 Population", "1 Population", "1 Population", "1 Population", "9 Population")

I wish to make a heatmap ultimately with the average values of the markers (EGFP.A, CD43.PE.A.....) but, my data sets will contain almost 2e8 cells that are sorted into a predetermined number of populations. The code that I wrote is shown here that creates 2 empty dataframes. The df_sum stores the running summation of the markers (EGFP.A, CD43.PE.A.....) while df_count takes a running tally of the total events in each population. Ultimately the code then takes the average by dividing the dataframe by the vector. The code is here.

# create an empty matrix
df_sum  <- data.frame(matrix(ncol = length(data_clean), nrow = num_clusters))
pops_header <- unique(metaClustering_perCell)
rownames(df_sum) <- pops_header
colnames(df_sum) <- colnames(data_clean)

# creates empty table for storing the count values
df_count <- data.frame(matrix(ncol = num_clusters, nrow = 1))
colnames(df_count) <- pops_header



df[is.na(df_sum)] <- 0
df_count[is.na(df_count)] <- 0



for (i in 1:length(metaClustering_perCell)){

  # only takes one row at a time of original data
  volt_vals <- data_clean[i,]
  
  # find the column to place it in (population)
  pop <- metaClustering_perCell[i]
  
  # Tally for each population
  df_count[1,pop] <- df_count[1,pop]   1
  
  # adds to the previous value in the dataframe
  for (a in colnames(volt_vals)){
    df_sum[pop, a] <- volt_vals[a]   df_sum[pop, a]
  }
    
  # creates another dataframe same size as df to overwrite with the averages
  df_aves <- df_sum
  
  
  # Divide the df_=
  for (n in pops_header){
    df_aves[n,] <- mapply('/', df_sum[n,], df_count[n])
  }
}

The output that I get is this (I clipped them off to make is easier to see)

>head(df_sum[1:3],10)

>head(df_count[1:5])

> head(df_aves[1:3], 10)

The data frame of averages of each population and their values for each of the column headers(markers) is exactly what I want..... however, it is brutally slow.... and I mean brutal. This is my first week with R (I come knowing self taught python from the stacks), so please explain thoroughly. Thanks for the help.

CodePudding user response：

It's unclear exactly what you're trying to achieve, and the sample data is too sparse to help disambiguate, but here are my two guesses:

Averages Of Each Marker Within Each Population

This interpretation is most consistent with your sample output, in which each population (cluster) appears only once, as if the data were aggregated by population.

It is very straightforward in R to group data and then summarize it with aggregate functions.

Solution 1.1: dplyr

Here's a solution with the dplyr package, which is syntactically intuitive:

library(dplyr)

data_clean %>%
  # Overwrite the 'NA' column with the cluster labels.
  mutate(`NA` = metaClustering_perCell) %>%
  # Group by cluster labels...
  group_by(`NA`) %>%
  # ...and summarize the average of each marker (column).
  summarize(across(everything(), mean))

Solution 1.2: data.table

Here's a solution with data.table, which offers even better performance.

library(data.table)

as.data.table(data_clean)[,
  # Overwrite the 'NA' column with the cluster labels.
  ("NA") := metaClustering_perCell
][,
  # Summarize the average of each marker (column), as grouped by cluster.
  lapply(.SD, mean), by = `NA`
]

Result

Let the values for data_clean and metaClustering_perCell be as sampled in your question.

While the first result (1.1) will be a tibble and the second (1.2) a data.table, each will contain the following data:

          NA   EGFP.A CD43.PE.A CD45.PE.Vio770.A CD235a_41a.APC.A APC.Vio770.A
1 Population 278.6667  390.2222         384.8889         426.7778     417.3333
9 Population 328.0000  358.0000         430.0000         265.0000       0.0000

Cumulative Averages ("") As Of Each Observation

This interpretation is most consistent with your algorithm, which seems to calculate its metrics (average, etc.) on a running basis, for each observation (row).

R also facilitates cumulative averages, sums, and so forth. It is far more efficient to leverage vectorized operations than to compute these metrics iteratively (with loops, the *apply() family, etc.) for each row.

Solution 2.1: dplyr

Serendipitously, dplyr already has its own cummean() function.

library(dplyr)

data_clean %>%
  # Overwrite the 'NA' column with the cluster labels.
  mutate(`NA` = metaClustering_perCell) %>%
  # Group by cluster labels...
  group_by(`NA`) %>%
  # ...and overwrite each marker (column) with its running average.
  mutate(across(everything(), cummean)) %>% ungroup()

Solution 2.2: data.table

With data.table we can improvise our own (anonymous) function

function(x) {
  cumsum(x) / seq_along(x)
}

which divides the running sum by the running count, to calculate the cumulative mean along a vector (column). We could also import dplyr and use cummean in place of our function.

library(data.table)

as.data.table(data_clean)[,
  # Overwrite the 'NA' column with the cluster labels.
  ("NA") := metaClustering_perCell
][,
  # Overwrite each marker (column) with its running average, as grouped by cluster.
  lapply(.SD, function(x)cumsum(x)/seq_along(x)), by = `NA`
]

Result

Let the values for data_clean and metaClustering_perCell be as sampled in your question.

While the first result (1.1) will be a tibble and the second (1.2) a data.table, each will contain the following data:

          NA   EGFP.A CD43.PE.A CD45.PE.Vio770.A CD235a_41a.APC.A APC.Vio770.A
1 Population 326.0000  435.0000         399.0000         412.0000     447.0000
1 Population 320.0000  418.5000         392.0000         515.0000     469.0000
1 Population 327.0000  435.3333         387.6667         423.0000     460.6667
1 Population 245.2500  397.2500         400.2500         457.7500     454.7500
1 Population 235.8000  378.4000         397.0000         498.4000     459.2000
1 Population 245.6667  377.1667         386.0000         447.5000     437.3333
1 Population 257.0000  386.4286         388.2857         435.4286     439.5714
1 Population 263.5000  383.5000         388.7500         429.1250     425.3750
1 Population 278.6667  390.2222         384.8889         426.7778     417.3333
9 Population 328.0000  358.0000         430.0000         265.0000       0.0000