0

I am new to programming so I apologize for any mistakes. I am trying to make a program where you import a .txt file that is then split by before and after delimiter and put into two columns to then export into an excel file.

I have a list of questions and answers formatted as: "What are the two types of IP addresses?=IPv4 and IPv6." where the delimiter is "=". I want the first column (questions) to be before the = and second column (answers) to be after =.

I've got them separated but I don't know how to get it into columns. Any help would be appreciated.

import pandas as pd

with open(r'C:\Users\Stephanie\OneDrive\Documents\guide3.txt') as guide:
    for x in guide.read().split("="):
        print(x)
    guide.close

The code prints out this. End goal is to have an excel file that looks like this excel file. Any advice or information would be much appreciated. Thank you!

Park
  • 2,446
  • 1
  • 16
  • 25
Stephanie W
  • 29
  • 5
  • If you're trying to use `pandas`, see [`read_csv`](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) and [`to_excel`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html) – Tomerikoo Mar 03 '22 at 10:47
  • By the way, apart from `guide.close` not doing anything (you forgot the `()`, should be `guide.close()`), it is not necessary when using a `with` statement. It takes care of closing the file for you – Tomerikoo Mar 03 '22 at 10:49

1 Answers1

1

I would first combine each column as a list, and use readlines instead:

import pandas as pd
question_list = []
answer_list = []
with open(r'C:\Users\Stephanie\OneDrive\Documents\guide3.txt') as guide:
    for line in guide.readlines():
        question, answer = line.split('=')
        question_list.append(question)
        anser_list.append(answer)

# convert to pandas dataframe
df = pd.DataFrame()
df['Questions'] = question_list
df['Ansers'] = answer_list

# export to csv, which an be read by excel
df.to_excel('guide.xlsx', sheet='sheet1')

You do not need to close, since python does that automaticly when leaving the with block.

Note: I did not test this code yet, but it should put you on the right track

lcdumort
  • 599
  • 3
  • 20
  • @StephanieW check/print what is the value of `line` in the loop, it's possible that it's reading a newline that is empty or with a line that is like `q=` which means there is only one tuple value to unpack. – LearningNoob Mar 03 '22 at 10:01
  • As mentioned above, this probably occurs because there is indeed a line that doesn't correspond with question=answer. Good chance that this is the final line that is just an empty line. You can fix this by just deleting it. If not that means there is one or more entries in your file that do not have the right format. You can skip this by using a try/except statement – lcdumort Mar 03 '22 at 10:30
  • Seems like quite a complication for something already built-in into pandas. Why not just `df = pd.read_csv("guide3.txt", delimiter='=', header=None)`? – Tomerikoo Mar 03 '22 at 10:41
  • Indeed, that might be the most straightforward solution. I however tried to remain similar to OP's solution. But indeed, yours is most straight forward and probably most performant as well – lcdumort Mar 03 '22 at 11:02