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

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

<p>Here is an example data in csv file:</p>

<pre><code>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
</code></pre>

<p>Here is my code:</p>

<pre><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()
</code></pre>

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

<pre><code>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")
</code></pre>

<p>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</p>

<pre><code>data.to_csv("test2.csv",index = False)
</code></pre>

<p>as last lane</p>

python csv