Is it possible to convert lines from a text file into columns to get a dataframe?

user_10413736 0
Viewed 267

I have a text file containing information on book title, author name, and country of birth which appear in seperate lines as shown below:

Oscar Wilde
De Profundis    
Ireland 
Nathaniel Hawthorn  
Birthmark   
USA 
James Joyce
Ulysses
Ireland
Walt Whitman
Leaves of Grass 
USA

Is there any way to convert the text to a dataframe with these three items appearing as different columns:

ID  Author                Book               Country
1  "Oscar Wilde"          "De Profundis"     "Ireland"
2  "Nathaniel Hawthorn"   "Birthmark"        "USA"
text-mining r
3 Answers

There are built-in functions for dealing with this kind of data:

data.frame(scan(text=xx, multi.line=TRUE,
  what=list(Author="", Book="", Country=""), sep="\n"))

#              Author            Book Country
#1        Oscar Wilde    De Profundis Ireland
#2 Nathaniel Hawthorn       Birthmark     USA
#3        James Joyce         Ulysses Ireland
#4       Walt Whitman Leaves of Grass     USA
user_4968030

There aren't any built in functions that handle data like this. But you can reshape your data after importing.

#Test data
xx <- "Oscar Wilde
De Profundis
Ireland
Nathaniel Hawthorn
Birthmark
USA
James Joyce
Ulysses
Ireland
Walt Whitman
Leaves of Grass
USA"
writeLines(xx, "test.txt")

And then the code

library(dplyr)
library(tidyr)
lines <- read.csv("test.txt", header=FALSE)
lines %>% 
  mutate(
    rid = ((row_number()-1) %% 3)+1,
    pid = (row_number()-1) %/%3 + 1) %>% 
  mutate(col=case_when(rid==1~"Author",rid==2~"Book", rid==3~"Country")) %>% 
  select(-rid) %>% 
  pivot_wider(names_from=col, values_from=V1)

Which returns

# A tibble: 4 x 4
    pid Author             Book            Country
  <dbl> <chr>              <chr>           <chr>  
1     1 Oscar Wilde        De Profundis    Ireland
2     2 Nathaniel Hawthorn Birthmark       USA    
3     3 James Joyce        Ulysses         Ireland
4     4 Walt Whitman       Leaves of Grass USA
user_23720640

You can create a 3-column matrix from one column of data.

dat <- read.table('data.txt', sep = ',')

result <- matrix(dat$V1, ncol = 3, byrow = TRUE) |>
  data.frame() |>
  setNames(c('Author', 'Book', 'Country'))

result <- cbind(ID = 1:nrow(result), result)

result
#  ID             Author            Book Country
#1  1        Oscar Wilde    De Profundis Ireland
#2  2 Nathaniel Hawthorn       Birthmark     USA
#3  3        James Joyce         Ulysses Ireland
#4  4       Walt Whitman Leaves of Grass     USA
user_39629140
Related