Reformat CSV file in python for spreadsheet

Question

I have a text file text.csv with dates arranged as such.

name1
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
name2
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
name3
2011-01-05 (i)
2011-05-05 (i)
2011-06-14 (i)

I'd like to reformat or write the file into 2 columns like:

+---------------+-----+
| 2010-01-02 (i)|name1|
| 2010-05-07 (i)|name1|
| 2010-06-12 (i)|name1|  
| 2010-01-02 (i)|name2|
| 2010-05-07 (i)|name2|
| 2010-06-12 (i)|name2|
| 2011-01-05 (i)|name3|
| 2011-05-05 (i)|name3|
| 2011-06-14 (i)|name3|
+---------------+-----+

The logic would be something like:

if line doesn't contain "(i)", name=value
else
write date=value, name to file

I'd rather not use PHP, but I could loop through the data:

<?php
$file = file($path);
foreach($file as $value)
{
  if ( strpos($value, "(i)" ) !== false)
    $name = $value;

    $fp = fopen('data.csv', 'w');
    fputcsv($fp, $line);
    fclose($fp);
}

Can you provide a python example that could get me started? It needs to run as a macro in Libre office calc.

Although there's no standard, neither the input nor the output file are CSV format. — martineau
– martineau, Commented Jul 5, 2014 at 16:48

martineau · Accepted Answer · 2014-07-05 17:10:09Z

2

As I said in a comment, your input file isn't a CSV file. You could use the following to do the formatting you want and produce a valid CSV file. Many spreadsheet programs can read CSV files that use either a comma or tab ('\t') character as a delimiter.

import csv
DELIMITER = ','

with open('data.csv', 'wb') as csvfile:
    writer = csv.writer(csvfile, delimiter=DELIMITER)
    row = [None, None]
    with open('input_data.txt', 'rt') as textfile:
        for line in (line.strip() for line in textfile):
            if line.endswith('(i)'):
                row[0] = line
                writer.writerow(row)
            else:
                row[1] = line

answered Jul 5, 2014 at 17:10

martineau

124k29 gold badges181 silver badges319 bronze badges

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

Comments

furas · Accepted Answer · 2014-07-05 15:45:27Z

1

data = '''name1
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
name2
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
name3
2011-01-05 (i)
2011-05-05 (i)
2011-06-14 (i)'''

name = None
for line in data.splitlines():
    if '(i)' in line:
        print line, name
    else:
        name = line

result:

2010-01-02 (i) name1
2010-05-07 (i) name1
2010-06-12 (i) name1
2010-01-02 (i) name2
2010-05-07 (i) name2
2010-06-12 (i) name2
2011-01-05 (i) name3
2011-05-05 (i) name3
2011-06-14 (i) name3

Now you have to read file and write lines in place of print.

answered Jul 5, 2014 at 15:45

furas

149k12 gold badges121 silver badges171 bronze badges

Comments

Burhan Khalid · Accepted Answer · 2014-07-05 17:23:43Z

1

Another, rather simple approach:

lines = []

with open('original.txt') as f:
    for line in f:
       if line.startswith('name'):
           key = line.rstrip()
       else:
           lines.append('{} {}'.format(line.rstrip(), key))

with open('output.txt', 'w') as f:
    f.writelines(lines)

answered Jul 5, 2014 at 17:23

Burhan Khalid

175k20 gold badges255 silver badges292 bronze badges

Collectives™ on Stack Overflow

Reformat CSV file in python for spreadsheet

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related