python - Ignoring bad rows of data in pandas.read_csv() that break header= keyword -


i have series of messy *.csv files being read in pandas. example csv is:

instrument 35392 "log file name : station" "setup date (mmddyy) : 031114" "setup time (hhmmss) : 073648" "starting date (mmddyy) : 031114" "starting time (hhmmss) : 090000" "stopping date (mmddyy) : 031115" "stopping time (hhmmss) : 235959" "interval (hhmmss) : 010000" "sensor warmup (hhmmss) : 000200" "circltr warmup (hhmmss) : 000200"    "date","time","","temp","","spcond","","sal","","ibatt","" "mmddyy","hhmmss","","øc","","ms/cm","","ppt","","volts",""  "random message here 031114 073721 031114 083200" 03/11/14,09:00:00,"",15.85,"",1.408,"",.74,"",6.2,"" 03/11/14,10:00:00,"",15.99,"",1.96,"",1.05,"",6.3,"" 03/11/14,11:00:00,"",14.2,"",40.8,"",26.12,"",6.2,"" 03/11/14,12:00:01,"",14.2,"",41.7,"",26.77,"",6.2,"" 03/11/14,13:00:00,"",14.5,"",41.3,"",26.52,"",6.2,"" 03/11/14,14:00:00,"",14.96,"",41,"",26.29,"",6.2,"" "message 3" "message 4"**

i have been using code import *csv file, process double headers, pull out empty columns, , strip offending rows bad data:

df = pd.read_csv(badfile,parse_dates={'datetime_(ascii)': [0,1]}, sep=",", \              header=[10,11],na_values=['','na', 'nan nan'], \              skiprows=[10], encoding='cp1252')  df = df.dropna(how="all", axis=1) df = df.dropna(thresh=2) droplist = ['message', 'random'] df = df[~df['datetime_(ascii)'].str.contains('|'.join(droplist))]  df.head()  datetime_(ascii)    (temp, øc)  (spcond, ms/cm) (sal, ppt)  (ibatt, volts) 0   03/11/14 09:00:00   15.85   1.408   0.74    6.2 1   03/11/14 10:00:00   15.99   1.960   1.05    6.3 2   03/11/14 11:00:00   14.20   40.800  26.12   6.2 3   03/11/14 12:00:01   14.20   41.700  26.77   6.2 4   03/11/14 13:00:00   14.50   41.300  26.52   6.2

this working fine , dandy until have file has erronious 1 row line after header: "random message here 031114 073721 031114 083200"

the error receieve is:

    *c:\users\user\appdata\local\continuum\anaconda3\lib\site-     packages\pandas\io\parsers.py in _do_date_conversions(self, names, data)    1554             data, names = _process_date_conversion(    1555                 data, self._date_conv, self.parse_dates, self.index_col,     -> 1556                 self.index_names, names,      keep_date_col=self.keep_date_col)    1557     1558         return names, data     c:\users\user\appdata\local\continuum\anaconda3\lib\site-     packages\pandas\io\parsers.py in _process_date_conversion(data_dict,      converter, parse_spec, index_col, index_names, columns, keep_date_col)    2975     if not keep_date_col:    2976         c in list(date_cols):     -> 2977             data_dict.pop(c)    2978             new_cols.remove(c)    2979     keyerror: ('time', 'hhmmss')*

if remove line, code works fine. similarly, if remove header= line code works fine. however, want able preserve because reading in hundreds of these files.

difficulty: prefer not open each file before call pandas.read_csv() these files can rather large - don't want read , save multiple times! also, prefer real pandas/pythonic solution doesn't involve openning file first stringio buffer removing offending lines.

here's 1 approach, making use of fact skip_rows accepts callable function. function receives row index being considered, built-in limitation of parameter.

as such, callable function skip_test() first checks whether current index in set of known indices skip. if not, opens actual file , checks corresponding row see if contents match.

the skip_test() function little hacky in sense inspect actual file, although inspects until current row index it's evaluating. assumes bad line begins same string (in example case, "foo"), seems safe assumption given op. 

# example data """ foo.csv uid,a,b,c 0,1,2,3 skip me 1,11,22,33 foo 2,111,222,333  """  import pandas pd  def skip_test(r, fn, fail_on, known):     if r in known: # know want skip these         return true     # check if row index matches problem line in file     # efficiency, quit after pass row index in file     f = open(fn, "r")     data = f.read()     i, line in enumerate(data.splitlines()):         if (i == r) & line.startswith(fail_on):             return true         elif > r:             break     return false  fname = "foo.csv" fail_str = "foo" known_skip = [2] pd.read_csv(fname, sep=",", header=0,              skiprows=lambda x: skip_test(x, fname, fail_str, known_skip)) # output    uid       b    c 0    0    1    2    3 1    1   11   22   33 2    2  111  222  333

if know line random message appear on when appear, faster, can tell not inspect file contents index past potential offending line.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more