how to divide data into different cells wherever the pipe is used in python-CodePudding

I have one txt file and the data inside the file is divided into four parts. Fund Name|Category|Inception Date|July 2021(Rupees in '000). They all are separated by pipes. I am trying to convert this data into csv file like each section should get its own cell and their should be four cells in the csv file. I have wrritten this code but the output is so messed up. data inside txt file looks like this.

Fund Name|Category|Inception Date|July 2021 ( Rupees in '000 )
ABL Asset Management Company Limited|||
ABL Cash Fund|Money Market|July 31, 2010|27,485,719
ABL Financial Planning Fund (Active Plan)|Fund of Funds|December 31, 2015|151,719

and my code is this

 import CSV

f = open('new11.csv', 'w', encoding="utf-8")
writer = csv.writer(f)
header = ['Fund Name','Category','Inception Date','July 2021(Rupees in 000)']
writer.writerow(header)


with open('combined_vps_data.txt') as f:
    lines = f.readlines()
    x = ' '.join(map(str, lines))

    x = x.replace("|", ",")
    print(x)
    writer.writerow([x])

CodePudding user response：

The csv module can read your file as-is by specifying the delimiter, but to convert it you can write it back out with the default delimiter of comma:

import csv

with open('data.csv',newline='') as f, \
     open('convert.csv','w',newline='') as fout:

    reader = csv.reader(f,delimiter='|')
    writer = csv.writer(fout)

    for line in reader:
        print(line)
        writer.writerow(line)

Output:

['Fund Name', 'Category', 'Inception Date', "July 2021 ( Rupees in '000 )"]
['ABL Asset Management Company Limited', '', '', '']
['ABL Cash Fund', 'Money Market', 'July 31, 2010', '27,485,719']
['ABL Financial Planning Fund (Active Plan)', 'Fund of Funds', 'December 31, 2015', '151,719']

convert.csv:

Fund Name,Category,Inception Date,July 2021 ( Rupees in '000 )
ABL Asset Management Company Limited,,,
ABL Cash Fund,Money Market,"July 31, 2010","27,485,719"
ABL Financial Planning Fund (Active Plan),Fund of Funds,"December 31, 2015","151,719"

CodePudding user response：

In case somebody wanted to do this with pandas instead of csv. I assume the input is called input_piped.csv

import pandas as pd

df = pd.read_csv("input_piped.csv", sep="|")
df.to_csv("output.csv", index=False)

The output.csv

Fund Name,Category,Inception Date,July 2021 ( Rupees in '000 )
ABL Asset Management Company Limited,,,
ABL Cash Fund,Money Market,"July 31, 2010","27,485,719"
ABL Financial Planning Fund (Active Plan),Fund of Funds,"December 31, 2015","151,719"