CODEWITHSUNDEEP
rdate
jeetkamal
jeetkamal

Reputation: 409

reading txt file and converting it to dataframe

I have a .txt file that consists of some investment data. I want to convert the data in file to data frame with three columns. Data in .txt file looks like below.

Date:
06-04-15, 07-04-15, 08-04-15, 09-04-15, 10-04-15
Equity : 
-237.79, -170.37, 304.32, 54.19, -130.5
Debt : 
16318.49, 9543.76, 6421.67, 3590.47, 2386.3

Upvotes: 0

Views: 71

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 101307

If you are going to use read.table(), then the following may help:

Assuming the dat.txt contains above contents, then 

dat <- read.table("dat.txt",fill=T,sep = ",")
df <- as.data.frame(t(dat[seq(2,nrow(dat),by=2),]))
rownames(df) <- seq(nrow(df))
colnames(df) <- trimws(gsub(":","",dat[seq(1,nrow(dat),by=2),1]))

yielding:

> df
       Date   Equity     Debt 
1  06-04-15  -237.79 16318.49
2  07-04-15  -170.37  9543.76
3  08-04-15   304.32  6421.67
4  09-04-15    54.19  3590.47
5  10-04-15   -130.5   2386.3

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 388962

Assuming the text file name is demo.txt here is one way to do this

#Read the file line by line
all_vals <- readLines("demo.txt")
#Since the column names and data are in alternate lines
#We first gather column names together and clean them
column_names <- trimws(sub(":", "", all_vals[c(TRUE, FALSE)]))
#we can then paste the data part together and assign column names to it
df <- setNames(data.frame(t(read.table(text = paste0(all_vals[c(FALSE, TRUE)], 
       collapse = "\n"), sep = ",")), row.names = NULL), column_names)
#Since most of the data is read as factors, we use type.convert to 
#convert data in their respective format.
type.convert(df)

#       Date  Equity     Debt
#1  06-04-15 -237.79 16318.49
#2  07-04-15 -170.37  9543.76
#3  08-04-15  304.32  6421.67
#4  09-04-15   54.19  3590.47
#5  10-04-15 -130.50  2386.30

Upvotes: 3

Related Questions