Python Pandas create dataframe from file with newline splits?

Question

I have a text file that is a transcript with timestamps, it looks like this:

00:25
hold it miles lunch and remember I'm
00:30
working late tonight again man you're a
00:34
total slave to that business of yours
00:36
nobody's a slave to their own dream

I'm trying to figure out how to import it into a Pandas Dataframe so it looks like this:

[Time] [Text]
00:25  hold it miles lunch and remember I'm
00:30  working late tonight again man you're a
00:34  total slave to that business of yours
00:36  nobody's a slave to their own dream

I'm embarrassed to say that I'm not even sure where to begin... all the methods I know and tried produce this:

  row1  00:25
  row2  hold it miles lunch and remember I'm
  row3  00:30
  row4  working late tonight again man you're a
  row5  00:34
  row6  total slave to that business of yours
  row7  00:36
  row8  nobody's a slave to their own dream

I found this question and it looks to be the same issue but I can't tell how to apply it when creating a dataframe.

Thank you for helping me!

Answer 1

Here is a method to accomplish this:

# Import the sample data
data='''00:25
hold it miles lunch and remember I'm
00:30
working late tonight again man you're a
00:34
total slave to that business of yours
00:36
nobody's a slave to their own dream'''

# Create a list containing every line
data = data.split('\n')

# Parse the data, assigning every other row to a different column
col1 = [data[i] for i in range(0,len(data),2)]
col2 = [data[i] for i in range(1,len(data),2)]

# Create the data frame
df = pd.DataFrame({'Time': col1, 'Text': col2})
print(df)

    Time                                     Text
0  00:25     hold it miles lunch and remember I'm
1  00:30  working late tonight again man you're a
2  00:34    total slave to that business of yours
3  00:36      nobody's a slave to their own dream

Answer 2

Another way to do it by splitting every line and assigning every other row to a different column e.g Time and Text. Finally make it a DataFrame from the modified dictionary.

import pandas as pd

# Read your files here 
files = ['text.txt'] #  you can add file or bunch of files
data = {}
for f in files:
  with open (f, "r") as myfile:
    all_lines = myfile.read().splitlines() # split by line
    # assign every alternative line to Time and Text index alternatively
    data['Time'], data['Text'] = all_lines[::2],  all_lines[1::2]

# create dataframe from the dictionary
df = pd.DataFrame(data)
print(df)

Output:

    Time                                     Text
0  00:25     hold it miles lunch and remember I'm
1  00:30  working late tonight again man you're a
2  00:34    total slave to that business of yours
3  00:36      nobody's a slave to their own dream