Iterating over column from CSV using pandas? [closed]

The problem that I am experiencing is as following:

1. I have a csv with the following columns: 'time' (with date and time), 'id', 'lat', and 'long'. The data shows movements and id represents a mobile device.
2. I need to calculate the distance and the velocity of an activity. Activity is movement that lasted above 5 minutes (or so, a conditional that I added). Some IDs have multiple movements in a single day so I need to separate first by ID and than by time.
3. So, far, I have managed to apply several things and I am not asking for someone to do this for me. I am a newbie and I want to learn on examples. So I am trying to do this on my own. However, developing an algorithm is really hard. I know what I want but don't know how to put it.

import geopy.distance
import pandas as pd
import math

#import csv and define dtypes and headers
headers = ['time', 'id', 'lat', 'lon']
dtypes = {'time': 'str', 'id': 'str', 'lat': 'float', 'lon': 'float'}
parse_dates = ['time']

df = df.sort_values(by=['id', 'time'])

# Group the sorted dataframe by ID, and grab the initial value for lat, lon, and time.
df['lat0'] = df.groupby('id')['lat'].transform(lambda x: x.iat)
df['lon0'] = df.groupby('id')['lon'].transform(lambda x: x.iat)
df['t0'] = df.groupby('id')['time'].transform(lambda x: x.iat)

#Calculating the distance between two points
def haversine(lon1, lat1, lon2, lat2):
# convert decimal degrees to radians
lon1, lat1, lon2, lat2 = map(math.radians, [lon1, lat1, lon2, lat2])

# haversine formula
dlon = lon2 - lon1
dlat = lat2 - lat1
a = math.sin(dlat/2)**2 + math.cos(lat1) * math.cos(lat2) * math.sin(dlon/2)**2
c = 2 * math.asin(math.sqrt(a))
r = 6371 # Radius of earth in kilometers. Use 3956 for miles
return c * r

lat_1 = df.lat0
lon_1 = df.lon0
lat_2 = df.lat0
lon_2 = df.lat0
#while df.id == df.id[::1] and (df.to df.to )
#distance + distance
#return final_distance
#lat_1+=1
#lon_1+=1
####this is still in work

###while lat i lon exist:
lat_1 +=1
lon_1 +=1
return haversine

#Calculating the time different between two points
t = df.t0
def calc_minutes(time_end, time_start):
duration = (time_end - time_start).seconds
minutes = duration/60
return minutes
time start = df.t0
time_end =

###while (lat0==(lat0+=1)) and (t0==(t0+=1)):
calculate haversine
#calculate time_difference
#calculate velocity
df.id+=1
df.t0+=1
df.lat+=1
df.lon+=1
df.lat0+=1'''

### Wherever I added this is an idea of mine. It still makes no sense.

How can I iterate over a column? For example, as long as id is equal to the id in the row after it, keep calculating and adding distance between lat and long in the corresponding rows. And I need to add another conditional besides the id and that is as long as id is equal to the id in the following row and the time difference between the two rows is less than 5 minutes, keep adding.

closed as off-topic by DPSSpatial, Fran Raga, LaughU, whyzar, HornbyddJun 26 at 21:45

This question appears to be off-topic. The users who voted to close gave this specific reason:

• "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – DPSSpatial, Fran Raga, LaughU, whyzar, Hornbydd
If this question can be reworded to fit the rules in the help center, please edit the question.

• Could you add some sample data to your question, or link to the csv. I think you need to try something like How to calculate time difference by group using pandas? and then Group again and calculate distances – BERA Jun 25 at 10:45
• @BERA drive.google.com/file/d/1i7zIyuGc3htItmefNsfR7HBup1p7o8sB/… Here is the link to the csv. I'll check that out. I also need to add new columns for velocity, distance, and calculating time. But am not sure how to iterate over a column, is += usable in that case? I'll check the link that you sent now. Thank you. I am also not sure if Haversine is the right pick and if I implemented it properly, but that is a another story that I'll deal with as soon as I manage to structure the whole project. – Matija Jun 25 at 10:55

Not sure this is correct, you will have to check it. In any case you might get some ideas, for example how to Group by id and timeinterval.

import pandas as pd
from math import radians, cos, sin, sqrt, atan2

csvfile = r'C:\GIS\data\testdata\python_velocity.csv'
df = pd.read_csv(csvfile, names=['timestamp','id','lat','long'])
df['timestamp']=pd.to_datetime(df['timestamp'])

def givedistance(lat1,long1,lat2,long2):
#https://stackoverflow.com/questions/19412462/getting-distance-between-two-points-based-on-latitude-longitude
R = 6373000.0
lat1,lon1,lat2,lon2 = map(radians, [lat1,long1,lat2,long2])
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
c = 2 * atan2(sqrt(a), sqrt(1 - a))
distance = R * c
return distance

#Im sure everything below can be done in one groupby with custom functions but i dont have enough pandas skills. So I do it using more steps

def f(x): #Will create lists of lat,long and timestamps when grouping
d = {}
d['coords'] = list(zip(x['lat'],x['long']))
d['times'] = list(x['timestamp'])
return pd.Series(d, index=['coords','times'])

#groupby id and 5 min timeperiods
df2 = df.groupby(['id', pd.Grouper(key='timestamp', freq='300s')]).apply(f).reset_index()

def distance(x):
coordlist = x['coords']
return sum([givedistance(c,c,c,c) for c in zip(coordlist, coordlist[1:])])

df2['distance'] = df2.loc[df2['coords'].str.len()>1].apply(distance, axis=1)

def meanspeed(x):
timelist = x['times']
seconds = (max(timelist)-min(timelist)).seconds
if seconds>0:
return x['distance']/seconds
else:
return 0
df2['meanspeed'] = df2.loc[df2['coords'].str.len()>1].apply(meanspeed, axis=1)
df2.drop(['timestamp','coords','times'],axis=1, inplace=True) #Drop temp columns

Example of df2: • Thanks @BERA That is very helpful actually. I'll post the code here when I manage to finish it if you want? – Matija Jun 25 at 19:42
• Nice, yes please do – BERA Jun 26 at 7:59
• Will do. Thanks @BERA. One question though. Basically, if one ID (which is a mobile device) has a timestamp in the morning from let's say 09:32 to 11:35 and another in the evening from 18:32 to 19:25, the idea is to somehow make these two different rows even though they have a same ID because they are different activities. And if, let's say that same ID has a third timestamp that starts at 13:40 and ends at 13:41, to discard it (which is what your code does) as it might as well be somebody going to bathroom :D Do you think that I should create two columns from a timestamps, date and time? – Matija Jun 26 at 10:11
• They will get different rows, look at the example screenshot above five last lines are the same id – BERA Jun 26 at 13:02
• It is great but I think you misunderstood me. In your above screenshot, you see id that starts at number 4: C001AE7B. I want to calculate the total distance of that id. But, that id has two activities that I want to separate. The first activity starts at 13/11/2017 09:32 and lasts until 13/11/2017 11:58 and the second 13/11/2017 08:48 and ends at 13/11/2017 09:31. This now looks like a bug, and it is. But these go one after the other and I need to separate them and calculate the total distance per each. – Matija Jul 18 at 19:27