Working with text data

[APK]

Hey everyone,

I am working with text data which has the following format in excel:

Interview 1
Speaker
Date
Text

Interview 2
Speaker
Date
Text

Interview 3
Speaker
Date
Text

... and so forth (there are 400 such interviews).

Having read the xlsx file into Python I would now like to structure the text data as follows:

interview_number Speaker Date Text
1 A 01/02/1999
1 A 01/02/1999
1 A 01/02/1999
1 A 01/02/1999
2 B 05/08/2000
2 B 05/08/2000


I am afraid I’m struggling to come up with a Python code/loop that helps me split up the text into such columns. Could you please help me with this?

Thanks a lot!