Read horizontal table into data frame in R-CodePudding

The .txt file is in the form:

high_quality 1.2 .9 .7 1.0 1.7 1.7 1.1 .9 1.7 1.9 1.3 2.1 1.6 1.8 1.4 1.3 1.9 1.6 .8 2.0 1.7 1.6 2.3 2.0

poor_quality 1.6 1.5 1.1 2.1 1.5 1.3 1.0 2.6

high_quality line and poor_quality line have 1 blank line between them.

I have tried:

fabric_df = read.table(file="../data2/fabric.txt",sep = " ", header = TRUE)

But got the error:

"Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, : line 1 did not have 25 elements"

Does anybody know how to properly import this file into a data frame?

CodePudding user response：

You can do data.table::fread("file.txt", fill=TRUE) which will give

    V1  V2  V3  V4  V5  V6  V7  V8  V9 V10 V11 V12 V13 V14 V15 V16 V17 V18 V19 V20 V21 V22 V23 V24
1: 1.2 0.9 0.7 1.0 1.7 1.7 1.1 0.9 1.7 1.9 1.3 2.1 1.6 1.8 1.4 1.3 1.9 1.6 0.8   2 1.7 1.6 2.3   2
2: 1.6 1.5 1.1 2.1 1.5 1.3 1.0 2.6  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA

CodePudding user response：

Here's another option and if you want to get it into 2 columns:

df <- read.table(text = "high_quality 1.2 .9 .7 1.0 1.7 1.7 1.1 .9 1.7 1.9 1.3 2.1 1.6 1.8 1.4 1.3 1.9 1.6 .8 2.0 1.7 1.6 2.3 2.0

poor_quality 1.6 1.5 1.1 2.1 1.5 1.3 1.0 2.6", fill=TRUE)

# Or read in the .txt file
# df <- read.table("fabric.txt", fill=TRUE)

library(tidyverse)

df %>% 
  t() %>% 
  row_to_names(row_number = 1) %>% 
  as.data.frame() %>% 
  mutate(across(everything(), as.numeric)) %>% 
  `rownames<-`(., NULL)

Or in base R to get in same format:

df <- t(read.table("fabric.txt", fill=TRUE))

colnames(df) <- df[1,]
df <- df[-1,]
row.names(df) <- NULL
df1 <- as.data.frame(apply(df, 2, as.numeric))

Output

   high_quality poor_quality
1           1.2          1.6
2           0.9          1.5
3           0.7          1.1
4           1.0          2.1
5           1.7          1.5
6           1.7          1.3
7           1.1          1.0
8           0.9          2.6
9           1.7           NA
10          1.9           NA
11          1.3           NA
12          2.1           NA
13          1.6           NA
14          1.8           NA
15          1.4           NA
16          1.3           NA
17          1.9           NA
18          1.6           NA
19          0.8           NA
20          2.0           NA
21          1.7           NA
22          1.6           NA
23          2.3           NA
24          2.0           NA