CODEWITHSUNDEEP
pythonpandascsv
Taras
Taras

Reputation: 77

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

Upvotes: 0

Views: 5134

Answers (2)

Prayson W. Daniel
Prayson W. Daniel

Reputation: 15558

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
'''

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

print(df.set_index(0).T)

Results:enter image description here

Upvotes: 1

d_kennetz
d_kennetz

Reputation: 5359

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.

Upvotes: 1

Related Questions