Parsing in python to excel

Question

So my problem right now is reading a .txt file and pulling out specific data. This data will then be written into an excel file where the rows will become columns now and vice versa. So far I have been able to read the file and create the excel sheet with columns but no data transfers. Below is the data that it reads and what is to be written into excel.

[' O/F=7.1800E+00  PERCENT FUEL=  12.2249  EQUIVALENCE RATIO=5.5559E-01    DENSITY=9.5028E-01']
[]
['                 CHAMBER   THROAT     EXIT     EXIT']
[' PC/P             1.0000   1.7346   1.0083   62.915']
[' P', ' PSIA          1500.0    864.8   1487.7    23.84']
[' T', ' DEG R           5886     5555     5880     3494']
[' H', ' BTU/LB        -446.6   -674.1   -450.1  -1819.9']
[' S', ' BTU/(LB)(R)   2.3395   2.3395   2.3395   2.3395']
[' DEN (LBM/FT3)  6.50E-01 4.01E-01 6.45E-01 1.81E-02']
[' ']
[' M', ' MOL WT        27.357   27.616   27.361   28.499']
[' (DLV/DLP)T     -1.01645 -1.01277 -1.01639 -1.00034']
[' (DLV/DLT)P       1.3300   1.2733   1.3292   1.0126']
[' CP', 'BTU/(LB)(R)   0.9274   0.8626   0.9265   0.4371']
[' CP GAS(SF)       0.4353   0.4326   0.4353   0.4041']
[' GAMMA GAS(SF)    1.2001   1.1993   1.2001   1.2084']
[' GAMMA (S)        1.1390   1.1395   1.1390   1.1949']
[' SON VEL', 'FT/SEC   3490.4   3375.7   3488.6   2698.9']
[' MU', 'LBF-S/FT2   2.01E-06 1.93E-06 2.01E-06 1.41E-06']
[' K', 'LBF/S-DEGR   3.16E-02 3.01E-02 3.16E-02 2.03E-02']
[' PRANDTL NO      0.69205  0.69371  0.69207  0.70189']
[' MACH NUMBER      0.0000   1.0000   0.1202   3.0726']
[' ']
[' AE/AT                     1.0000   4.9993   9.0000']
[' CSTAR', ' FT/SEC               5139     5139     5139']
[' CF  VAC                    1.233             1.757']
[' CF                         0.657             1.614']
[' IVAC', 'LBF-S/LBM            197.00            280.59']
[' I', ' LBF-SEC/LBM            104.92            257.74']
[' MOL WT(MIX)      27.357   27.616   27.361   28.499']

Below is the code I have so far

import csv
import sys
import xlsxwriter
import pandas as pd 
import argparse

def parse_args():

    parser = argparse.ArgumentParser(description='Read POST run outputs.')
    parser.add_argument('infile',
                        type=argparse.FileType('r'),
                        help='POST file to be analyzed.')
    parser.add_argument('outfile',
                        type=argparse.FileType('w'),
                        help='Output xlsx file.')
    return parser.parse_args()


def read_post_file(LOX_CH4_GG_5zn.txt, LOX_CH4_GG_5zn.csv):
    f = open(LOX_CH4_GG_5zn.txt,"r")
    csv_f = csv.reader(f)

relevantData = False
firstDataSet = True



for row in csv_f:
    string = str(row)
    if 'O/F' in string and firstDataSet:
        relevantData = True
    elif 'MOLE FRACTIONS' in string:
        relevantData = False
        firstDataSet = False
    if firstDataSet and relevantData:
        print (string)

f.close()

    if __name__ == "__main__":
    args = parse_args()
    read_post_file(args.infile.name, args.outfile.name)



