current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 3 down vote favorite

I need to transpose the following file output1.csv, which is is a result from a quantum chemistry calculation into a single colum efficiently:

 Frequencies --    18.8210                44.7624                46.9673
 Frequencies --    66.6706               102.0432               112.4930
 Frequencies --   124.4601               138.4393               180.1404
 Frequencies --   230.0306               240.4389               258.2459
 Frequencies --   282.7781               340.8302               357.7789
 Frequencies --   378.9043               384.1284               401.4285
 Frequencies --   418.0523               444.2264               447.6885
 Frequencies --   473.2391               501.0937               518.9083
 Frequencies --   559.5925               609.9256               623.7729
 Frequencies --   657.4144               672.5480               728.2009
 Frequencies --   740.5035               750.3238               757.2199
 Frequencies --   774.6343               806.7750               815.9990
 Frequencies --   839.3050               858.0716               876.1641
 Frequencies --   888.6654               942.2963               965.7888
 Frequencies --   987.3819               994.7388              1020.8724
 Frequencies --  1025.0426              1045.5129              1059.0966
 Frequencies --  1076.5127              1143.1178              1155.4200
 Frequencies --  1208.6790              1219.7513              1244.7080
 Frequencies --  1265.6108              1287.8830              1300.0463
 Frequencies --  1325.0427              1339.0678              1353.0061
 Frequencies --  1369.0614              1408.5258              1433.0543
 Frequencies --  1452.4148              1454.6319              1500.4304
 Frequencies --  1511.2305              1517.2562              1552.9189
 Frequencies --  1560.5313              1636.2290              1640.1732
 Frequencies --  1664.8747              1681.5566              1703.2026
 Frequencies --  1770.2627              3058.4143              3122.3743
 Frequencies --  3147.1828              3192.5897              3199.1398
 Frequencies --  3211.0676              3222.0033              3236.3394
 Frequencies --  3262.2119              3556.7997              3862.4791

To achieve that I wrote this code:

import os
import csv
import re
import sys
import pandas as pd

inputfile = open('output1.csv', 'r')
reader = csv.reader(inputfile)

outputfile = open('output1_f.csv', 'a')
writer = csv.writer(outputfile)

with open('output1_f.csv', 'w') as file:
    file.write('Frequencies,Frequencies,Frequencies\n')
for row in reader:   
    row = [re.sub(' +', ',', item) for item in row]
    row = [re.sub(',Frequencies,--,', '', item) for item in row]               
#    row = map(str.strip, row)
    writer.writerow(row)

inputfile.close()
outputfile.close()

I added the code as comment in order to delete the first and last character " from every line in the output1_f.csv file. However it does not work out.

 row = map(str.strip, row)

I found the line.replace solution which creates a second output1_2f.csv file.

inputfile = open('output1_f.csv', 'r')
outputfile = open('output1_2f.csv', 'w')
for line in inputfile:
    line = line.replace('"', '')
    outputfile.write(line)

inputfile.close()
outputfile.close()

The following transpose part works only if the character " is removed and this is the reason that I need to delete the " character efficiently code instead of line.replace.

ifile  = open('output1_2f.csv', "rb")
reader = csv.reader(ifile)

with open('output1_transp.csv', 'w') as out:
    rownum = 0
    for row in reader:
    # Save header row.
        if rownum == 0:
            header = row
        else:
            colnum = 0
            for col in row:
                out.write( '%s\n' % (col))
                colnum += 1

        rownum += 1

ifile.close()

I would be grateful if you could propose me any idea to shorten the code and make it more efficient and easier to use. Thank you developers for your time!!!

share|improve this question
2  
It's not clear what the result is intended to be. Is it a file with a single column of frequencies? – Edward Jul 6 '16 at 12:59
up vote 1 down vote accepted

I believe there is a much simpler way of doing this.

If I correctly understand the problem, the idea is to produce a single column of frequencies. 

with open('input.txt', 'r') as infile, open('out.csv', 'w') as outfile:
    print >> outfile, "Frequency"
    for line in infile:
        for freq in line.split()[2:]:
            print >> outfile, freq

This iterates over each line in the input file. It uses split to eliminate whitespace and to split each line into fields. It then discards the first two, which are assumed to be "Frequencies" and "--" and writes the remaining fields to the output file.

