0

I want to find out mean of each column in my dataset, which contains null / blank values.

I've attached screenshots of actual and sample data for reference.

image 1 image 2

Z.Lin
  • 28,055
  • 6
  • 54
  • 94
shanti
  • 57
  • 1
  • 8
  • 1
    Does this answer your question? [How to find the mean of a column in R](https://stackoverflow.com/questions/37908949/how-to-find-the-mean-of-a-column-in-r) – Wil Jul 13 '20 at 10:37
  • This does not answer the question. – Georgery Jul 13 '20 at 11:44

4 Answers4

1

I don't see the data? Usually, you just have to calculate mean() of extracted column from a data frame if a column is numerical. And you become immediate data frame with importing an excel file in rstudio.

It's is easier to work with the data frame if you name your columns.

dataframe_name <- c(column1, column2, column3)

Then, you can easily extract the mean of a column.

mean(dataframe_name$column1)
Petra Pavlovic
  • 19
  • 3
  • Sir, there are more than 100 column, and its is difficult to write and extract each column by typing. I need a loop function and also the datasets contain Na/null value – shanti Jul 13 '20 at 09:48
0

Best thing to do, is to add a helper column, using this formula:

=IfError(B1;0)

(Obviously you might need to use another cell reference)
This formula replaces all error values by zero, you can use this column as an input for calculating your averages.

Dominique
  • 16,450
  • 15
  • 56
  • 112
0

There are two simple ways that can solve your problem.

  1. Set up a separate table next to the one you use and take the mean of the respective cells:
=IfError(Cell;0)
=MEAN(StartCell:EndCell)
  1. Replace the null values with 0 by using the replace all function and take the mean value afterwards.

Note: Both approaches will take into consideration the zeros when calculating the mean. If you want to avoid this, replace the null values with "nothing". Hope that helps.

Malte Susen
  • 767
  • 3
  • 13
  • 36
0
library(tidyverse)

df %>%
    summarise_if(is.numeric, mean, na.rm = TRUE)

This calculates the mean for every numeric column in your dataframe.

Georgery
  • 7,643
  • 1
  • 19
  • 52
  • Thanks a lot , It works for me. Can you please suggest me code , if in place of null value the data set contain -9999 , then for this what will be the code? – shanti Jul 13 '20 at 11:29
  • Create an additional function: `NA_is_9999 <- function(x) replace(x, x == -9999, NA)` and then do `df %>% mutate_if(is.numeric, NA_is_9999) %>% summarise_if(is.numeric, mean, na.rm = TRUE)`. – Georgery Jul 13 '20 at 11:43
  • Happy to help. :) – Georgery Jul 14 '20 at 07:25
  • can you please suggest me code to clip multiple shapefile from a single shapefile in one go. I am doing one by one but not in one go, please help me? – shanti Jul 18 '20 at 07:51