英文:
Is there a way to summarise by percentage in R while including the data in a new data frame?
问题
我在我的工作中经常使用Excel和R，我一直在尝试自动化一种我的老板要求我检查数据质量的表格。我最近才开始使用R，所以我的代码还不是很好。
我的想法是创建一个数据框，每一列都汇总了这些向量的信息。数据框中所有NA的总数，数据框中NA的百分比，然后根据某些列对NA在某个水平上的数量进行过滤。
我尝试过的代码如下：
rowsna <- c("总NA数", "% NA占比", "变量1的NA数量，在水平1上", ...)na_count <- df %>% summarise_all(~sum(is.na(.)))na_count[2, ] <- df %>% summarise_all(~mean(is.na(.)))na_count[3, ] <- df %>% filter(变量 == 值) %>% summarise_all(~sum(is.na(.)))...row.names(na_count) <- rowsnana_count <- as.data.frame(t(na_count))na_count$variable
问题是，我不知道如何计算na_count[2, ]部分的缺失百分比。如果可能的话，我想要一些帮助。
英文:
I'm working a lot using Excel and R in my job and I've been trying to automatize a type of form my Boss asks me about the data quality. I've just recently started working with R so my code isn't the best.
The idea is to do a data.frame that summarizes in each column these vectors. Sum of all na's in the data.frame, percentage of NA in the data.frame and then filtering by some columns is the n of NAs in a level.
The code I've tried is the following one:
rowsna <- c("Total NA", "% NA", "n NA Variable 1, level 1",...)na_count <- df %>% summarise_all(~sum(is.na(.)))na_count[2, ] <- df %>% summarise_all(~mean(is.na(.)))na_count[3, ] <- df %>% filter(variable == value) %>% summarise_all(~sum(is.na(.)))...row.names(na_count) <- rowsnana_count <- as.data.frame(t(na_count))na_count$variable
The thing is, I've got no idea how to calc the percentage of missing in the na_count[2 , ] part. I would like some help if possible.
答案1
得分: 1
这似乎是你想要的：
library(tidyverse)# 虚构数据集df <- tibble(id = 1:10,x = c(1:9, NA),y = c(1:5, rep(NA, 5)),z = rep(NA, 10))NA_df <- df %>%# 计算每列中NA的数量summarise(across(everything(), ~ sum(is.na(.x)))) %>%# 然后将其长格式化pivot_longer(cols = everything()) %>%# 接着计算每列中NA的百分比mutate(mean = 100*value/nrow(df))# 假设我们只想获取NA少于5个的列threshold <- 5good_columns <- NA_df %>%filter(value < threshold) %>%pull(name)# 现在我们可以使用good_columns向量来子集化原始数据框df %>%select(all_of(good_columns))# 一个tibble: 10 × 2id x<int> <int>1 1 12 2 23 3 34 4 45 5 56 6 67 7 78 8 89 9 910 10 NA
英文:
It sounds like this is what you want:
library(tidyverse)# toy datasetdf <- tibble(id = 1:10,x = c(1:9, NA),y = c(1:5, rep(NA, 5)),z = rep(NA, 10))NA_df <- df %>%# we find the number of NAs in each columnsummarise(across(everything(), ~ sum(is.na(.x)))) %>%# then we pivot it longerpivot_longer(cols = everything()) %>%# then find the percentage of NAs in each columnmutate(mean = 100*value/nrow(df))# let's say for the sake of argument that we only want to get columns with less than 5 NAsthreshold <- 5good_columns <- NA_df %>%filter(value < threshold) %>%pull(name)# now we can use the good_columns vector to subset the original dataframedf %>%select(all_of(good_columns))# A tibble: 10 × 2id x<int> <int>1 1 12 2 23 3 34 4 45 5 56 6 67 7 78 8 89 9 910 10 NA
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论