error when converting hex (string and numbers) column to int

Question

Helllo,

I have this file :

time_collect_data1,data1,time_collect_data2,data2,time_collect_data3,data3,time_collect_data4,data4
2021-06-24 00:01:11,0000000000000000,2021-06-24 00:01:11,00000000000089FE,2021-06-24 00:01:11,0000000000000000,2021-06-24 00:01:11,0000000000000000
2021-06-24 00:01:14,0000000000000000,2021-06-24 00:01:14,0000000000000002,2021-06-24 00:01:14,0000000000000000,2021-06-24 00:01:14,0000000000000000
2021-06-24 00:01:15,0000000000000000,2021-06-24 00:01:15,0000000000000000,2021-06-24 00:01:16,0000000000000000,2021-06-24 00:01:16,0000000000000000
2021-06-24 00:01:16,0000000000000000,2021-06-24 00:01:16,0000000000000000,2021-06-24 00:01:17,0000000000000000,2021-06-24 00:01:17,0000000000000000
2021-06-24 00:01:17,0000000000000000,2021-06-24 00:01:17,0000000000000000,2021-06-24 00:01:18,0000000000000000,2021-06-24 00:01:18,0000000000000000
2021-06-24 00:01:18,0000000000000000,2021-06-24 00:01:18,0000000000000000,2021-06-24 00:01:19,0000000000000000,2021-06-24 00:01:19,0000000000000000
2021-06-24 00:01:20,0000000000000000,2021-06-24 00:01:20,0000000000000000,2021-06-24 00:01:20,0000000000000000,2021-06-24 00:01:20,0000000000000000
2021-06-24 00:01:20,0000000000000000,2021-06-24 00:01:21,0000000000000000,2021-06-24 00:01:21,0000000000000000,2021-06-24 00:01:21,0000000000000000
2021-06-24 00:01:21,0000000000000000,2021-06-24 00:01:22,0000000000000000,2021-06-24 00:01:22,0000000000000000,2021-06-24 00:01:22,0000000000000000
2021-06-24 00:01:23,0000000000000000,2021-06-24 00:01:23,00000000000000A8,2021-06-24 00:01:23,0000000000000000,2021-06-24 00:01:23,0000000000000000
2021-06-24 00:01:24,0000000000000000,2021-06-24 00:01:24,000000000000913B,2021-06-24 00:01:24,0000000000000000,2021-06-24 00:01:24,0000000000000000
2021-06-24 00:01:25,0000000000000000,2021-06-24 00:01:25,0000000000000001,2021-06-24 00:01:25,0000000000000000,2021-06-24 00:01:25,0000000000000000
2021-06-24 00:01:26,0000000000000000,2021-06-24 00:01:26,0000000000000000,2021-06-24 00:01:26,0000000000000000,2021-06-24 00:01:26,0000000000000031
2021-06-24 00:01:27,0000000000000000,2021-06-24 00:01:27,0000000000000000,2021-06-24 00:01:27,0000000000000000,2021-06-24 00:01:27,0000000000000000
2021-06-24 00:01:28,0000000000000000,2021-06-24 00:01:28,0000000000000F66,2021-06-24 00:01:28,0000000000000000,2021-06-24 00:01:28,0000000000000000
2021-06-24 00:01:29,0000000000000000,2021-06-24 00:01:29,0000000000000000,2021-06-24 00:01:29,0000000000000000,2021-06-24 00:01:29,0000000000000000
2021-06-24 00:01:33,0000000000000000,2021-06-24 00:01:34,0000000000042763,2021-06-24 00:01:34,0000000000000000,2021-06-24 00:01:35,000000000000000C
2021-06-24 00:01:36,0000000000000000,2021-06-24 00:01:36,0000000000000000,2021-06-24 00:01:37,0000000000000000,2021-06-24 00:01:37,0000000000000000
2021-06-24 00:01:37,0000000000000000,2021-06-24 00:01:38,0000000000000000,2021-06-24 00:01:38,0000000000000000,2021-06-24 00:01:38,0000000000000000
2021-06-24 00:01:39,0000000000000000,2021-06-24 00:01:39,0000000000000000,2021-06-24 00:01:39,0000000000000000,2021-06-24 00:01:39,0000000000000000
2021-06-24 00:01:40,0000000000000000,2021-06-24 00:01:40,0000000000000694,2021-06-24 00:01:40,0000000000000000,2021-06-24 00:01:40,0000000000000000
2021-06-24 00:01:41,0000000000000000,2021-06-24 00:01:41,0000000000000694,2021-06-24 00:01:41,0000000000000000,2021-06-24 00:01:41,0000000000000000
2021-06-24 00:01:42,0000000000000000,2021-06-24 00:01:42,0000000000000000,2021-06-24 00:01:42,0000000000000000,2021-06-24 00:01:43,0000000000000000
2021-06-24 00:01:43,0000000000000000,2021-06-24 00:01:43,0000000000000000,2021-06-24 00:01:43,0000000000000000,2021-06-24 00:01:44,0000000000000002
2021-06-24 00:01:46,0000000000000000,2021-06-24 00:01:46,0000000000006B9E,2021-06-24 00:01:46,0000000000000000,2021-06-24 00:01:46,0000000000000000
2021-06-24 00:01:47,0000000000000000,2021-06-24 00:01:47,0000000000000000,2021-06-24 00:01:47,0000000000000000,2021-06-24 00:01:47,0000000000000000
2021-06-24 00:01:48,0000000000000000,2021-06-24 00:01:48,0000000000000000,2021-06-24 00:01:48,0000000000000000,2021-06-24 00:01:48,0000000000000000
2021-06-24 00:01:49,0000000000000000,2021-06-24 00:01:49,0000000000000000,2021-06-24 00:01:49,0000000000000000,2021-06-24 00:01:49,0000000000000000
2021-06-24 00:01:50,0000000000000000,2021-06-24 00:01:50,000000000000046A,2021-06-24 00:01:51,0000000000000000,2021-06-24 00:01:51,0000000000000000
2021-06-24 00:01:54,0000000000000000,2021-06-24 00:01:54,0000000000000000,2021-06-24 00:01:55,0000000000000000,2021-06-24 00:01:55,0000000000000000
2021-06-24 00:01:56,0000000000000000,2021-06-24 00:01:57,000000000000001C,2021-06-24 00:01:57,0000000000000000,2021-06-24 00:01:57,0000000000000000
2021-06-24 00:01:58,0000000000000000,2021-06-24 00:01:58,0000000000000000,2021-06-24 00:01:59,0000000000000000,2021-06-24 00:01:59,0000000000000000
2021-06-24 00:02:01,0000000000000000,2021-06-24 00:02:02,0000000000000000,2021-06-24 00:02:02,0000000000000000,2021-06-24 00:02:02,0000000000000000
2021-06-24 00:02:03,0000000000000000,2021-06-24 00:02:03,0000000000000000,2021-06-24 00:02:03,0000000000000000,2021-06-24 00:02:03,0000000000000000
2021-06-24 00:02:04,0000000000000000,2021-06-24 00:02:04,0000000000000000,2021-06-24 00:02:04,0000000000000000,2021-06-24 00:02:04,0000000000000000
2021-06-24 00:02:05,0000000000000000,2021-06-24 00:02:05,0000000000000000,2021-06-24 00:02:05,0000000000000000,2021-06-24 00:02:05,0000000000000000
2021-06-24 00:02:06,0000000000000000,2021-06-24 00:02:06,0000000000000000,2021-06-24 00:02:06,0000000000000000,2021-06-24 00:02:07,0000000000000000
2021-06-24 00:02:07,0000000000000000,2021-06-24 00:02:07,0000000000000000,2021-06-24 00:02:08,0000000000000000,2021-06-24 00:02:08,0000000000000000
2021-06-24 00:02:08,0000000000000000,2021-06-24 00:02:09,0000000000000000,2021-06-24 00:02:09,0000000000000000,2021-06-24 00:02:09,0000000000000000
2021-06-24 00:02:09,0000000000000000,2021-06-24 00:02:10,0000000000000000,2021-06-24 00:02:10,0000000000000000,2021-06-24 00:02:10,0000000000000000
2021-06-24 00:02:12,0000000000000000,2021-06-24 00:02:12,0000000000000000,2021-06-24 00:02:12,0000000000000000,2021-06-24 00:02:13,0000000000000000
2021-06-24 00:02:14,0000000000000000,2021-06-24 00:02:14,0000000000000000,2021-06-24 00:02:14,0000000000000000,2021-06-24 00:02:14,0000000000000000
2021-06-24 00:02:15,0000000000000000,2021-06-24 00:02:16,0000000000000000,2021-06-24 00:02:17,0000000000000000,2021-06-24 00:02:17,0000000000000000
2021-06-24 00:02:18,0000000000000000,2021-06-24 00:02:18,0000000000000000,2021-06-24 00:02:18,0000000000000000,2021-06-24 00:02:18,0000000000000000
2021-06-24 00:02:19,0000000000000000,2021-06-24 00:02:19,0000000000000000,2021-06-24 00:02:19,0000000000000000,2021-06-24 00:02:19,0000000000000000
2021-06-24 00:02:20,0000000000000000,2021-06-24 00:02:20,0000000000000003,2021-06-24 00:02:20,0000000000000000,2021-06-24 00:02:20,0000000000000000
2021-06-24 00:02:21,0000000000000000,2021-06-24 00:02:22,0000000000000005,2021-06-24 00:02:22,0000000000000000,2021-06-24 00:02:22,0000000000000000
2021-06-24 00:02:23,0000000000000000,2021-06-24 00:02:23,0000000000000000,2021-06-24 00:02:23,0000000000000000,2021-06-24 00:02:23,0000000000000000
2021-06-24 00:02:28,0000000000000000,2021-06-24 00:02:28,0000000000000001,2021-06-24 00:02:28,0000000000000000,2021-06-24 00:02:28,0000000000000000
2021-06-24 00:02:29,0000000000000000,2021-06-24 00:02:29,0000000000000003,2021-06-24 00:02:29,0000000000000000,2021-06-24 00:02:29,0000000000003D5D
2021-06-24 00:02:30,0000000000000000,2021-06-24 00:02:30,0000000000000000,2021-06-24 00:02:30,0000000000000000,2021-06-24 00:02:30,0000000000000000
2021-06-24 00:03:13,,2021-06-24 00:03:19,,2021-06-24 00:03:25,,2021-06-24 00:03:31,
2021-06-24 00:03:32,0000000000000000,2021-06-24 00:03:32,0000000000086BE4,2021-06-24 00:03:32,0000000000000000,2021-06-24 00:03:32,0000000000000000
2021-06-24 00:03:33,0000000000000000,2021-06-24 00:03:33,0000000000000000,2021-06-24 00:03:33,0000000000000000,2021-06-24 00:03:34,0000000000000000
2021-06-24 00:03:34,0000000000000000,2021-06-24 00:03:34,0000000000000000,2021-06-24 00:03:34,0000000000000000,2021-06-24 00:03:35,0000000000000000
2021-06-24 00:03:35,0000000000000000,2021-06-24 00:03:35,0000000000008B7E,2021-06-24 00:03:35,0000000000000000,2021-06-24 00:03:35,0000000000000000
2021-06-24 00:03:36,0000000000000000,2021-06-24 00:03:36,0000000000AA2C11,2021-06-24 00:03:36,0000000000000000,2021-06-24 00:03:36,0000000000000000

I am trying to convert some hex column to int with this code :

df['data1']=df.data1.apply(lambda x: int(x, 16))

But I have this error because some values as already interpreted like an int :

TypeError: int() can't convert non-string with explicit base

how to force pandas to understand that my column data1,date2,data3,data4 ar a string to convert them, any others solutions please ??

thank you in advance.

Answer 1

As your data columns contains both float numbers e.g. 0.0 and hex values, also NaN values, in order to keep NaN as well as convert hex values, we can use:

df.data2.fillna(-1, downcast='infer').astype(str).apply(lambda x: int(x, 16)).replace(-1, np.nan).astype('Int64')

Answer 2

1st fill any NAN values with 0 convert the dtype to str and then convert the hex string column to int type.

df.data1 = df.data1.fillna(0).astype(str).apply(int, base=16)

Answer 3

If you're sure it's a hex number misinterpreted as an int, there are two ways:

• Explicitly tell pandas the types of each column when loading the file.

• Convert back to string then re-convert:

  df['data1']=df.data1.apply(lambda x: int(str(x), 16))