How to convert structured text to data columns in R?

Question

I have a fairly large (1000 pages) list of structured text which I would like to convert into a data frame (preferably using R, but I am open to suggestions).

The text file looks as follows:

AC-Acrelândia
TV    Canal 18    AINDA NÃO OUTORGADO
RTV  Canal 9    RADIO TV DO AMAZONAS LTDA
RTV  Canal 10    RADIO TV DO AMAZONAS LTDA(REDENCAO)
TVD  Canal 15    RADIO TV DO AMAZONAS LTDA
TVD  Canal 15    AINDA NÃO OUTORGADO(REDENÇÃO)
FM   88,5 MHz   RADIO E TV MAIRA LTDA

AC-Assis Brasil
TV    Canal 34    AINDA NÃO OUTORGADO
RTV  Canal 6    AMAZONIA CABO LTDA
RTV  Canal 10    RADIO TV DO AMAZONAS LTDA
RTV  Canal 13    AINDA NÃO OUTORGADO
RTV  Canal 45    FUNDACAO JOAO PAULO II

and I would like to convert it into something like this:

AC  Acrelândia    TV    Canal 18    AINDA NÃO OUTORGADO
AC  Acrelândia    RTV   Canal 9     RADIO TV DO AMAZONAS LTDA
AC  Acrelândia    RTV   Canal 10    RADIO TV DO AMAZONAS LTDA(REDENCAO)
....

It seems readLines() is a good start, but I am having a hard time with the structure.

Answer 1

To CSV File

Since you are open to other languages, I suggest a solution in Python. It produces a csv file looking like this:

"AC","Acrelândia","TV","Canal 18","AINDA NÃO OUTORGADO"
"AC","Acrelândia","RTV","Canal 9","RADIO TV DO AMAZONAS LTDA"
"AC","Acrelândia","RTV","Canal 10","RADIO TV DO AMAZONAS LTDA(REDENCAO)"
"AC","Acrelândia","TVD","Canal 15","RADIO TV DO AMAZONAS LTDA"
"AC","Acrelândia","TVD","Canal 15","AINDA NÃO OUTORGADO(REDENÇÃO)"
"AC","Acrelândia","FM","88,5 MHz","RADIO E TV MAIRA LTDA"
"AC","Assis Brasil","TV","Canal 34","AINDA NÃO OUTORGADO"
"AC","Assis Brasil","RTV","Canal 6","AMAZONIA CABO LTDA"
"AC","Assis Brasil","RTV","Canal 10","RADIO TV DO AMAZONAS LTDA"
"AC","Assis Brasil","RTV","Canal 13","AINDA NÃO OUTORGADO"
"AC","Assis Brasil","RTV","Canal 45","FUNDACAO JOAO PAULO II"

The Code

This makes two assumptions: (1) The first line in the file, or any line following a blank line is a station name and (2) Fields are separated by two or more spaces

#-*- coding: utf-8 -*-

import re
import csv

# CREATE DATA STRUCTURE TO SIMULATE READING A TEXT FILE

data = u'''AC-Acrelândia
TV    Canal 18    AINDA NÃO OUTORGADO
RTV  Canal 9    RADIO TV DO AMAZONAS LTDA
RTV  Canal 10    RADIO TV DO AMAZONAS LTDA(REDENCAO)
TVD  Canal 15    RADIO TV DO AMAZONAS LTDA
TVD  Canal 15    AINDA NÃO OUTORGADO(REDENÇÃO)
FM   88,5 MHz   RADIO E TV MAIRA LTDA

AC-Assis Brasil
TV    Canal 34    AINDA NÃO OUTORGADO
RTV  Canal 6    AMAZONIA CABO LTDA
RTV  Canal 10    RADIO TV DO AMAZONAS LTDA
RTV  Canal 13    AINDA NÃO OUTORGADO
RTV  Canal 45    FUNDACAO JOAO PAULO II'''.split('\n')

def read_records():
    for line in data:
        yield line


# INITIALIZE SPLITTER, READ RECORDS AND WRITE TO CSV FILE

splitter = re.compile('\s{2,}')
change_station = True
station = ''

f = open('./output.csv', 'w')
writer = csv.writer(f, quoting=csv.QUOTE_ALL)

for rec in read_records():
    rec = rec.strip()
    if rec == '':
        change_station = True
    elif change_station == True:
        station = rec.replace('-', '  ')
        change_station = False
    else:
        record = station + '  ' + rec
        record = record.encode('utf-8')
        record = re.split(splitter, record)
        writer.writerow(record)

f.close()

# READ IN FILE AND PRINT TO CONSOLE FOR DEMO PURPOSES

f = open('./output.csv', 'r')
print ''.join( f.readlines() )
f.close()

Answer 2

If you're happy to use Python, then this will work (assuming tab-separated output):

import os

program = open('program', 'r')
new_prog = open('new_prog', 'w')

# Get initial state and city
state, city = program.readline().rstrip().split('-')

for line in program:
    # Blank lines denote city change
    if not line.strip():
        line = program.next()
        state, city = line.rstrip().split('-')
        line = program.next()

    band, cname, channel, show = line.rstrip().split(None, 3)

    new_line = '\t'.join([state, city, band, cname, channel, show, os.linesep])
    new_prog.write(new_line)

program.close()
new_prog.close()