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
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.
pandas.errors.ParserError: Error tokenizing data. C error: Expected 2 fields in line 10, saw 3
: in column 1 or column 2 (for example if you had one that said Time: 9:00 am, 3 which would create 3 columns instead of 2 and just fill the 2nd column with 9 and the 3rd column with 00 am for those :P. Cleaning up your data is half the battle!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:
