Python Forum
Rearrange Columns in a CSV File
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Rearrange Columns in a CSV File
#1
Hello,

I've got a file that I am trying to reformat into a new file, but am having issues with the code I am using. It is adding a blank row in between every line in the file.

Here is what I need to do:
1) Open any File with a "*-100.csv" and Write it to "*-102.csv". Ideally the star would stay consistent (i.e. file 1-100.csv would create 1-102.csv and file "2-100.csv" would create "2-102.csv"

Edit the first line of the file to give it headers (not sure if this is necessary, but since there aren't column headers for every column, I don't know a way around this). In my current code, I've used the following headers:
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK

2) Rearrange the Columns in the following order: ['A','B','C','H', 'I', 'D', 'E', 'F', 'G','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK']

3) Delete the column header


Here is what I have that is giving me issues with the added blank lines in between each row of data. I'm struggling to figure out how to add the column headers to the first line of the file and then removing them after, so that's not currently in the code at all.

import csv

with open("C:\\csv\\041-100.csv", 'r') as infile, open("C:\\csv\\041-102.csv", 'a') as outfile:
    # output dict needs a list for new column ordering
    fieldnames = ['A','B','C','H', 'I', 'D', 'E', 'F', 'G','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK']
    writer = csv.DictWriter(outfile, fieldnames=fieldnames)
    # reorder the header first
    writer.writeheader()
    for row in csv.DictReader(infile):
        # writes the reordered rows to the new file
        writer.writerow(row)
Thanks in advance for your help!
Reply
#2
Hi,
The first question before 1-2-3 is about the extra line.
I suspect that you are reading some CRLF from the old file.
In python they call that newline.
You may try to read the row and then do:
row = row[:-1] that takes away the old escape sequence \n

1) Would be simpler to answer if the directory contains only files
that need to be transformed, or other stuff that looks like that.

Paul
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Create Choices from .ods file columns cspower 3 520 Dec-28-2023, 09:59 PM
Last Post: deanhystad
  Create csv file with 4 columns for process mining thomaskissas33 3 695 Nov-06-2023, 09:36 PM
Last Post: deanhystad
  How can I rearrange df as the nodes index in pytorch geometric manner? uqlsmey 0 495 Jul-31-2023, 11:28 AM
Last Post: uqlsmey
  Converting a json file to a dataframe with rows and columns eyavuz21 13 4,174 Jan-29-2023, 03:59 PM
Last Post: eyavuz21
  deleting columns in CSV file astral_travel 8 2,167 Nov-26-2022, 09:36 PM
Last Post: astral_travel
  Replace columns indexes reading a XSLX file Larry1888 2 951 Nov-18-2022, 10:16 PM
Last Post: Pedroski55
  rows from sql query need to write to a file as columns sjcsvatt 6 2,336 Oct-09-2021, 12:45 AM
Last Post: snippsat
  [Solved] Using readlines to read data file and sum columns Laplace12 4 3,464 Jun-16-2021, 12:46 PM
Last Post: Laplace12
  Python Matplotlib: Create chart for every 4 columns in Excel file JaneTan 2 2,733 Feb-28-2021, 05:02 AM
Last Post: JaneTan
  How to fill parameter with data from multiple columns CSV file greenpine 1 1,619 Dec-21-2020, 06:50 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020