share|improve this answer
    
Thank you very much! It is very useful and very clever. – Manolis Semidalas Jul 6 '16 at 16:42
    
Downvoter: could you explain? How could this answer be improved? – Edward Jul 7 '16 at 1:08
    
@Edward I'm not the downvoter, but print >> outfile, "Frequency" is only erroring for me. And may explain the downvote. – Peilonrayz Jul 7 '16 at 8:40
    
@JoeWallis Could it be version difference? The question itself isn't tagged with a specific version. – Mast Jul 7 '16 at 11:10
    
@Mast Yes, print >>stream, ... is explicitly Python 2, in Python 3 you use print(..., file=stream) instead. – Mathias Ettinger Jul 7 '16 at 11:12
up vote 0 down vote

If I am understanding what you want, and you are already importing pandas, this can be done very easily:

import csv
import pandas as pd

df = pd.read_csv('output1.csv', header=None, delim_whitespace=True)
df = df.iloc[:, 2:].stack().reset_index(drop=True).to_frame()
df.insert(0, -1, '--')
df.insert(0, -2, 'Frequencies')
df.to_csv('output1_f.csv', sep=' ', mode='a', index=False, quoting=csv.QUOTE_NONE, header=None)

And the result:

Frequencies -- 18.821
Frequencies -- 44.7624
Frequencies -- 46.9673
Frequencies -- 66.6706
Frequencies -- 102.0432
Frequencies -- 112.493
Frequencies -- 124.4601
Frequencies -- 138.4393
Frequencies -- 180.1404
Frequencies -- 230.0306
Frequencies -- 240.4389
Frequencies -- 258.2459
Frequencies -- 282.7781
Frequencies -- 340.8302
Frequencies -- 357.7789
Frequencies -- 378.9043
Frequencies -- 384.1284
Frequencies -- 401.4285
Frequencies -- 418.0523
Frequencies -- 444.2264
Frequencies -- 447.6885
Frequencies -- 473.2391
Frequencies -- 501.0937
Frequencies -- 518.9083
Frequencies -- 559.5925
Frequencies -- 609.9256
Frequencies -- 623.7729
Frequencies -- 657.4144
Frequencies -- 672.548
Frequencies -- 728.2009
Frequencies -- 740.5035
Frequencies -- 750.3238
Frequencies -- 757.2199
Frequencies -- 774.6343
Frequencies -- 806.775
Frequencies -- 815.999
Frequencies -- 839.305
Frequencies -- 858.0716
Frequencies -- 876.1641
Frequencies -- 888.6654
Frequencies -- 942.2963
Frequencies -- 965.7888
Frequencies -- 987.3819
Frequencies -- 994.7388
Frequencies -- 1020.8724
Frequencies -- 1025.0426
Frequencies -- 1045.5129
Frequencies -- 1059.0966
Frequencies -- 1076.5127
Frequencies -- 1143.1178
Frequencies -- 1155.42
Frequencies -- 1208.679
Frequencies -- 1219.7513
Frequencies -- 1244.708
Frequencies -- 1265.6108
Frequencies -- 1287.883
Frequencies -- 1300.0463
Frequencies -- 1325.0427
Frequencies -- 1339.0678
Frequencies -- 1353.0061
Frequencies -- 1369.0614
Frequencies -- 1408.5258
Frequencies -- 1433.0543
Frequencies -- 1452.4148
Frequencies -- 1454.6319
Frequencies -- 1500.4304
Frequencies -- 1511.2305
Frequencies -- 1517.2562
Frequencies -- 1552.9189
Frequencies -- 1560.5313
Frequencies -- 1636.229
Frequencies -- 1640.1732
Frequencies -- 1664.8747
Frequencies -- 1681.5566
Frequencies -- 1703.2026
Frequencies -- 1770.2627
Frequencies -- 3058.4143
Frequencies -- 3122.3743
Frequencies -- 3147.1828
Frequencies -- 3192.5897
Frequencies -- 3199.1398
Frequencies -- 3211.0676
Frequencies -- 3222.0033
Frequencies -- 3236.3394
Frequencies -- 3262.2119
Frequencies -- 3556.7997
Frequencies -- 3862.4791
share|improve this answer
    
Thank you very much! It is really useful. – Manolis Semidalas Jul 6 '16 at 16:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.