CODEWITHSUNDEEP
pythonpandasreadlines
Joey
Joey

Reputation: 934

Converting text files to pandas dataframe

I have .TX0 file (some sort of csv txt file) and have converted this to a .txt file via python .readlines(), open(filename, 'w') etc method. I have this new saved txt file but when i try to convert it to a dataframe it's giving me only one column. the txt file is below : 

Empty DataFrame
Columns: [ '"Software Version:", 6.3.2.0646, Date:, 19/08/2015 09:26:04\n',  '"Reprocess Number:", vma2:  261519, Unnamed: 7, \n',  '"Sample Name:",  , Data Acquisition Time:, 18/08/2015 17:23:23\n',  '"Instrument Name:", natural gas (PE ASXL-TCD/FID), Channel:, B\n',  '"Rack/Vial:", 0, 0.1, Operator:, joey.walker\n',  '"Sample Amount:", 1.000000, Dilution Factor:, 1.000000\n',  '"Cycle:", 1, Result File :, \\\\vma2\\TotalChrom\11170_he_tcd001.rst \n',  '"Sequence File :", \\\\vma\C1_C2_binary.seq \n',  '"===================================================================================================================================="\n',  '""\n',  '""\n'.1,  '"condensate analysis (HP4890 Optic - FID)"\n',  '"Peak", Component, Time, Area, Height, BL\n',  '"#", Name, [min], [uV*sec], [uV], \n'.1,  '------, ------, ------.1, ------.2, ------.3, ------\n',  '1, Unnamed: 55, 0.810, 706.42, 304.38, *BB\n',  '2, CH4, 0.900, 1113518.24, 495918.41, *BB\n'.1,  '3, C2H6, 1.373, 901670.23, 295381.12, *BB\n'.2,  '"", Unnamed: 73, Unnamed: 74, ------.4, ------.5, \n'.2,  '"".1, Unnamed: 79, Unnamed: 80, 2015894.89, 791603.91, \n'.3,  '"Missing Component Report"\n',  '"Component", Expected Retention (Calibration File)\n',  '------.1, ------\n'.1,  '"All components were found"\n',  '"Report stored in ASCII file :", C:\\Shared Folders\\TotalChrom\\11170_he_tcd001.TX0 \n']]
Index: []

for easier reading:

Empty DataFrame

Columns: [ '"Software Version:", 6.3.2.0646, Date:, 19/08/2015 09:26:04\n', '"Reprocess Number:", vma2: 261519, Unnamed: 7, \n', '"Sample Name:", , Data Acquisition Time:, 18/08/2015 17:23:23\n', '"Instrument Name:", natural gas (PE ASXL-TCD/FID), Channel:, B\n', '"Rack/Vial:", 0, 0.1, Operator:, joey.walker\n', '"Sample Amount:", 1.000000, Dilution Factor:, 1.000000\n', '"Cycle:", 1, Result File :, \\vma2\TotalChrom\data\Joey\Binary_Mixtures\Std1\11170_he_tcd001.rst \n', '"Sequence File :", \\vma2\TotalChrom\sequences\Joey\C1_C2_binary.seq \n', '"===================================================================================================================================="\n', '""\n', '""\n'.1, '"condensate analysis (HP4890 Optic - FID)"\n', '"Peak", Component, Time, Area, Height, BL\n', '"#", Name, [min], [uV*sec], [uV], \n'.1, '------, ------, ------.1, ------.2, ------.3, ------\n', '1, Unnamed: 55, 0.810, 706.42, 304.38, *BB\n', '2, CH4, 0.900, 1113518.24, 495918.41, *BB\n'.1, '3, C2H6, 1.373, 901670.23, 295381.12, *BB\n'.2, '"", Unnamed: 73, Unnamed: 74, ------.4, ------.5, \n'.2, '"".1, Unnamed: 79, Unnamed: 80, 2015894.89, 791603.91, \n'.3, '"Missing Component Report"\n', '"Component", Expected Retention (Calibration File)\n', '------.1, ------\n'.1, '"All components were found"\n', '"Report stored in ASCII file :", C:\Shared Folders\TotalChrom\data\Joey\Binary_Mixtures\Std1\11170_he_tcd001.TX0 \n']] Index: []

As you can see this is comma separated. Would there be any way of transferring this text to a comma delimited dataframe?

Thanks.

J

Upvotes: 3

Views: 59782

Answers (4)

Hassan yousef
Hassan yousef

Reputation: 1

I've just found an easy solution and it works for my code. You can try this in your cade as well:

f = open('glove.6B.100d.txt', encoding='utf8')

Upvotes: 0

beginner
beginner

Reputation: 85

Here I came with a general answer to this question:

import re
import pandas as pd

#first u have to open  the file and seperate every line like below:

df = open('file.txt', "r")
lines = df.readlines()
df.close()

# remove /n at the end of each line
for index, line in enumerate(lines):
      lines[index] = line.strip()



#creating a dataframe(consider u want to convert your data to 2 columns)

df_result = pd.DataFrame(columns=('first_col', 'second_col'))
i = 0  
first_col = "" 
second_col = ""  
for line in lines:
    #you can use "if" and "replace" in case you had some conditions to manipulate the txt data
    if 'X' in line:
        first_col = line.replace('X', "")
    else:
        #you have to kind of define what are the values in columns,for example second column includes:
        second_col = re.sub(r' \(.*', "", line)
        #this is how you create next line data
        df_result.loc[i] = [first_col, second_col]
        i =i+1

Upvotes: 2

LOrD_ARaGOrN
LOrD_ARaGOrN

Reputation: 4506

you can try below code to convert text file into dataframe.

data = pd.read_csv('file.txt', sep=',')

Hope its self explanatory.

Upvotes: 2

Eric
Eric

Reputation: 2729

You can try to use the below function and it will helps you load all the data from your local csv file

ps.read_csv()

More details can be found in pandas.read_csv tutorial

Upvotes: 2

Related Questions