How to move column values from one column to another based on the data_time value on time-series

Question

I have a df

Date_time	Universe_Location_1	Universe_Location_2	Universe_Location_3	Universe_Location_4
20.06.2023 11:00	Saturn		mars	earth

Considering (i) Saturn moves from one location to another every 30 minutes (ii) Mars moves from one location to another every 45 minutes (iii) Earth Moves from one location to another every 60 minutes

Want to get the result_df on input of required time

example input 1 ------> 20.06.2023 11:30

result_df

Date_time	Universe_Location_1	Universe_Location_2	Universe_Location_3	Universe_Location_4
20.06.2023 11:30		Saturn	mars	earth

example input 2 ----> 20.06.2023 11:45

result_df

Date_time	Universe_Location_1	Universe_Location_2	Universe_Location_3	Universe_Location_4
20.06.2023 11:45		Saturn		mars earth

example input 3 ----> 20.06.2023 12:00

result_df

Date_time	Universe_Location_1	Universe_Location_2	Universe_Location_3	Universe_Location_4
20.06.2023 12:00	Earth		Saturn	mars

Request to give me suggestions on how to solve this.

Answer 1

You can model the locations of each planet as a lambda function using the time difference and modulo:

import pandas as pd

# read timestamp from string with pandas
start_time_string = "20.06.2023 11:00"
start_time = pd.to_datetime(start_time_string, format="%d.%m.%Y %H:%M")

# create a lambda function for each planet
saturn_location = lambda time : int(((time - start_time).total_seconds() / (30 * 60)) % 4)
mars_location = lambda time : int((((time - start_time).total_seconds() / (45 * 60)) + 2) % 4)
earth_location = lambda time : int((((time - start_time).total_seconds() / (60 * 60)) + 3) % 4)

# validate
print(saturn_location(start_time)) # 0
print(mars_location(start_time)) # 2
print(earth_location(start_time)) # 3

The 30, 45 and 60 in each lambda represent the number of minutes it takes the planet to go to the next location. The +2 / +3 models the initial starting position of the planets.

Note that the indexes of the planets location are zero-based. if you want to use your notation, simply add 1 each time.