Reorder the columns in CSV file using Python

Many time we work on files to load or process the flat files. In this post, I am showing if we receive the file but the columns are not in order then how to rearrange them in the right format. There are multiple ways to handle this but here I am using a simple python code which will reorder my columns.

Say for example my right order of my file should be as below:

 empno, ename, sal 

But say I receive a file with

 sal, ename, empno 

image

Now here is the simple python script which will change the file into right order :

import csv

with open('file2.csv', 'r') as infile, open('file2_modified.csv', 'a') as outfile:
fieldnames = ['empno','ename','sal']
writer = csv.DictWriter(outfile, fieldnames=fieldnames)
writer.writeheader()
for row in csv.DictReader(infile):
writer.writerow(row)

Save the above code as “target_file_format.py” or anyname you want.

Execute the python script , and check the result.

image

You can modify the code in various ways as per your need.

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

8 thoughts on “Reorder the columns in CSV file using Python

    • Yes you can.

      Check this :

      oracle@dev-VirtualBox ~/Desktop $ cat <sample.csv
      > sal, ename, empno
      > 5000,Nitya, 007
      > EOF
      oracle@dev-VirtualBox ~/Desktop $ ls
      myfiles sample.csv
      oracle@dev-VirtualBox ~/Desktop $ cat sample.csv
      sal, ename, empno
      5000,Nitya, 007
      oracle@dev-VirtualBox ~/Desktop $
      oracle@dev-VirtualBox ~/Desktop $ ls
      myfiles sample.csv
      oracle@dev-VirtualBox ~/Desktop $ awk 'BEGIN {FS=OFS=","} {print $3,$2,$1}' sample.csv>target.csv
      oracle@dev-VirtualBox ~/Desktop $ ls
      myfiles sample.csv target.csv
      oracle@dev-VirtualBox ~/Desktop $ cat target.csv
      empno, ename,sal
      007,Nitya,5000
      oracle@dev-VirtualBox ~/Desktop $ cat sample.csv
      sal, ename, empno
      5000,Nitya, 007
      oracle@dev-VirtualBox ~/Desktop $

      Thanks,
      Swadhin

  1. Hi Swadhin, can you help to re order the columns of a file when the order of columns is different in each file say for ex: file1 : ename, job, sal, empno , file2 : sal,job,ename,empno file3 empno,sal,job,ename etc

  2. Hi Swadhin, A directory has multiple csv files – each file having columns in a different order and in each file the fieldnames might not be same way spelled say for ex ename,empname,emp_name,name is there a way where a script can check ??

  3. See in any system you should follow certain standards, and the example you have said is not matching and it is not that simple as because I can also put one file with col1, col2, col3 now in this scenario no one will know what is the column need to be matched unless untill we ask the program to match the column name.
    Now say if there are multiple files coming from multiple server then there should be a file name prefix or suffix just to identify it and then you write your program to how to reorder based on the column name in python or use the sequence in bash.

    Hope this helps.

  4. Hi Swadhin can you help to have a automated script to re order multiple files in a standarad format – for ex there are 20 files – we need all the files to be rearranged with a standard format – ignoring if any extra columns are present

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s