How to formate dates in csv using python? eg Jan 2001 to 01/2001-CodePudding

My csv file looks something like this

Date     Value  otm  oty
Jan 2015  300    na   na
Feb 2015  302    2    na
Mar 2015  303    1    na
Apr 2015  305    2    na
May 2015  307    2    na
Jun 2015  307    0    na
Jul 2015  305    -2   na
Aug 2015  306    1    na

How can I change all the dates to mm/yyyy. eg Jan 2015 would be 01/2015? Thanks

CodePudding user response：

def reformat(x):
  # returns string
  from datetime import datetime
  return datetime.strptime(x, '%b %Y').strftime('%m/%Y')

import pandas as pd
df.Date = df.Date.apply(lambda x:reformat(x))

CodePudding user response：

You could do easily with Pandas and dateutils like

from pandas import read_csv
from dateutil.parser import parse

frame = read_csv('data.csv', sep='\t')
frame['Date'] = frame['Date'].map(lambda x: parse(x).strftime('%m/%Y'))
print(frame)

This will generate a data frame like:

     Date  Value otm oty
  0  01/2015    300  na  na
  1  02/2015    302   2  na
  2  03/2015    303   1  na
  3  04/2015    305   2  na
  4  05/2015    307   2  na
  5  06/2015    307   0  na
  6  07/2015    305  -2  na
  7  08/2015    306   1  na

You could also do this with on-board tools of Python like csv.DictReader and strptime from datetime.datetime:

from csv import DictReader
from datetime import date
from pprint import pprint


def convert_date(entry):
    entry['Date'] = datetime.strptime(
        entry['Date'], '%b %Y'
    ).strftime('%m/%Y')
    return entry

with open('data.csv') as src:
    reader = DictReader(src, delimiter='\t')
    data = list(map(convert_date, reader))

pprint(data)

This will yield

[{'Date': '01/2015', 'Value': '300', 'otm': 'na', 'oty': 'na'},
 {'Date': '02/2015', 'Value': '302', 'otm': '2', 'oty': 'na'},
 {'Date': '03/2015', 'Value': '303', 'otm': '1', 'oty': 'na'},
 {'Date': '04/2015', 'Value': '305', 'otm': '2', 'oty': 'na'},
 {'Date': '05/2015', 'Value': '307', 'otm': '2', 'oty': 'na'},
 {'Date': '06/2015', 'Value': '307', 'otm': '0', 'oty': 'na'},
 {'Date': '07/2015', 'Value': '305', 'otm': '-2', 'oty': 'na'},
 {'Date': '08/2015', 'Value': '306', 'otm': '1', 'oty': 'na'}]