Convert Time Column In Pandas From Float To Actual Time Value

PROBLEM Statement #1 (EASY) I wanted to convert the time column of my dataframe to actual time value like '12:01:45' hh:mm:ss Have tried : df_new['time_1'] = pd.to_datetime(df_new

Solution 1:

For your first question, quick fix is to format your data in csv before you import. Before you load your csv into python, you could try changing 'DATE' column to 'Short date' and 'TIME' column to 'Time' in excel and save the csv (rename it so that you don't overwrite your original file) and then try importing in python?

Probably there's a clever way to do this in python.

For your second question, you may be able to get what you are after if you follow these steps:

1. Join dataframes using 'Primary key' using pd.merge()
2. Filter joined dataset to satisfy your extra conditions using df.query() (i.e. near 50 mtrs distance & max 10 mins of time difference)
3. Get counts of each primary key to get your instances using df['Primary key'].value_counts()

Solution 2:

When you give to_datetime() a plain old integer or float value, it will see that value as the number of seconds since "the UNIX epoch", or 1 January 1970. That's why the values in time_1 are all 1970-01-01. You can read more about it here if you're interested.

To solve your problem, it looks like there are two thing you'll need to address.

First, be sure that the value you're storing in TIME is the right time from the Unix epoch or find another way to store the time.

Once that's worked out, you're storing a Timestamp in time_1, so you can get the time part of it by using something like df_new["time_1"].dt.time, which should give you something in the form "hh:mm:ss". You can store those values in another column.