Insert data in specific columns in csv file

Question

I am trying to insert the data obtained from the date column. Columns headers are date,day,month,year,pcp1,pcp2,pcp3,pcp4,pcp5,pcp6 in the csv file. The columns day, month, year are currently empty.

I would like to insert the data obtained from the date by split method into these columns. How can l do that?

Here is an example data in csv file:

date    day month   year    pcp1    pcp2    pcp3    pcp4    pcp5    pcp6
1.01.1979               0.431   2.167   9.375   0.431   2.167   9.375
2.01.1979               1.216   2.583   9.162   1.216   2.583   9.162
3.01.1979               4.041   9.373   23.169  4.041   9.373   23.169
4.01.1979               1.799   3.866   8.286   1.799   3.866   8.286
5.01.1979               0.003   0.051   0.342   0.003   0.051   0.342
6.01.1979               2.345   3.777   7.483   2.345   3.777   7.483
7.01.1979               0.017   0.031   0.173   0.017   0.031   0.173

Here is my code:

import csv

dd=[]
mm=[]
yy=[]
with open('output2.csv') as csvfile:
            reader = csv.DictReader(csvfile, fieldnames=("date","day","month","year","pcp1","pcp2","pcp3","pcp4","pcp5","pcp6"), delimiter=';', quotechar='|')
            next(reader)  # skip header row
            x = [row['date'] for row in reader]

for date_str in x:
    day, month, year = date_str.split('.')
    dd.append(day)
    mm.append(month)
    yy.append(year)

csvfile.close()

with open('output2.csv') as f:
    fieldnames = ["date","day","month","year","pcp1","pcp2","pcp3","pcp4","pcp5","pcp6"]
    writer = csv.DictWriter(f, fieldnames=fieldnames,delimiter=';', quotechar='|')
    for i in range(len(dd)):
        writer.writerow({'day':dd[i]})
    for i in range(len(mm)):
        writer.writerow({'month':mm[i]})
    for i in range(len(yy)):
        writer.writerow({'year':yy[i]})


f.close()

Martin Evans · Accepted Answer · 2016-09-05 13:14:00Z

2

You could just parse the CSV as follows. This reads all of your rows into a list, and then inserts the date componants into the empty columns.

import csv

with open('output2.csv', newline='') as f_input:
    csv_input = csv.reader(f_input, delimiter=';', quotechar='|')
    header = next(csv_input)
    rows = list(csv_input)

with open('output2b.csv', 'w', newline='') as f_output:
    csv_output = csv.writer(f_output, delimiter=';', quotechar='|')
    csv_output.writerow(header)

    for row in rows:
        day, month, year = row[0].split('.')
        row[1:4] = [day, month, year]
        csv_output.writerow(row)

Giving you the following output:

date;day;month;year;pcp1;pcp2;pcp3;pcp4;pcp5;pcp6
1.01.1979;1;01;1979;0.431;2.167;9.375;0.431;2.167;9.375
2.01.1979;2;01;1979;1.216;2.583;9.162;1.216;2.583;9.162
3.01.1979;3;01;1979;4.041;9.373;23.169;4.041;9.373;23.169
4.01.1979;4;01;1979;1.799;3.866;8.286;1.799;3.866;8.286
5.01.1979;5;01;1979;0.003;0.051;0.342;0.003;0.051;0.342
6.01.1979;6;01;1979;2.345;3.777;7.483;2.345;3.777;7.483
7.01.1979;7;01;1979;0.017;0.031;0.173;0.017;0.031;0.173

Tested using Python 3.5.2

edited Sep 5, 2016 at 13:14

answered Sep 5, 2016 at 11:55

Martin Evans

46.9k17 gold badges88 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ömer sarı Over a year ago

when l try it , it gives me that error " day, month, year = row[0].split('.') IndexError: list index out of range" . l m using Python 3.5.

Martin Evans Over a year ago

For Python 3.5 the file needs to be opened differently, try it now.

ömer sarı Over a year ago

l tried it works with python 2.7 via pervious answer of you. then ,l tried with python 3.5 via new answer and some files ,it gives error : day, month, year = row[0].split('.') .ValueError: too many values to unpack (expected 3) as l think "date" column ,it must use but according to code,there are many columns that the code try to split.

Martin Evans Over a year ago

I'm guessing your CSV file is not in the expected format, normally it has commas as delimiters. If you added a line such as print(rows) you should see something like [['1.01.1979', '', '', '', '0.431', '2.167', '9.375', '0.431', '2.167', '9.375'], ....

Martin Evans Over a year ago

Assuming you do have ; as the delimiter, just add that to the csv.reader() line as you had before.

|

Glostas · Accepted Answer · 2016-09-05 11:44:34Z

1

Use pandas. You will be able to use most of your code which is not too far from working

import pandas as pd    
filename = "test.csv"
data = pd.read_excel(filename)

x = data["date"]
dd=[]
mm=[]
yy=[]

for date_str in x:
    day, month, year = date_str.split('.')
    dd.append(day)
    mm.append(month)
    yy.append(year)

data["day"] = dd
data["month"] = mm
data["year"] = yy

data.to_csv("test2.csv")

In test2.csv the day month year is filled. If you do't want to have the index as first row ( I would not) use

data.to_csv("test2.csv",index = False)

as last lane

edited Sep 5, 2016 at 11:44

answered Sep 5, 2016 at 11:26

Glostas

1,2202 gold badges12 silver badges22 bronze badges

4 Comments

ömer sarı Over a year ago

when l try to install a module in python 3.5 ,l get this error :"Fatal error in launcher: Unable to create process using ".l have two version of python 3.5 and 2.7 and it cause this problem l think but l m not able to solve it yet.l tried many things on here as solution but no work. That is why ,without pandas ,l need to solve it.

Glostas Over a year ago

I tested it with python 2.7. It works well. I have no experience with python 3.5 so I cannot help you there

Glostas Over a year ago

Have you solved your problem about installing modules? I was suprised seeing you accepted my answer

ömer sarı Over a year ago

l tried on my friends pc and it works fine .that is why ,l accepted your answer.

Collectives™ on Stack Overflow

Insert data in specific columns in csv file

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related