Pandas - how to coerce only empty string values in dataframe column to NaN

Question

With data like below

colVals = [['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '18', '', '3', '   185', '892', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '21', '', '3', '   185', '892', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '17', '', '3', '    73', '892', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '17', '', '3', '   185', '892', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '21', '', '3', '    73', '892', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.708', '268', '4', '28', '-67.60', '13', '', '2', '', '', '', '', '2', '13.128', '', '', '', '', '', '', ''], ['05:17:55.711', '', '', '', '', '', '18', '', '3', '    57', '892', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.711', '', '', '', '', '', '21', '', '3', '   201', '892', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.711', '', '', '', '', '', '21', '', '3', '    25', '892', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.723', '', '', '', '', '', '21', '', '3', '   217', '893', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.723', '', '', '', '', '', '21', '', '3', '   217', '893', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.723', '', '', '', '', '', '21', '', '3', '   217', '893', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.726', '268', '4', '', '-67.80', '', '', '', '', '', '', '', '', '12.902', '', '', '', '', '', '', ''], ['05:17:55.728', '', '', '28', '', '12', '31', '2', '3', '   185', '894', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.728', '', '', '', '', '', '31', '', '3', '   185', '894', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '31', '', '3', '   217', '894', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '20', '', '3', '   217', '894', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '20', '', '3', '   217', '894', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '20', '', '3', '   217', '894', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.743', '', '', '', '', '', '20', '', '3', '   217', '895', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.743', '', '', '', '', '', '20', '', '3', '   217', '895', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.743', '', '', '', '', '', '20', '', '3', '   217', '895', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.746', '268', '4', '', '-67.82', '', '', '', '', '', '', '', '', '13.243', '', '', '', '', '', '', ''], ['05:17:55.747', '', '', '28', '', '13', '20', '2', '3', '   185', '896', '1', '2', '', '', '', '', '', '', '', ''], ['05:17:55.747', '', '', '', '', '', '20', '', '3', '   185', '896', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.747', '', '', '', '', '', '30', '', '3', '   217', '896', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '18', '', '3', '   217', '896', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '21', '', '3', '   217', '896', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '21', '', '3', '   217', '896', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '21', '', '3', '   217', '896', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '31', '', '3', '   217', '897', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '30', '', '3', '   217', '897', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '20', '', '3', '   217', '897', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '20', '', '3', '   217', '897', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.766', '268', '4', '', '-67.13', '', '', '', '', '', '', '', '', '13.685', '', '', '', '', '', '', ''], ['05:17:55.768', '', '', '28', '', '12', '20', '2', '3', '   185', '898', '3', '2', '', '', '', '', '', '', '', ''], ['05:17:55.768', '', '', '', '', '', '16', '', '3', '   217', '898', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.771', '', '', '', '', '', '18', '', '3', '   217', '898', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.771', '', '', '', '', '', '20', '', '3', '   217', '898', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.771', '', '', '', '', '', '20', '', '3', '   217', '898', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '    41', '899', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '    25', '899', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.786', '268', '4', '', '-67.66', '', '', '', '', '', '', '', '', '13.683', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '28', '', '13', '18', '2', '3', '   185', '900', '0', '2', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '20', '', '3', '   185', '900', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '20', '', '3', '   185', '900', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '16', '', '3', '    41', '900', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '17', '', '3', '   185', '900', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '   105', '900', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '    89', '900', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '18', '', '3', '   217', '900', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '   217', '900', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '    25', '900', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.806', '268', '4', '', '-67.50', '', '', '', '', '', '', '', '', '14.023', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '28', '', '12', '31', '2', '3', '   185', '902', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '31', '', '3', '   185', '902', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '20', '', '3', '    25', '902', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '20', '', '3', '    25', '902', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '16', '', '3', '   217', '902', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '16', '', '3', '   217', '902', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '    89', '902', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '   121', '902', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '18', '', '3', '   217', '902', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '   217', '902', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '    73', '902', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '     9', '902', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '20', '', '3', '   217', '902', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '20', '', '3', '    25', '902', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '20', '', '3', '   217', '902', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '18', '', '3', '   217', '903', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '21', '', '3', '   217', '903', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '19', '', '3', '   105', '903', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '21', '', '3', '    41', '903', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   217', '903', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '     9', '903', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   105', '903', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   217', '903', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   217', '903', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   121', '903', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '    89', '903', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.826', '268', '4', '', '-67.51', '', '', '', '', '', '', '', '', '14.370', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '28', '', '12', '18', '2', '3', '   185', '904', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '21', '', '3', '   185', '904', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '21', '', '3', '   185', '904', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '21', '', '3', '   185', '904', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '17', '', '3', '   217', '904', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '17', '', '3', '   217', '904', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '21', '', '3', '   217', '904', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '20', '', '3', '   169', '904', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '20', '', '3', '   217', '904', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '20', '', '3', '   217', '904', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.846', '268', '4', '', '-67.01', '', '', '', '', '', '', '', '', '14.227', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '28', '', '13', '19', '2', '3', '    57', '906', '1', '2', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '19', '', '3', '    41', '906', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '19', '', '3', '    73', '906', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '16', '', '3', '   217', '906', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '16', '', '3', '   217', '906', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '19', '', '3', '     9', '906', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.851', '', '', '', '', '', '20', '', '3', '   121', '906', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.851', '', '', '', '', '', '20', '', '3', '    57', '906', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.851', '', '', '', '', '', '20', '', '3', '   105', '906', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   217', '906', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   217', '906', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   105', '906', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '17', '', '3', '   185', '907', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   217', '907', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '     9', '907', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   217', '907', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '    57', '907', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   153', '907', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   217', '907', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   217', '907', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '    57', '907', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   105', '907', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '17', '', '3', '   185', '908', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '20', '', '3', '   185', '908', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '20', '', '3', '     9', '908', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '16', '', '3', '    73', '908', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.868', '268', '4', '28', '-66.79', '13', '', '2', '', '', '', '', '2', '13.969', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '20', '', '3', '   105', '908', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '20', '', '3', '    25', '908', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '17', '', '3', '   217', '908', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '21', '', '3', '   217', '908', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '21', '', '3', '     9', '908', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '21', '', '3', '   121', '908', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '   217', '908', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '   217', '908', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '    57', '908', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '18', '', '3', '    73', '909', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '   217', '909', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '    89', '909', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '    89', '909', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.886', '268', '4', '', '-67.48', '', '', '', '', '', '', '', '', '13.857', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '28', '', '12', '31', '2', '3', '   185', '910', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '21', '', '3', '    89', '910', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '21', '', '3', '     9', '910', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '21', '', '3', '   185', '910', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '17', '', '3', '   217', '910', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '17', '', '3', '   217', '910', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '    89', '910', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '    73', '910', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '18', '', '3', '   217', '910', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '   217', '910', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '    57', '910', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-10.0', '  8', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 13.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 17.0', ' 48', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  0', ' 12', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 24', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  8', ' 12', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -6.0', '  4', ' 24', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -4.0', '  8', ' 32', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'SS_0', '  1.0', '  0', '216', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 16.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -4.0', '  8', ' 32', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -3.0', ' 12', ' 44', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 15.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-11.0', '  0', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-11.0', '  0', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -8.0', '  8', ' 16', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-15.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'SS_0', ' -1.0', '  0', '216', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  0', ' 16', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 14.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-15.0', ' 12', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  8', ' 24', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  6.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-16.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -6.0', '  8', ' 44', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -6.0', '  0', ' 44', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  7.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-16.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-17.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', '  4', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 14.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-16.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', '  0', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.900', '', '', '', '', '', '21', '', '3', '   217', '911', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '17', '', '3', '   217', '911', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '17', '', '3', '   217', '911', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '21', '', '3', '   105', '911', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '21', '', '3', '    25', '911', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '18', '', '3', '   217', '911', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   217', '911', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '    57', '911', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   153', '911', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   217', '911', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   217', '911', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   137', '911', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.906', '268', '4', '', '-67.79', '', '', '', '', '', '', '', '', '14.234', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '28', '', '13', '18', '2', '3', '    57', '912', '0', '2', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '21', '', '3', '   185', '912', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '21', '', '3', '    57', '912', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '17', '', '3', '   137', '912', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '17', '', '3', '   169', '912', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.923', '', '', '', '', '', '21', '', '3', '   217', '913', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.923', '', '', '', '', '', '21', '', '3', '    89', '913', '12', '', '', '', '', '', '', '', '', '']]
colNames = ['Time', 'Col01', 'Col02', 'Col03', 'Col04', 'Col05', 'Col06', 'Col07', 'Col08', 'Col09', 'Col10', 'Col11', 'Col12', 'Col13', 'Col14', 'Col15', 'Col16', 'Col17', 'Col18', 'Col19', 'Col20']

I'm creating a data frame

df = pd.DataFrame(colVals, columns=colNames)

This gives all the columns as object (Strings)

df.dtypes
Out[28]: 
Time     object
Col01    object
Col02    object
Col03    object
Col04    object
Col05    object
Col06    object
Col07    object
Col08    object
Col09    object
Col10    object
Col11    object
Col12    object
Col13    object
Col14    object
Col15    object
Col15    object
Col16    object
Col17    object
Col18    object
Col19    object
dtype: object

I want to convert the columns as appropiate data types as floats, int and strings. For example Col01 should be int, Col04 should be float and Col15 should be string.

using the answer here -

df = df.set_index('Time')
def func(x):
    a = pd.to_numeric(x, errors='coerce')
    try:
        return a.astype('Int64')
    except:
        return a

df1 = df.apply(func)

This works for floats and int type columns, but it also converts all strings to NaN. The desired results is that only null strings '' shall be converted to NaN. How to go about this?

Your function is working fine when i run it. I get `NAN` only where there is no value, the values are retained as is for the rest. — moys, Oct 03 '19 at 06:15
Per numpy, if you have a NaN, it is a float (not an int). You cannot mix integers with NaNs. https://stackoverflow.com/questions/12708807/numpy-integer-nan — Alexander, Oct 03 '19 at 06:17
@Alexander with pandas 0.24+, you can have `int` type for a col even if 'NaN` is present. See https://pandas.pydata.org/pandas-docs/stable/user_guide/integer_na.html#nullable-integer-data-type — user3206440, Oct 03 '19 at 07:17
@user3206440 I thought this may have changed at some point. Thanks for the reminder and for the version where the switch occurred. — Alexander, Oct 03 '19 at 07:21

jezrael · Accepted Answer · 2019-10-08T07:17:49.950

I think you need convert to floats first and then to Int64:

df = df.set_index('Time')
df1 = df.mask(df.eq(''))

def func(x):
    try:
        return x.astype('float').astype('Int64') #pd 0.24+ 
    except:
        try:
            return x.astype('float')
        except:
            return x
df2 = df1.apply(func)

print (df2.dtypes)
Col01      Int64
Col02      Int64
Col03      Int64
Col04    float64
Col05      Int64
Col06      Int64
Col07      Int64
Col08      Int64
Col09      Int64
Col10      Int64
Col11      Int64
Col12      Int64
Col13    float64
Col14      Int64
Col15     object
Col16      Int64
Col17      Int64
Col18      Int64
Col19      Int64
Col20      Int64
dtype: object

You can investigate unique values - floats are returned only because at least 1 non NaNs floats (Col04, Col13) and object only if at least 1 non NaN string value (Col15):

print (df1.apply(lambda x: pd.Series(x.unique())))
   Col01 Col02 Col03   Col04 Col05 Col06 Col07 Col08   Col09 Col10 Col11  \
0    NaN   NaN   NaN     NaN   NaN    21   NaN     3     217   891    12   
1    268     4    28  -67.60    13    18     2   NaN     185   892    13   
2    NaN   NaN   NaN  -67.80    12    17   NaN   NaN      73   NaN    15   
3    NaN   NaN   NaN  -67.82   NaN   NaN   NaN   NaN     NaN   893    16   
4    NaN   NaN   NaN  -67.13   NaN    31   NaN   NaN      57   894    17   
5    NaN   NaN   NaN  -67.66   NaN    20   NaN   NaN     201   895    18   
6    NaN   NaN   NaN  -67.50   NaN    30   NaN   NaN      25   896     0   
7    NaN   NaN   NaN  -67.51   NaN    16   NaN   NaN      41   897     1   
8    NaN   NaN   NaN  -67.01   NaN    19   NaN   NaN     105   898     5   
9    NaN   NaN   NaN  -66.79   NaN   NaN   NaN   NaN      89   899     6   
10   NaN   NaN   NaN  -67.48   NaN   NaN   NaN   NaN     121   900     7   
11   NaN   NaN   NaN  -67.79   NaN   NaN   NaN   NaN       9   902   NaN   
12   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     169   903    10   
13   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     153   904    11   
14   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     137   906     2   
15   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   907     3   
16   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   908     8   
17   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   909   NaN   
18   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   910   NaN   
19   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   911   NaN   
20   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   912   NaN   
21   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   913   NaN   
22   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   NaN   NaN   
23   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   NaN   NaN   
24   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   NaN   NaN   
25   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   NaN   NaN   
26   NaN   NaN   NaN     NaN   NaN   NaN   NaN   NaN     NaN   NaN   NaN   

   Col12   Col13 Col14 Col15  Col16 Col17 Col18 Col19 Col20  
0    NaN     NaN   NaN   NaN    NaN   NaN   NaN   NaN   NaN  
1      2  13.128     0   PSH  -10.0     8     8     0   NaN  
2      1  12.902    13   PCH   13.0    48     1   NaN   NaN  
3    NaN  13.243   NaN  SS_0  -13.0    20     4   NaN   NaN  
4    NaN  13.685   NaN   NaN   17.0     0    12   NaN   NaN  
5    NaN  13.683   NaN   NaN   -9.0    24    24   NaN   NaN  
6    NaN  14.023   NaN   NaN   12.0     4    32   NaN   NaN  
7    NaN  14.370   NaN   NaN   -6.0    12   216   NaN   NaN  
8    NaN  14.227   NaN   NaN   -4.0    16     5   NaN   NaN  
9    NaN  13.969   NaN   NaN   11.0   NaN    44   NaN   NaN  
10   NaN  13.857   NaN   NaN    1.0   NaN    16   NaN   NaN  
11   NaN  14.234   NaN   NaN   16.0   NaN   NaN   NaN   NaN  
12   NaN     NaN   NaN   NaN   -3.0   NaN   NaN   NaN   NaN  
13   NaN     NaN   NaN   NaN    9.0   NaN   NaN   NaN   NaN  
14   NaN     NaN   NaN   NaN   10.0   NaN   NaN   NaN   NaN  
15   NaN     NaN   NaN   NaN  -14.0   NaN   NaN   NaN   NaN  
16   NaN     NaN   NaN   NaN   15.0   NaN   NaN   NaN   NaN  
17   NaN     NaN   NaN   NaN    8.0   NaN   NaN   NaN   NaN  
18   NaN     NaN   NaN   NaN  -11.0   NaN   NaN   NaN   NaN  
19   NaN     NaN   NaN   NaN   -8.0   NaN   NaN   NaN   NaN  
20   NaN     NaN   NaN   NaN  -15.0   NaN   NaN   NaN   NaN  
21   NaN     NaN   NaN   NaN   -1.0   NaN   NaN   NaN   NaN  
22   NaN     NaN   NaN   NaN   14.0   NaN   NaN   NaN   NaN  
23   NaN     NaN   NaN   NaN    6.0   NaN   NaN   NaN   NaN  
24   NaN     NaN   NaN   NaN  -16.0   NaN   NaN   NaN   NaN  
25   NaN     NaN   NaN   NaN    7.0   NaN   NaN   NaN   NaN  
26   NaN     NaN   NaN   NaN  -17.0   NaN   NaN   NaN   NaN

Aryerez · Answer 2 · 2019-10-03T07:48:55.307

0

Try changing the function to:

def func(x):
    try:
        return x.astype('Int64')
    except:
        try:
            return x.astype('float')
        except:
            return x

edited Oct 03 '19 at 07:48

answered Oct 03 '19 at 06:18

Aryerez

3,417
2
9
17

shouldn't this be like `x.astype('Int64')`. Also all the types are of `object` – user3206440 Oct 03 '19 at 07:23
@user3206440 1) You are right. I've copy pasted it without noticing `x` changed to `a` in the part I've passed. Edited it. 2) I didn't understand. If it won't succeed converting to `int` or `float` than it'll return `object` like he wanted. – Aryerez Oct 03 '19 at 07:51
`df1.dtypes Out[51]: Col01 object Col02 object Col03 object Col04 object Col05 object Col06 object Col07 object Col08 object Col09 object Col10 object Col11 object Col12 object Col13 object Col14 object Col15 object Col16 object Col17 object Col18 object Col19 object Col20 object dtype: object` – user3206440 Oct 03 '19 at 12:05
guess this is because all columns have some empty strings in it and hence end up in the last `except` block – user3206440 Oct 03 '19 at 12:26
@user3206440 If it's always empty string `''`, you can try to add `x = x.astype(str).str.replace('',np.nan)` before the first `try` – Aryerez Oct 03 '19 at 12:36
`TypeError: ('repl must be a string or callable', 'occurred at index Col01')` – user3206440 Oct 03 '19 at 13:37

