How to remove empty rows after csv data import in r

user_3984584 0
Viewed 3519

I combine several large csv files with arrival (ATA) and departure (ATD) times of objects. After combining the files I cannot remove <NA> rows using familiar methods. The origin may be in difference between Windows and Unix files in newline and carriage returns. But I don't want to alter the csv files. I want to be able to correct the data frame in R.

I combine several large csv files containing the same variables, e.g.:

# read csv files
df1 <- read.csv("data_1.csv", stringsAsFactors = FALSE)
df2 <- read.csv("data_2.csv", stringsAsFactors = FALSE)
df3 <- read.csv("data_3.csv", stringsAsFactors = FALSE)

# combine csv files
combidat <- rbind(df1, df2, df3)

# remove duplicate entries
combidat <- combidat[!duplicated(combidat), ]

To remove entries with <NA> in ID (first column variable), I use one of several:

combidat <- combidat[!is.na(combidat$ID),]
combidat <- combidat[complete.cases(combidat[ , 1]),]
combidat <- combidat[rowSums(is.na(combidat)) != ncol(combidat),]

I also found:

combidat <- combidat[-which(apply(combidat,1,function(x)all(is.na(x)))),]

But I cannot use this approach. If I do combidat becomes empty.

If I check the result:

combidat[is.na(combidat$ID),]

I get:

 [1] ID            ATA            ATD            object
<0 rows> (or 0-length row.names)

However, if I check on inconsistencies, i.e. departure times before arrival times:

combidat[(ATD<ATA),]

I get:

               ID                      ATA                       ATD    object
233      51586002      2016-03-14 09:44:00       2016-03-14 09:00:00    car718
798      54846070      2016-06-19 01:37:00       2016-04-07 23:59:00    car276
4126     56066767      2016-03-31 14:00:00       2016-03-30 07:00:00    car089
NA             NA                     <NA>                      <NA>        NA
NA.1           NA                     <NA>                      <NA>        NA
NA.2           NA                     <NA>                      <NA>        NA
NA.3           NA                     <NA>                      <NA>        NA
NA.4           NA                     <NA>                      <NA>        NA
NA.5           NA                     <NA>                      <NA>        NA
NA.6           NA                     <NA>                      <NA>        NA
NA.7           NA                     <NA>                      <NA>        NA

What I hope to get is:

               ID                      ATA                       ATD    object
233      51586002      2016-03-14 09:44:00       2016-03-14 09:00:00    car718
798      54846070      2016-06-19 01:37:00       2016-04-07 23:59:00    car276
4126     56066767      2016-03-31 14:00:00       2016-03-30 07:00:00    car089

Any explanation what I do wrong and how to correct it, would be much appreciated.

[Addition June 28, 2019] Something goes wrong with the imported csv files. Somehow newlines/carriage returns within a data field, get interpreted as end of record markers. I have juggled with 'quote':

df1 <- read.csv("data_1.csv", stringsAsFactors = FALSE, quote = "\"'")

And this has some impact, but I do not get it right.

na dataframe csv r
3 Answers
combidat <- combidat[which(!is.na(combidat$ID)),]
user_89362390

As noted by gdevaux, maybe your "NA" are characters. In that case you could filter your data with the dplyr package (you can also do it with base R as you attempted):

library(dplyr)
combidat<- combidat%>% filter(!ID=="NA")

It may also have spaces, in that case you can trim in the importing or trim the column and retry the code above. 

library(stringr)    
combidat<- combidat%>% 
      mutate(ID=str_trim(ID, side = "both"))%>%
      filter(!ID=="NA")

Lastly, it seems that your ID column is just composed of numeric ID. In that case you could make your column numeric and hence coerce the NA values to real NA. 

    combidat$ID<- as.numeric(combidat$ID)
combidat<- combidat%>% filter(!is.na(ID))

If values were coerced, R will tell you.

user_132015860

The two answers above would have helped you. Here is another approach to filter empty observation and variables from the data frame.

#install.packages("janitor")
library(janitor)
remove_empty(combidat, "rows")
user_140610770
Related