How do I format and merge columns from a single CSV file

Question

I am new to Python, I've looked through the CSV doc and had a look at a few Stack Overflow examples but can't seem to get it right. I have a CSV file that has data that looks like this:

IDCJAC0010,66062,2019,01,01,31.6,1,Y

Index [2:5] are the elements of the date so I'd like to merge those columns and have a '-' between year-month-day so that I can then use the newly created CSV to create a plot with matplotlib. To be clear, the desired output in the new CSV file is:

IDCJAC0010,66062,2019-01-01,31.6,1,Y

My code so far is:

with open(file_in, newline='') as f_in, open(file_out, 'w') as f_out:

    reader = csv.reader(f_in, delimiter=',')

    new_row = []

    for row in reader:
        new_row.append(row[0: 2])
        amended_row = '-'.join(row[2:5])
        new_row.append(amended_row)
        new_row.append(row[5:])

The output that I'm getting is:

['IDCJAC0010', '66062'], '2019-01-05', ['37.8', '1', 'Y']

You seem to know what you need to do. Why did you not do it? — mkrieger1, Apr 25 '20 at 10:32
Instead of starting with an empty new row, start with the first two items of the original row. And after appending the new formatted date, append the rest of the original row. — mkrieger1, Apr 25 '20 at 10:35
I'm getting somewhere but the output I'm getting are lists within lists. — AdrianJoooo, Apr 25 '20 at 10:53
See https://stackoverflow.com/questions/252703/what-is-the-difference-between-pythons-list-methods-append-and-extend. Sorry for confusing you with the word "append". — mkrieger1, Apr 25 '20 at 11:26

score 1 · Accepted Answer · answered Apr 25 '20 at 11:34

You are mixing strings and lists here.

row[0: 2] returns the list of strings ['IDCJAC0010', '66062'] for the first row. But '-'.join(row[2:5]) results in the plain string '2019-01-05'.

There are different ways around this problem. One way would be to use new_row.extend(row[0:2]) when you want to add all elements from your partial lists.

What I usually do is, I stick to the += operator to extend my lists. If you want your code too look consistently, wrap the string '-'.join(row[2:5]) in a list like this: ['-'.join(row[2:5])].

with open(file_in, newline='') as f_in, open(file_out, 'w') as f_out:
    reader = csv.reader(f_in, delimiter=',')
    writer = csv.writer(f_out, delimiter=',')

    for row in reader:
        new_row = []
        new_row += row[0:2]
        new_row += ['-'.join(row[2:5])]
        new_row += row[5:]
        writer.writerow(new_row)

score 0 · Answer 2 · answered Apr 25 '20 at 11:26

0

You need to use new_row.extend(row[0: 2]) and new_row.append(row[5:]) to avoid having lists within lists.

answered Apr 25 '20 at 11:26

Miles Winther

134
1
7

How do I format and merge columns from a single CSV file

2 Answers2