ansev · Answer 3 · 2019-10-05T10:43:57.333

You can mask the values '' of the original data frame using mask. And use this data frame to replace the NaN values after applying the function (over the original data frame df) using fillna

df = df.set_index('Time')
def func(x):
    a = pd.to_numeric(x, errors='coerce')
    try:
        return a.astype('Int64')
    except:
        return a
df1 = df.apply(func,axis=1).fillna(df.mask(df.eq('')))

Col15 df output:

df['Col15'].values

array(['', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
       '', '', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH',
       'PSH', 'PCH', 'PCH', 'PSH', 'PCH', 'PCH', 'PSH', 'PCH', 'SS_0',
       'PCH', 'PSH', 'PCH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH',
       'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH',
       'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH',
       'PCH', 'PSH', 'PCH', 'SS_0', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH',
       'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH',
       'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', '', '', '',
       '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''],
      dtype=object)

Col15 df1 output:

array([nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, 'PSH',
       'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH',
       'PCH', 'PSH', 'PCH', 'PCH', 'PSH', 'PCH', 'SS_0', 'PCH', 'PSH',
       'PCH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH',
       'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH',
       'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH',
       'PCH', 'SS_0', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH',
       'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH',
       'PSH', 'PCH', 'PSH', 'PCH', 'PSH', 'PCH', nan, nan, nan, nan, nan,
       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,
       nan, nan], dtype=object)

The problem with the solution is that with the step `a = pd.to_numeric(x, errors='coerce')` in `func` all non numeric characters are converted to `NaN`. Eg. just try `df.appy(pd.to_numeric, errors='coerce')` — user3206440, Oct 08 '19 at 06:11
I think algorithmic-ally, first step should be to fill `NaN` where value == '' . Then convert to int, else float else retain original - see below. — user3206440, Oct 08 '19 at 06:47

score 0 · Answer 4 · answered Oct 08 '19 at 06:58

colVals = [['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.703', '', '', '', '', '', '21', '', '3', '   217', '891', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '18', '', '3', '   185', '892', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '21', '', '3', '   185', '892', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '17', '', '3', '    73', '892', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '17', '', '3', '   185', '892', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.707', '', '', '', '', '', '21', '', '3', '    73', '892', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.708', '268', '4', '28', '-67.60', '13', '', '2', '', '', '', '', '2', '13.128', '', '', '', '', '', '', ''], ['05:17:55.711', '', '', '', '', '', '18', '', '3', '    57', '892', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.711', '', '', '', '', '', '21', '', '3', '   201', '892', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.711', '', '', '', '', '', '21', '', '3', '    25', '892', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.723', '', '', '', '', '', '21', '', '3', '   217', '893', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.723', '', '', '', '', '', '21', '', '3', '   217', '893', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.723', '', '', '', '', '', '21', '', '3', '   217', '893', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.726', '268', '4', '', '-67.80', '', '', '', '', '', '', '', '', '12.902', '', '', '', '', '', '', ''], ['05:17:55.728', '', '', '28', '', '12', '31', '2', '3', '   185', '894', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.728', '', '', '', '', '', '31', '', '3', '   185', '894', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '31', '', '3', '   217', '894', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '20', '', '3', '   217', '894', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '20', '', '3', '   217', '894', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.731', '', '', '', '', '', '20', '', '3', '   217', '894', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.743', '', '', '', '', '', '20', '', '3', '   217', '895', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.743', '', '', '', '', '', '20', '', '3', '   217', '895', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.743', '', '', '', '', '', '20', '', '3', '   217', '895', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.746', '268', '4', '', '-67.82', '', '', '', '', '', '', '', '', '13.243', '', '', '', '', '', '', ''], ['05:17:55.747', '', '', '28', '', '13', '20', '2', '3', '   185', '896', '1', '2', '', '', '', '', '', '', '', ''], ['05:17:55.747', '', '', '', '', '', '20', '', '3', '   185', '896', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.747', '', '', '', '', '', '30', '', '3', '   217', '896', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '18', '', '3', '   217', '896', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '21', '', '3', '   217', '896', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '21', '', '3', '   217', '896', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.751', '', '', '', '', '', '21', '', '3', '   217', '896', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '31', '', '3', '   217', '897', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '30', '', '3', '   217', '897', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '20', '', '3', '   217', '897', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.763', '', '', '', '', '', '20', '', '3', '   217', '897', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.766', '268', '4', '', '-67.13', '', '', '', '', '', '', '', '', '13.685', '', '', '', '', '', '', ''], ['05:17:55.768', '', '', '28', '', '12', '20', '2', '3', '   185', '898', '3', '2', '', '', '', '', '', '', '', ''], ['05:17:55.768', '', '', '', '', '', '16', '', '3', '   217', '898', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.771', '', '', '', '', '', '18', '', '3', '   217', '898', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.771', '', '', '', '', '', '20', '', '3', '   217', '898', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.771', '', '', '', '', '', '20', '', '3', '   217', '898', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '    41', '899', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '    25', '899', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.784', '', '', '', '', '', '20', '', '3', '   217', '899', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.786', '268', '4', '', '-67.66', '', '', '', '', '', '', '', '', '13.683', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '28', '', '13', '18', '2', '3', '   185', '900', '0', '2', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '20', '', '3', '   185', '900', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '20', '', '3', '   185', '900', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '16', '', '3', '    41', '900', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.788', '', '', '', '', '', '17', '', '3', '   185', '900', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '   105', '900', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '    89', '900', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '18', '', '3', '   217', '900', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '   217', '900', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.791', '', '', '', '', '', '20', '', '3', '    25', '900', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.806', '268', '4', '', '-67.50', '', '', '', '', '', '', '', '', '14.023', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '28', '', '12', '31', '2', '3', '   185', '902', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '31', '', '3', '   185', '902', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '20', '', '3', '    25', '902', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '20', '', '3', '    25', '902', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '16', '', '3', '   217', '902', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.808', '', '', '', '', '', '16', '', '3', '   217', '902', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '    89', '902', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '   121', '902', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '18', '', '3', '   217', '902', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '   217', '902', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '    73', '902', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.811', '', '', '', '', '', '20', '', '3', '     9', '902', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '20', '', '3', '   217', '902', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '20', '', '3', '    25', '902', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '20', '', '3', '   217', '902', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '18', '', '3', '   217', '903', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '21', '', '3', '   217', '903', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '19', '', '3', '   105', '903', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.815', '', '', '', '', '', '21', '', '3', '    41', '903', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   217', '903', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '     9', '903', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   105', '903', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   217', '903', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   217', '903', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '   121', '903', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.823', '', '', '', '', '', '21', '', '3', '    89', '903', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.826', '268', '4', '', '-67.51', '', '', '', '', '', '', '', '', '14.370', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '28', '', '12', '18', '2', '3', '   185', '904', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '21', '', '3', '   185', '904', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '21', '', '3', '   185', '904', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '21', '', '3', '   185', '904', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '17', '', '3', '   217', '904', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.828', '', '', '', '', '', '17', '', '3', '   217', '904', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '21', '', '3', '   217', '904', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '20', '', '3', '   169', '904', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '20', '', '3', '   217', '904', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.831', '', '', '', '', '', '20', '', '3', '   217', '904', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.846', '268', '4', '', '-67.01', '', '', '', '', '', '', '', '', '14.227', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '28', '', '13', '19', '2', '3', '    57', '906', '1', '2', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '19', '', '3', '    41', '906', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '19', '', '3', '    73', '906', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '16', '', '3', '   217', '906', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '16', '', '3', '   217', '906', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.848', '', '', '', '', '', '19', '', '3', '     9', '906', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.851', '', '', '', '', '', '20', '', '3', '   121', '906', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.851', '', '', '', '', '', '20', '', '3', '    57', '906', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.851', '', '', '', '', '', '20', '', '3', '   105', '906', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   217', '906', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   217', '906', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   105', '906', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '17', '', '3', '   185', '907', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '   217', '907', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.855', '', '', '', '', '', '20', '', '3', '     9', '907', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   217', '907', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '    57', '907', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   153', '907', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   217', '907', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   217', '907', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '    57', '907', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.864', '', '', '', '', '', '20', '', '3', '   105', '907', '18', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '17', '', '3', '   185', '908', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '20', '', '3', '   185', '908', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '20', '', '3', '     9', '908', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.867', '', '', '', '', '', '16', '', '3', '    73', '908', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.868', '268', '4', '28', '-66.79', '13', '', '2', '', '', '', '', '2', '13.969', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '20', '', '3', '   105', '908', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '20', '', '3', '    25', '908', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '17', '', '3', '   217', '908', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '21', '', '3', '   217', '908', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '21', '', '3', '     9', '908', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.871', '', '', '', '', '', '21', '', '3', '   121', '908', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '   217', '908', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '   217', '908', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '    57', '908', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '18', '', '3', '    73', '909', '0', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '   217', '909', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '    89', '909', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.875', '', '', '', '', '', '21', '', '3', '    89', '909', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.886', '268', '4', '', '-67.48', '', '', '', '', '', '', '', '', '13.857', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '28', '', '12', '31', '2', '3', '   185', '910', '0', '1', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '21', '', '3', '    89', '910', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '21', '', '3', '     9', '910', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '21', '', '3', '   185', '910', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '17', '', '3', '   217', '910', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.888', '', '', '', '', '', '17', '', '3', '   217', '910', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '    89', '910', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '    73', '910', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '18', '', '3', '   217', '910', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '   217', '910', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.891', '', '', '', '', '', '21', '', '3', '    57', '910', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-10.0', '  8', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 13.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 17.0', ' 48', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  0', ' 12', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 24', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  8', ' 12', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -6.0', '  4', ' 24', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -4.0', '  8', ' 32', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'SS_0', '  1.0', '  0', '216', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 16.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -4.0', '  8', ' 32', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -3.0', ' 12', ' 44', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 12.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 15.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-11.0', '  0', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-11.0', '  0', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 11.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -8.0', '  8', ' 16', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-15.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'SS_0', ' -1.0', '  0', '216', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  0', ' 16', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 14.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-15.0', ' 12', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -9.0', '  8', ' 24', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  6.0', '  0', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-16.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -6.0', '  8', ' 44', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', ' -6.0', '  0', ' 44', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  7.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-16.0', ' 16', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-17.0', ' 20', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  8.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-14.0', '  4', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 14.0', ' 48', '  5', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-16.0', '  0', '  4', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', '  9.0', ' 48', '  1', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', '  0', 'PSH', '-13.0', '  0', '  8', '  0', ''], ['05:17:55.893', '', '', '', '', '', '', '', '', '', '', '', '', '', ' 13', 'PCH', ' 10.0', ' 48', '  1', '  0', ''], ['05:17:55.900', '', '', '', '', '', '21', '', '3', '   217', '911', '3', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '17', '', '3', '   217', '911', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '17', '', '3', '   217', '911', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '21', '', '3', '   105', '911', '7', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '21', '', '3', '    25', '911', '8', '', '', '', '', '', '', '', '', ''], ['05:17:55.900', '', '', '', '', '', '18', '', '3', '   217', '911', '10', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   217', '911', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '    57', '911', '12', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   153', '911', '13', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   217', '911', '15', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   217', '911', '16', '', '', '', '', '', '', '', '', ''], ['05:17:55.903', '', '', '', '', '', '21', '', '3', '   137', '911', '17', '', '', '', '', '', '', '', '', ''], ['05:17:55.906', '268', '4', '', '-67.79', '', '', '', '', '', '', '', '', '14.234', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '28', '', '13', '18', '2', '3', '    57', '912', '0', '2', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '21', '', '3', '   185', '912', '1', '', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '21', '', '3', '    57', '912', '2', '', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '17', '', '3', '   137', '912', '5', '', '', '', '', '', '', '', '', ''], ['05:17:55.908', '', '', '', '', '', '17', '', '3', '   169', '912', '6', '', '', '', '', '', '', '', '', ''], ['05:17:55.923', '', '', '', '', '', '21', '', '3', '   217', '913', '11', '', '', '', '', '', '', '', '', ''], ['05:17:55.923', '', '', '', '', '', '21', '', '3', '    89', '913', '12', '', '', '', '', '', '', '', '', '']]
colNames = ['Time', 'Col01', 'Col02', 'Col03', 'Col04', 'Col05', 'Col06', 'Col07', 'Col08', 'Col09', 'Col10', 'Col11', 'Col12', 'Col13', 'Col14', 'Col15', 'Col16', 'Col17', 'Col18', 'Col19', 'Col20']
df = pd.DataFrame(colVals, columns=colNames)

Keep the time col as index

df = df.set_index('Time')

Fill in '' with NaN

df1 = df.mask(df.eq(''))

Function to convert to int else float else string

def func(x):
    try:
        return x.astype('Int64') #pd 0.24+ 
    except:
        try:
            return x.astype('float')
        except:
            return x
df2 = df1.apply(func)

The problem with this is that int looking values are also

df2.dtypes
Col01    float64
Col02    float64
Col03    float64
Col04    float64
Col05    float64
Col06    float64
Col07    float64
Col08    float64
Col09    float64
Col10    float64
Col11    float64
Col12    float64
Col13    float64
Col14    float64
Col15     object
Col16    float64
Col17    float64
Col18    float64
Col19    float64
Col20      Int64
dtype: object

It seems the Int64 conversion is failing on all columns except Col20 where all values are NaN

To check this on Col01

df1["Col01"].unique()
Out[37]: array([nan, '268'], dtype=object)

Applying Int64 conversion only on Col01

dtype_dict = {'Col01': 'Int64'}
df1.astype(dtype_dict)

This throws error TypeError: object cannot be converted to an IntegerDtype

Looking at pandas version

pd.__version__
Out[38]: '0.25.1'

Why is the Int64 conversion failing here. According to Nullable integer data type, this should have worked

Pandas - how to coerce only empty string values in dataframe column to NaN

4 Answers4