CSV file from txt using pandas


I have a txt file with info inside of it, separated for every deal with \n symbol.

DEAL: 896
CITY: New York
MARKET: Manhattan
PRICE: $9,750,000
ASSET TYPE: Rental Building
SF: 8,004
PPSF: $1,218
DATE: 11/01/2017

Is there any way to make a csv (or another) table with headers, specified like CITY, MARKET, etc. with pandas or csv module? All the info from specific title should go into corresponding header

- - Source

Answers

answered 6 day ago Prayson W. Daniel #1

Use Pandas to input it and then transform/pivot your table.

import pandas as pd

df = pd.read_csv('data.txt',sep=':',header=None)

df = df.set_index(0).T

Example

import pandas as pd

data = '''
DEAL: 896
CITY: New York
MARKET: Manhattan
PRICE: $9,750,000
ASSET TYPE: Rental Building
SF: 8,004
PPSF: $1,218
DATE: 11/01/2017
'''

txt_like = pd.compat.StringIO(data)


df = pd.read_csv(txt_like,sep=':',header=None)

print(df.set_index(0).T)

Results:enter image description here

answered 6 day ago d_kennetz #2

Updated to navigate around using : as a delimiter:

import pandas as pd
new_temp = open('temp.txt', 'w') # writing data to a new file changing the first delimiter only
with open('fun.txt') as f:
    for line in f:
        line = line.replace(':', '|', 1) # only replace first instance of : to use this as delimiter for pandas
        new_temp.write(line)
new_temp.close()

df = pd.read_csv('temp.txt', delimiter='|', header=None)
df = df.set_index([0]).T
df.to_csv('./new_transposed_df.csv', index=False)

Will make a csv with the left column as headers and the right column as data without changing colons in the second column. It will write out a temp file called temp.txt which you can remove after you run the program.

comments powered by Disqus