CODEWITHSUNDEEP
pythoncsv
Mihir Patel
Mihir Patel

Reputation: 2372

Use Python to iterate through a list of date range and append to a set of data in csv

Need help with appending a date from a list (range of dates) to a set of data in csv.

I am able to create a range of dates. Then use csv reader to go through csv. But I am stuck on how to append a date from the list and keep repeating till the last date (see below for desired output).

CSV Data

AsofDate,Account Number,Security ID,Market Value,Market Value Currency,Security Type Code,Weight
08/01/16,Account 1,123,2000,USD,E,5
08/01/16,Account 1,111,2000,USD,FC,5
08/01/16,Account 2,123,2000,USD,E,5
08/01/16,Account 2,111,2000,USD,FC,5
08/01/16,Account 3,123,2000,USD,E,5
08/01/16,Account 3,111,2000,USD,FC,5

Python Code:

import sys

import datetime

start = datetime.datetime.strptime("08/01/2016", "%m/%d/%Y")
end = datetime.datetime.strptime("08/31/2016", "%m/%d/%Y")
date_generated = [start + datetime.timedelta(days=x) for x in range(0, (end-start).days)]

date_list = []

for date in date_generated:
    date_list.append(date.strftime("%m/%d/%Y"));
#     print date.strftime("%m/%d/%Y")
print date_list

with open('test.csv', 'rb') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print row

Desired Output 

AsofDate,Account Number,Security ID,Market Value,Market Value Currency,Security Type Code,Weight
08/01/16,Account 1,123,2000,USD,E,5
08/01/16,Account 1,111,2000,USD,FC,5
08/01/16,Account 2,123,2000,USD,E,5
08/01/16,Account 2,111,2000,USD,FC,5
08/01/16,Account 3,123,2000,USD,E,5
08/01/16,Account 3,111,2000,USD,FC,5
08/02/16,Account 1,123,2000,USD,E,5
08/02/16,Account 1,111,2000,USD,FC,5
08/02/16,Account 2,123,2000,USD,E,5
08/02/16,Account 2,111,2000,USD,FC,5
08/02/16,Account 3,123,2000,USD,E,5
08/02/16,Account 3,111,2000,USD,FC,5
...
08/31/16,Account 1,123,2000,USD,E,5
08/31/16,Account 1,111,2000,USD,FC,5
08/31/16,Account 2,123,2000,USD,E,5
08/31/16,Account 2,111,2000,USD,FC,5
08/31/16,Account 3,123,2000,USD,E,5
08/31/16,Account 3,111,2000,USD,FC,5

Upvotes: 0

Views: 591

Answers (1)

wwii
wwii

Reputation: 23763

Sorry, I didn't fully understand your question so I deleted my previous answer because it was wrong. Hopefully this is what you want.

Get the rows as dictionaries

with open('test.csv') as f:
    reader = csv.DictReader(f)
    fieldnames = reader.fieldnames
    rows = [row for row in reader]

Iterate over the dates and the rows, substituting the date in each row, then write it to a file.

with open('new.csv', 'w') as out:
    writer = csv.DictWriter(out, fieldnames)
    writer.writeheader()
    for date in date_list:
        for row in rows:
            row['AsofDate'] = date
            writer.writerow(row)

Upvotes: 1

Related Questions