Trying to take info from a CSV file, rearrange the columns and then write the new output to a new CS-CodePudding

I have a set of data in a CSV file that basically requires re-ordering and the re-ordered data writing to a new CSV file. The data looks like this to start

Communit,Equtions,8000,707757,2024.96,0,99
Annlins,EXSES,5063,536835,71.26,0,99
K ad,EXPSES,3028,40360,37.31,0,99
Harr White,EXSES,1644,10634264,85.55,0,99
Emge,Equutions,89250,68895,93.53,0,99
HMC,120PE249,83210,12039,1651.86,0,99

7 columns of data separated by a comma. To make it a bit more readable I shall focus on the first line.

So it starts like - Communit,Equtions,8000,707757,2024.96,0,99 And needs to end up like - Communit,8000,707757,2024.96,Equtions,99

My current code can print it to the screen but I'm struggling to get it to write to a file

import csv

with open('C:\\Impexp\\Input\\02B-210722.csv') as f:
    reader = csv.reader(f, delimiter=',')
    for row in reader:
        print(",".join([row[0], row[2], row[3], row[4], row[1], row[6]]))

I did try changing the sys.stdout to a file but that wouldn't work.

I'm a bit rusty with my coding as I mainly use SQL and spreadsheets are my primary focus and this is my first time dabbling with Python.

Any help appreciated, have tried looking at other posts to try and cobble together a solution that fits my problem, but so far no joy.

CodePudding user response：

You can use csv.writer() to write patched data into another CSV:

import csv

with open(r'C:\Impexp\Input\02B-210722.csv') as i_f, \
        open(r'C:\Impexp\Input\02B-210722_patched.csv', 'w', newline='') as o_f:
    reader = csv.reader(i_f)
    writer = csv.writer(o_f)
    for row in reader:
        row[4] = row.pop(1)
        writer.writerow(row)

If you want to modify existing file without creating new (which I don't recommend you to do without backups) you can open file in r mode, read all rows into list and rewrite same file:

import csv

with open(r'C:\Impexp\Input\02B-210722.csv', 'r ', newline='') as f:
    reader = csv.reader(f)
    rows = []
    for row in reader:
        row[4] = row.pop(1)
        rows.append(row)
    f.seek(0)
    writer = csv.writer(f)
    writer.writerows(rows)
    f.truncate()

You can help my country, check my profile info.

CodePudding user response：

You can use pandas to rearrange the columns:

import pandas as pd

df = pd.read_csv('data.csv')
new_cols = ['A', 'C', 'D', 'E', 'F', 'B', 'G']
df = df[new_cols]
df.to_csv('new_data.csv', index=False)

data.csv

A,B,C,D,E,F,G
Communit,Equtions,8000,707757,2024.96,0,99
Annlins,EXSES,5063,536835,71.26,0,99
K ad,EXPSES,3028,40360,37.31,0,99
Harr White,EXSES,1644,10634264,85.55,0,99
Emge,Equutions,89250,68895,93.53,0,99
HMC,120PE249,83210,12039,1651.86,0,99

new_data.csv

A,C,D,E,F,B,G
Communit,8000,707757,2024.96,0,Equtions,99
Annlins,5063,536835,71.26,0,EXSES,99
K ad,3028,40360,37.31,0,EXPSES,99
Harr White,1644,10634264,85.55,0,EXSES,99
Emge,89250,68895,93.53,0,Equutions,99
HMC,83210,12039,1651.86,0,120PE249,99

Or, using the csv module:

import csv

with open('data.csv') as f, open('new_data.csv', 'w', newline='') as g:
    reader = csv.reader(f, delimiter=',')
    writer = csv.writer(g, delimiter=',')
    for row in reader:
        writer.writerow([row[0], row[2], row[3], row[4], row[1], row[6]])

data.csv

Communit,Equtions,8000,707757,2024.96,0,99
Annlins,EXSES,5063,536835,71.26,0,99
K ad,EXPSES,3028,40360,37.31,0,99
Harr White,EXSES,1644,10634264,85.55,0,99
Emge,Equutions,89250,68895,93.53,0,99
HMC,120PE249,83210,12039,1651.86,0,99

new_data.csv

Communit,8000,707757,2024.96,Equtions,99
Annlins,5063,536835,71.26,EXSES,99
K ad,3028,40360,37.31,EXPSES,99
Harr White,1644,10634264,85.55,EXSES,99
Emge,89250,68895,93.53,Equutions,99
HMC,83210,12039,1651.86,120PE249,99