Home > python > pandas: add new columns; reorder columns

pandas: add new columns; reorder columns

Problem
I had a CSV file and (1) I wanted to add some new columns, and (2) I wanted to reorder the columns.

Solution
Instead of doing it manually, I used the pandas library for this job.

Input file:

number,season,episode,airdate,title,TVmaze link
1,1,1,24 Jun 15,"eps1.0_hellofriend.mov","http://www.tvmaze.com/episodes/157154/mr-robot-1x01-eps10hellofriendmov"
2,1,2,01 Jul 15,"eps1.1_ones-and-zer0es.mpeg","http://www.tvmaze.com/episodes/167379/mr-robot-1x02-eps11ones-and-zer0esmpeg"
3,1,3,08 Jul 15,"eps1.2_d3bug.mkv","http://www.tvmaze.com/episodes/167380/mr-robot-1x03-eps12d3bugmkv"
4,1,4,15 Jul 15,"eps1.3_da3m0ns.mp4","http://www.tvmaze.com/episodes/167381/mr-robot-1x04-eps13da3m0nsmp4"
5,1,5,22 Jul 15,"eps1.4_3xpl0its.wmv","http://www.tvmaze.com/episodes/167382/mr-robot-1x05-eps143xpl0itswmv"
6,1,6,29 Jul 15,"eps1.5_br4ve-trave1er.asf","http://www.tvmaze.com/episodes/167383/mr-robot-1x06-eps15br4ve-trave1erasf"
7,1,7,05 Aug 15,"eps1.6_v1ew-s0urce.flv","http://www.tvmaze.com/episodes/167384/mr-robot-1x07-eps16v1ew-s0urceflv"
8,1,8,12 Aug 15,"eps1.7_wh1ter0se.m4v","http://www.tvmaze.com/episodes/167385/mr-robot-1x08-eps17wh1ter0sem4v"
9,1,9,19 Aug 15,"eps1.8_m1rr0r1ng.qt","http://www.tvmaze.com/episodes/167386/mr-robot-1x09-eps18m1rr0r1ngqt"
10,1,10,02 Sep 15,"eps1.9_zer0-day.avi","http://www.tvmaze.com/episodes/167387/mr-robot-1x10-eps19zer0-dayavi"

Desired output:

number,season,episode,prod_code,airdate,title,special,TVmaze link
1,1,1,,24 Jun 15,eps1.0_hellofriend.mov,,http://www.tvmaze.com/episodes/157154/mr-robot-1x01-eps10hellofriendmov
2,1,2,,01 Jul 15,eps1.1_ones-and-zer0es.mpeg,,http://www.tvmaze.com/episodes/167379/mr-robot-1x02-eps11ones-and-zer0esmpeg
3,1,3,,08 Jul 15,eps1.2_d3bug.mkv,,http://www.tvmaze.com/episodes/167380/mr-robot-1x03-eps12d3bugmkv
4,1,4,,15 Jul 15,eps1.3_da3m0ns.mp4,,http://www.tvmaze.com/episodes/167381/mr-robot-1x04-eps13da3m0nsmp4
5,1,5,,22 Jul 15,eps1.4_3xpl0its.wmv,,http://www.tvmaze.com/episodes/167382/mr-robot-1x05-eps143xpl0itswmv
6,1,6,,29 Jul 15,eps1.5_br4ve-trave1er.asf,,http://www.tvmaze.com/episodes/167383/mr-robot-1x06-eps15br4ve-trave1erasf
7,1,7,,05 Aug 15,eps1.6_v1ew-s0urce.flv,,http://www.tvmaze.com/episodes/167384/mr-robot-1x07-eps16v1ew-s0urceflv
8,1,8,,12 Aug 15,eps1.7_wh1ter0se.m4v,,http://www.tvmaze.com/episodes/167385/mr-robot-1x08-eps17wh1ter0sem4v
9,1,9,,19 Aug 15,eps1.8_m1rr0r1ng.qt,,http://www.tvmaze.com/episodes/167386/mr-robot-1x09-eps18m1rr0r1ngqt
10,1,10,,02 Sep 15,eps1.9_zer0-day.avi,,http://www.tvmaze.com/episodes/167387/mr-robot-1x10-eps19zer0-dayavi

Python code:

import pandas as pd

def main():
    df = pd.read_csv('bad.csv')

    # add these two extra columns to the end
    df["prod_code"] = ""
    df["special"] = ""
    cols = df.columns.tolist()
    # reorder columns
    cols = cols[:3] + [cols[-2]] + cols[3:5] + [cols[-1]] + [cols[-3]]
    # "commit" the reordering
    df = df[cols]
    # write the output without Pandas' first index column
    df.to_csv('out.csv', index=False)
Advertisements
Categories: python Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: