Convert Semicolon Separated Strings Text File to Columns and perform sorting in Python?-CodePudding

I have Semicolon Separated Text File consist of below data:

QWxxx1;Jan;2021;Rate;Customer;Operator;Online;100
QWxxx2;Jan;2021;Rate;Customer;Operator;Online;980
QWxxx4;Jan;2021;Rate;Customer;Operator;Offline;200
QWxxx5;Jan;2021;Rate;Customer;Operator;Online;1000
QWxxx6;Jan;2021;Rate;Customer;Operator;Offline;500

Now want to convert this Data to eight Columns in new text file(".txt"):

0         1      2       3          4    5         6      7
QWxxx1    Jan    2021    Customer        Online    100
QWxxx2    Jan    2021    Customer        Online    980
QWxxx4    Jan    2021    Customer        Offline          200  
QWxxx5    Jan    2021    Customer        Online    1000
QWxxx6    Jan    2021    Customer        Offline          500

->Want to Delete Rate and Operator column.

->Want to keep 4th column blank.

->If my 5th column value is "offline" then want to shift number to next column.

Python Code:

    import pandas
    datafile = "path\.txt"
    newfile = "New_" datafile
    f = open(newfile,"w")
    for str in open(datafile,"r"):
        str = str.split(';')
        df = pandas.DataFrame([str], columns =[' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']) 
        f.write(str)
    f.close()

CodePudding user response：

You can read your file with pandas.read_csv, then apply your transformations one by one, and save with pandas.to_csv:

df = pd.read_csv('filename.csv', delimiter=';', header=None)  # read input
del df[3]                                     # remove column
df[5] = ''                                    # empty column
df.columns = range(df.shape[1])               # rename columns
df[7] = df[6].where(df[5].eq('Offline'), '')  # create new column with subset
df[6] = df[6].where(df[5].eq('Online'), '')   # replace existing column with subset
df.to_csv('newfile.csv', sep='\t', index=False)               # save to file

CodePudding user response：

Without the aid of pandas:-

with open('f.txt') as txtin:
    with open('f1.txt', 'w') as txtout:
        print('0       1    2     3        4    5        6     7     ', file=txtout)
        for line in [l.strip() for l in txtin.readlines()]:
            t = line.split(';')
            if t[6] == 'Online':
                c6 = t[7]
                c7 = ''
            else:
                c6 = ''
                c7 = t[7]
            txtout.write(f'{t[0]: <7} {t[1]: <4} {t[2]: <5} {t[4]: <9}     {t[6]: <8} {c6: <5} {c7: <5}\n')