The questions:
Why is the date conversion function throwing the error on empty values? How can I force convert an object into a datetime value to subsequently insert into a database table?
The following is a bit long-winded. Sorry, I try to capture only the essential parts as I have not found any working answer so far on SO for this combination of coding.
Background
Using variables (plus including lists and dicts) my import function can treat different file imports always the same. The code snippet for the import is as follows:
for dataset in pd.read_csv( file_in, chunksize = 50000, sep = col_sep, encoding = encoded, header = row_head,
names = col_names, usecols = col_names, parse_dates = col_dates, converters = col_cv_map):
...
One example for the variables is like:
col_names = [ 'code_purchase_order', 'code_container', 'type_container', 'name_vessel', 'code_carrier_bill', 'date_planned_receipt',
'date_estimate_depart', 'date_estimate_arrival', 'date_nolo_container', 'name_nolo_comment', 'date_pan_diff_eta',
'date_kn_diff_eta', 'name_port_load', 'date_adod', 'code_warehouse', 'name_ship_to', 'name_carrier',
'flag_hannover_warehouse', 'flag_extra_work_plan'
]
col_dates = [ 'date_planned_receipt', 'date_estimate_depart', 'date_estimate_arrival', 'date_nolo_container',
'date_pan_diff_eta', 'date_kn_diff_eta', 'date_adod'
]
col_cv_map = { 'type_container':cv_quote, 'date_planned_receipt':cv_forcedate, 'date_estimate_depart':cv_forcedate,
'date_estimate_arrival':cv_forcedate, 'date_nolo_container':cv_forcedate, 'date_pan_diff_eta':cv_forcedate,
'date_kn_diff_eta':cv_forcedate, 'date_adod':cv_forcedate
}
col_sep = ';'
The data frame is then written to a database table using pandas to_sql method. This works well except where no date values exist in columns. In these cases I get back an object from the date parsing instead of a date.
The function for the numeric conversion works fine, the function to force a date out of an object results in error:
UnboundLocalError: local variable 'out_dt' referenced before assignment
And I can see that the function goes into the except branch as this outputs: Error converting to date: <> Meaning the empty value can not be converted to a date result.
Coding
For comparison the cv_number function works fine, the cv_forcedate throws an exception.
def cv_number(in_obj):
"""Convert incoming string into number"""
try:
out_num = float( in_obj.replace(',', '.') )
except ValueError:
out_num = ny.nan
except:
print(f'Error converting to number: <{in_obj}> ')
return out_num
def cv_forcedate(in_obj):
"""Forces object to datetime"""
try:
# try first format yyyy/mm/dd
dt_fmt = '%Y/%m/%d'
out_dt = in_obj.apply(lambda _: datetime.strptime(_, dt_fmt)) #datetime.strptime(in_obj, dt_fmt)
except ValueError:
out_dt = ny.nat
except:
print(f'Error converting to date: <{in_obj}>')
return out_dt
When I compare the execution both seem to work the same way:
import dis
dis.dis(cv_number)
108 0 SETUP_EXCEPT 20 (to 22)
109 2 LOAD_GLOBAL 0 (float)
4 LOAD_FAST 0 (in_obj)
6 LOAD_ATTR 1 (replace)
8 LOAD_CONST 1 (',')
10 LOAD_CONST 2 ('.')
12 CALL_FUNCTION 2
14 CALL_FUNCTION 1
16 STORE_FAST 1 (out_num)
18 POP_BLOCK
20 JUMP_FORWARD 52 (to 74)
110 >> 22 DUP_TOP
24 LOAD_GLOBAL 2 (ValueError)
26 COMPARE_OP 10 (exception match)
28 POP_JUMP_IF_FALSE 46
30 POP_TOP
32 POP_TOP
34 POP_TOP
111 36 LOAD_GLOBAL 3 (ny)
38 LOAD_ATTR 4 (nan)
40 STORE_FAST 1 (out_num)
42 POP_EXCEPT
44 JUMP_FORWARD 28 (to 74)
112 >> 46 POP_TOP
48 POP_TOP
50 POP_TOP
113 52 LOAD_GLOBAL 5 (print)
54 LOAD_CONST 3 ('Error converting to number: <')
56 LOAD_FAST 0 (in_obj)
58 FORMAT_VALUE 0
60 LOAD_CONST 4 ('> ')
62 BUILD_STRING 3
64 CALL_FUNCTION 1
66 POP_TOP
68 POP_EXCEPT
70 JUMP_FORWARD 2 (to 74)
72 END_FINALLY
114 >> 74 LOAD_FAST 1 (out_num)
76 RETURN_VALUE
import dis
dis.dis(cv_forcedate)
118 0 SETUP_EXCEPT 26 (to 28)
120 2 LOAD_CONST 1 ('%Y/%m/%d')
4 STORE_DEREF 0 (dt_fmt)
121 6 LOAD_FAST 0 (in_obj)
8 LOAD_ATTR 0 (apply)
10 LOAD_CLOSURE 0 (dt_fmt)
12 BUILD_TUPLE 1
14 LOAD_CONST 2 (<code object <lambda> at 0x7f8b2d4878a0, file "<ipython-input-16-91bdd92a24b3>", line 121>)
16 LOAD_CONST 3 ('cv_forcedate.<locals>.<lambda>')
18 MAKE_FUNCTION 8
20 CALL_FUNCTION 1
22 STORE_FAST 1 (out_dt)
24 POP_BLOCK
26 JUMP_FORWARD 52 (to 80)
122 >> 28 DUP_TOP
30 LOAD_GLOBAL 1 (ValueError)
32 COMPARE_OP 10 (exception match)
34 POP_JUMP_IF_FALSE 52
36 POP_TOP
38 POP_TOP
40 POP_TOP
123 42 LOAD_GLOBAL 2 (ny)
44 LOAD_ATTR 3 (nat)
46 STORE_FAST 1 (out_dt)
48 POP_EXCEPT
50 JUMP_FORWARD 28 (to 80)
124 >> 52 POP_TOP
54 POP_TOP
56 POP_TOP
125 58 LOAD_GLOBAL 4 (print)
60 LOAD_CONST 4 ('Error converting to date: <')
62 LOAD_FAST 0 (in_obj)
64 FORMAT_VALUE 0
66 LOAD_CONST 5 ('>')
68 BUILD_STRING 3
70 CALL_FUNCTION 1
72 POP_TOP
74 POP_EXCEPT
76 JUMP_FORWARD 2 (to 80)
78 END_FINALLY
126 >> 80 LOAD_FAST 1 (out_dt)
82 RETURN_VALUE
In particular if possible I try to determine what I am doing wrong as all the demo and example coding I found has always used well formed test cases which just do not match the real life data I am working with.