CODEWITHSUNDEEP
rtext
AYa
AYa

Reputation: 503

Data pivot transformation

I am aware of some packages in R for text mining like tm but I am unable to use it for my task. I have a text file that has data something like this:

 452924301037
    5May2014
       John
    7May2014
       Mark
       Sam
 452924302789
    6May2014
       Bill

I want the above data in a data frame as something like this:

UserID, Date, Names
452924301037,5May2014,John
452924301037,7May2014,Mark Sam
452924302789,6May2014,Bill

How can I do this in R?

Example 2:

Input textfile:

452924301037
    5May2014
       John
           Cricket
           Football
    7May2014
       Mark
           Hockey
452924302789
     6May2014
       Bill
           Billiards

And I want to setup a data frame as below:

Game, Player, Date, UserID 
Cricket, John, 5May2014, 452924301037
Football, John, 5May2014, 452924301037
Hockey, Mark, 7May2014, 452924301037
Billiards, Bill, 6May2014, 452924302789

Upvotes: 1

Views: 110

Answers (1)

Jaap
Jaap

Reputation: 83215

A possible solution using data.table and zoo:

# read the textfile
txt <- readLines('textlines.txt')

# load the needed packages
library(zoo)
library(data.table)

# convert the text to a data.table (an enhanced form of a dataframe)
DT <- data.table(txt = txt)

# extract the info into new columns
DT[grepl('\\d+{8,}', txt), User_id := grep('\\d+{8,}', txt, value = TRUE)
   ][grepl('\\D+{3}\\d+{4}', txt), Date := txt
     ][, (c('User_id','Date')) := lapply(.SD, na.locf, na.rm = FALSE), .SDcols = 2:3
       ][txt!=User_id & txt != Date, .(Names = paste0(txt, collapse = ' ')), by = .(User_id, Date)]

which gives:

        user_id     date    Names
1: 452924301037 5May2014     John
2: 452924301037 7May2014 Mark Sam
3: 452924302789 6May2014     Bill

To see what each step does, run the following code:

# extract the user_id's
DT[grepl('\\d+{8,}', txt), User_id := grep('\\d+{8,}', txt, value = TRUE)][]
# extract the dates
DT[grepl('\\D+{3}\\d+{4}', txt), Date := txt][]
# fill the NA-values of 'User_id' and 'Date' with na.locf from the zoo package
DT[, (c('User_id','Date')) := lapply(.SD, na.locf, na.rm = FALSE), .SDcols = 2:3][]
# filter out the rows where the 'txt'-column has either a 'User_id' or a 'Date'
# collapse the names into one string by 'User_id' and 'Date'
DT[txt != User_id & txt != Date, .(Names = paste0(txt, collapse = ' ')), by = .(User_id, Date)][]

For the added 2nd example, you could do:

DT <- data.table(txt = trimws(txt))

DT[grepl('\\d+{8,}', txt), User_id := grep('\\d+{8,}', txt, value = TRUE)
   ][grepl('\\D+{3}\\d+{4}', txt), Date := txt
     ][, (c('User_id','Date')) := lapply(.SD, na.locf, na.rm = FALSE), .SDcols = 2:3
       ][txt!=User_id & txt != Date
         ][, Name := txt[1], by = .(User_id, Date)
           ][Name != txt]

which gives:

         txt      User_id     Date Name
1:   Cricket 452924301037 5May2014 John
2:  Football 452924301037 5May2014 John
3:    Hockey 452924301037 7May2014 Mark
4: Billiards 452924302789 6May2014 Bill

Upvotes: 2

Related Questions