So I have some dataframes based on the geographical location of vehicles. I have already pre-processed the data plenty and I am only missing a final column, which is a future location based on already known data. So the data spans the entire years of 2018 and 2019, and I have additional data from early 2020 to populate end of 2019. So basically, for each individual row of data, there is a "Zone" location for each individual vehicle, which has a unique ID. I need each row to be populated with the "Zone" 20 days into the future.
To show how the dataframe looks, here is a simplified version of it:
| ID | Date | CurrentZone | FutureZone20Days |
|---|---|---|---|
| 1 | 2018-01-01 | ZoneX | ? |
| 2 | 2018-02-01 | ZoneZ | ? |
| 3 | 2018-03-01 | ZoneY | ? |
| 4 | 2018-04-01 | ZoneV | ? |
| 5 | 2018-05-01 | ZoneR | ? |
| 6 | 2018-06-01 | ZoneJ | ? |
| 7 | 2018-07-01 | ZoneL | ? |
| 8 | 2018-08-01 | ZoneO | ? |
| 9 | 2018-09-01 | ZoneU | ? |
| 10 | 2018-10-01 | ZoneG | ? |
So I attempted a super-silly way of doing this, which seems to work, but I haven't allowed the code to finish on a large sample yet, since its runtime is like O(2^n). Its a nested-for-loop over two version of the dataframe, and it looks like this, while it might not help anyone to answer the question:
for i, r in df.iterrows():
vehicle = df.loc[i, "ID"]
date = df.loc[i, "date"]
for ind, row in df1.iterrows():
vehicle1 = df1.loc[ind, "ID"]
date1 = df1.loc[ind, "date"]
zone = df1.loc[ind, "Current Zone"]
if (vehicle == vehicle1) & (date1 == date+timedelta(days=20)):
df1.loc[i, "FutureLoc20Days"] = zone
As a final note, there are actually 15 columns in the dataset, if that somehow affects the solution, as I read some similar posts about setting indexes etc.
I hope someone can help me with this. Thanks.