workbook = xlsxwriter.Workbook('Com.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 20)

bold = workbook.add_format({'bold': True})

worksheet.write('A1', 'O/F', bold)
worksheet.write('B1', 'PC/P', bold)
worksheet.write('C1', 'P,PSIA', bold)
worksheet.write('D1', 'T,DEG(R)', bold)
worksheet.write('E1', 'H,BTU/LB', bold)
worksheet.write('F1', 'S,BTU/(LB)(R)', bold)
worksheet.write('G1', 'DEN,LBM/FT3', bold)
worksheet.write('H1', 'M,MOL WT', bold)
worksheet.write('I1', '(DLV/DLP)T', bold)
worksheet.write('J1', '(DLV/DLP)P', bold)
worksheet.write('I1', 'CP,BTU/(LB)(R)', bold)
worksheet.write('K1', 'CP GAS,SF', bold)
worksheet.write('L1', 'GAMMA GAS,SF', bold)
worksheet.write('K1', 'GAMMA,S', bold)
worksheet.write('K1', 'SON VEL,FT/SEC', bold)
worksheet.write('K1', 'MU,LBF-S/FT2', bold)
worksheet.write('K1', 'K,LBF/S-DEG(R)', bold)
worksheet.write('K1', 'PRANDTL NO', bold)
worksheet.write('K1', 'MACH NUMBER', bold)
worksheet.write('K1', 'AE/AT', bold)
worksheet.write('K1', 'CSTAR,FT/SEC', bold)
worksheet.write('K1', 'CF VAC', bold)
worksheet.write('K1', 'CF', bold)
worksheet.write('K1', 'IVAC, LBF-SEC/LBM', bold)
worksheet.write('K1', 'I,LBF-SEC/LBM', bold)
worksheet.write('K1', 'MOL WT(MIX)', bold)

workbook.close()

Any help is greatly appreciated one this.

input 1

The output is what was posted in the beginning of the question.

please provide a sample input TXT file (5-7 rows would be enough) and an expected output (it might be also in CSV format or a link to an Excel file) — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Mar 7, 2016 at 19:48
Why is K1 overwritten so many time. Is this the correct code? — Barun Sharma
– Barun Sharma, Commented Mar 7, 2016 at 19:48
You should write to C:\Users\Public..... I have no idea where you're writing too. Might not have privs... and make sure you're looking at the right file...E.g. workbook = xlsxwriter.Workbook('C:\Users.....\Test.xlsx') — FirebladeDan
– FirebladeDan, Commented Mar 7, 2016 at 19:51
@Z_Russ89, If your input file is a CSV-like file, then it should be pretty straightforward to implement it using Pandas. That said waiting for input and output samples... — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Mar 7, 2016 at 19:54
Plus can you just attempt your first write? worksheet.write('A1', 'O/F', bold) — FirebladeDan
– FirebladeDan, Commented Mar 7, 2016 at 19:54

MaxU - stand with Ukraine · Accepted Answer · 2016-03-07 20:53:15Z

1

Here is a small working prototype which shows how to do it using Pandas.

As OP didn't provide sample for input data I've generated a sample data frame: 8 columns and 20 rows. In this example I will select only 3 columns ('A','C','H') and transpose them (convert columns to rows).

Code:

import six
import numpy as np
import pandas as pd

# generate random DataFrame with 8 columns and 20 rows
sample = pd.DataFrame(np.random.randint(0,100,size=(20, 8)), columns=list('ABCDEFGH'))

csv_str = six.StringIO()
sample.to_csv(csv_str, sep=';', index=False)
print('sample data\n')
print(csv_str.getvalue())

# save sample data into CSV file: 'sample.csv'
sample.to_csv('sample.csv', sep=';', index=False)

# create DF from sample data (we want to use only the following columns: 'A' 'C' 'H')
df = pd.read_csv('sample.csv', sep=';', usecols=['A','C','H'])
# transpose input DF
out_df = df.T
print(out_df)

# save result DF as excel file
xlwriter = pd.ExcelWriter('out.xlsx')
out_df.to_excel(xlwriter, sheet_name='Output', index=True)
xlwriter.save()

Output:

sample data

A;B;C;D;E;F;G;H
51;81;17;32;94;81;18;74
37;95;13;68;83;55;26;23
5;76;91;11;71;93;17;58
42;11;86;52;44;39;82;90
20;85;25;13;58;87;18;68
49;64;25;15;19;32;49;85
40;11;91;40;47;73;3;37
91;67;97;21;71;56;96;86
88;40;98;15;21;8;17;74
22;64;28;34;8;14;2;21
42;34;50;69;48;5;56;23
67;97;89;70;81;50;8;75
96;95;81;11;48;63;35;74
36;50;84;2;99;56;25;69
35;48;85;84;80;51;26;23
29;53;50;21;13;40;55;92
33;63;40;77;47;91;71;73
69;18;15;80;83;4;23;84
51;80;72;60;23;56;45;48
55;4;60;87;28;82;14;63

   0   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  \
A  51  37   5  42  20  49  40  91  88  22  42  67  96  36  35  29  33  69  51
C  17  13  91  86  25  25  91  97  98  28  50  89  81  84  85  50  40  15  72
H  74  23  58  90  68  85  37  86  74  21  23  75  74  69  23  92  73  84  48

   19
A  55
C  60
H  63

generated sample CSV file

output Excel file

@Z_Russ89, i would use your real input/output data if you would post sample input and expected output, but you can use this example as a starting point.

edited Mar 7, 2016 at 20:53

answered Mar 7, 2016 at 20:48

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

2 Comments

Z_Russ89 Over a year ago

gave the input file but the output numbers i am aiming for are already listed above.

MaxU - stand with Ukraine Over a year ago

I showed you the way - feel free to use or not. If i would have this input sample when i worked on that solution i would have taken it and i would build my solution for that sample, but now i lost interest... ;)

Collectives™ on Stack Overflow

Parsing in python to excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related