How to convert UNIX/EPOCH time by filtering the int values in a python pandas dataframe?

Question

I have a dataframe as below and would like to convert all of the UNIX times to normal date format only for the filtered int values

	ID	State	TYPE	START DATE	END DATE
0	863	MI	Hire	2023-09-01	2023-12-31
1	224	WI	Retire	2023-09-01	2023-12-31
2	567	FL	Transfer	2023-10-01	1698710400000000000
3	345	NC	NaN	NaT	None
4	432	NY	NaN	NaT	None

I tried: df["END DATE"] = pd.to_datetime(df["END DATE"], unit = 'ns') but get this error: ValueError: mixed datetimes and integers in passed array

I filtered out the int values using df.loc[df["END DATE"].apply(lambda x : isinstance(x, int))] but am unable to figure out how to apply this with pd.to_datetime

import pandas as pd
import numpy as np

data = {'ID': {0: 863, 1: 224, 2: 567, 3: 345, 4: 432}, 
        'State': {0: 'MI', 1: 'WI', 2: 'FL', 3: 'NC', 4: 'NY'}, 
        'TYPE': {0: 'Hire', 1: 'Retire', 2: 'Transfer', 3: np.nan, 4: np.nan}, 
        'START DATE': {0: pd.Timestamp('2023-09-01 00:00:00'), 1: pd.Timestamp('2023-09-01 00:00:00'), 2: pd.Timestamp('2023-10-01 00:00:00'), 3: pd.NaT, 4: pd.NaT}, 
        'END DATE': {0: '2023-12-31', 1: '2023-12-31', 2: '1698710400000000000', 3: np.nan, 4: np.nan}}

df = pd.DataFrame(data)

Does this answer your question? [Convert unix time to readable date in pandas dataframe](https://stackoverflow.com/questions/19231871/convert-unix-time-to-readable-date-in-pandas-dataframe) — Mark, Jul 15 '23 at 00:04
@Mark Thanks for sharing .. no it does not . I know how to convert it but the problem is I am unable to both filter those specific int values and convert them to normal dates — user12715151, Jul 15 '23 at 00:19
@ Mark please let me know if this is ok or if I misunderstood — user12715151, Jul 15 '23 at 00:40
The question hasn't been updated? Update: Great! Thanks for that :-) — Mark, Jul 15 '23 at 00:43
just did.. though i'm not sure why `END DATE` has 1970 while a print of the dataframe shows 2023 . `START DATE` seems to be fine in both views (print and dict) — user12715151, Jul 15 '23 at 00:45
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/254508/discussion-between-user12715151-and-mark). — user12715151, Jul 15 '23 at 01:05

score 0 · Accepted Answer · answered Jul 15 '23 at 01:38

end_dates = df["END DATE"].to_list()

for i in range(len(end_dates)):
    try: 
        end_dates[i] = pd.to_datetime(end_dates[i])
    except:
        if not pd.isnull(end_dates[i]):
            end_dates[i] = pd.to_datetime(int(end_dates[i]))

df['END DATE'] = end_dates

How to convert UNIX/EPOCH time by filtering the int values in a python pandas dataframe?

1 Answers1