Python table construction

Question

I have the following data copied to an xml file from an excel sheet,I need to create a table out of this data and out the table to microsoft outlook,initially I was thinking of creating lists of everycolumn,constuct HTMl code out of it and out put to excel...how do split every line because title data can be anything,mixture of spaces etc...anyone has done this before ,appreciate good suggestions,waiting for feedback

CR  FA  CL  TITLE
12345   Dta 656885  Age out SRR values from buffer in Beacon miss scenario
16534   fta 656886  To Record stack Event Logging
76849   cta 654661  To Record HAL and FLM SW Event Logging
45678   CT  656928  CR1234: BT doesn’t work that Riva neither sends HCI Evt for HID ACL data nor response to CI after entering into real sniffer rat mode.

I don't quite follow what role Python is playing here. Excel, for all its flaws, is pretty good at making nice tables. You also say both that the table is going to Outlook and that you're going to build an HTML (an HTML table?) and output to Excel, which is a little confusing. — DSM
– DSM, Commented Nov 7, 2012 at 19:17

Burhan Khalid · Accepted Answer · 2012-11-07 19:10:31Z

2

Use csv.DictReader to get your data into a dictionary:

>>> import csv
>>> with open('t.txt') as f:
...    reader = csv.DictReader(f,dialect='excel-tab')
...    rows = list(reader)
... 
>>> rows[0]
{'TITLE': 'Age out SRR values from buffer in Beacon miss scenario',
 'CR': '12345', 'CL': '656885', 'FA': 'Dta'}

answered Nov 7, 2012 at 19:10

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

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

2 Comments

user1795998 Over a year ago

@Burkhan -import re input="""CR FA CL TITLE 12345 Dta 656885 Age out SRR values from buffer in Beacon miss scenario 16534 fta 656886 To Record stack Event Logging 76849 cta 654661 To Record HAL and FLM SW Event Logging 45678 CT 656928 CR 1234: BT doesn't work that Riva neither sends HCI Evt for HID ACL data nor response to CI after entering into real sniffer rat mode.""" for i in input.splitlines(): index = re.split(r'\W+',i,3) print "INDEX" print index

Burhan Khalid Over a year ago

So you are saying, you have a solution? Otherwise what is the point of the comment, or even the question?

Liso · Accepted Answer · 2012-11-07 19:32:55Z

1

If only last column is more complex you could use re.split ->

import re
input="""CR FA  CL  TITLE
12345   Dta 656885  Age out SRR values from buffer in Beacon miss scenario
16534   fta 656886  To Record stack Event Logging
76849   cta 654661  To Record HAL and FLM SW Event Logging
45678   CT  656928  CR1234: BT doesn’t work that Riva neither sends HCI Evt for HID ACL data nor response to CI after entering into real sniffer rat mode."""

[re.split(r'\W+',i,3) for i in input.splitlines()]

answered Nov 7, 2012 at 19:32

Liso

2,2701 gold badge17 silver badges20 bronze badges

4 Comments

user1795998 Over a year ago

can you please explain what exactly does "re.split(r'\W+',i,3) does do?I kind of understand you are splitting based on a word but not sure how does it help for the column...

user1795998 Over a year ago

nevermind I figured out,is there an easier way to construct a table out of it to export to outlook...currently logic am using is to construct html table code and output that to outlook..

Liso Over a year ago

from doc -> \w Matches any alphanumeric character; equivalent to [a-zA-Z0-9_]. With LOCALE, it will match the set [0-9_] plus characters defined as letters for the current locale. \W Matches the complement of \w.

Liso Over a year ago

but I agree better could be \s Matches any whitespace character; equivalent to [ \t\n\r\f\v].

alanmanderson · Accepted Answer · 2012-11-07 18:59:42Z

0

If you can find a pattern for what each line has in it then you can parse the line easier. Is each line 1 record? If so see below.

for line in open('myfile','r').readlines():
    # Do whatever you need to the line variable.

answered Nov 7, 2012 at 18:59

alanmanderson

8,31012 gold badges56 silver badges89 bronze badges

2 Comments

user1795998 Over a year ago

@ajon - my question is more interms of ideas on how to construct the table...pattern is always the same as above....number,string,number,sentence...first line will represent what they mean which should be the header

user1795998 Over a year ago

@ajon - I want to read each line and split based on space and construct lists for each column out of it but this fails for the last column which is a sentence

Joran Beasley · Accepted Answer · 2012-11-07 19:58:52Z

0

make sure you use a proper separator for your csv ... (see attached tweaked data file,uses tab separation) https://dl.dropbox.com/u/18004504/data.csv

import pandas as pd
df=pd.DataFrame.from_csv("data.csv",sep="\t",parse_dates=False)


print df
for row in df.iterrows(): 
    print row
    print "-------"

answered Nov 7, 2012 at 19:58

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

1 Comment

user1795998 Over a year ago

I need to construct a table to output to outlook ..import re input="""CR FA CL TITLE 12345 Dta 656885 Age out SRR values from buffer in Beacon miss scenario 45678 CT 656928 CR 1234: BT doesn't work that Riva neither sends HCI Evt for HID ACL data nor response to CI after entering into real sniffer rat mode.""" for i in input.splitlines(): index = re.split(r'\W+',i,3)

Collectives™ on Stack Overflow

Python table construction

4 Answers 4

2 Comments

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related