CatLady CatLady - 21 days ago 4
Python Question

Write a function from csv using dataframes to read and return column values in python

I have the following data set in a csv file:

vehicle---time-----aspd[m/s]------gspd[m/s]----hdg---alt[m-msl]

veh_1---17:19.5---0.163471505---0.140000001---213---273.8900146
veh_2---17:19.5---0.505786836---0.170000002---214---273.9100037
veh_3---17:19.8---0.173484877---0.109999999---213---273.980011
veh_4---44:12.4---18.64673424---19.22999954---316---388.9299927
veh_5---44:13.0---18.13533401---19.10000038---316---389.1700134


I am trying to write a function launch_time() with two inputs (dataframe, vehicle name) that returns the first time the gspd is reported above 10.0 m/s.
The output time must be converted from a string (HH:MM:SS.SS) to a minutes after 12:00 format.

It should look something like this:

>>> launch_time(df, veh_1)
30.0


I will use this function to iterate through each vehicle and then need to record the results into a list of tuples with the format (v_name, launch time) in launch sequence order.

It should look something like this:

'veh_1', 30.0, 'veh_2', 15.0


Disclosure: my python/pandas knowledge is very entry-level.

Answer

You can use read_csv with separator -{3,} - read csv with 3 and more -:

import pandas as pd
from pandas.compat import StringIO

temp=u"""vehicle---time-----aspd[m/s]------gspd[m/s]----hdg---alt[m-msl]

veh_1---17:19.5---0.163471505---0.140000001---213---273.8900146
veh_2---17:19.5---0.505786836---0.170000002---214---273.9100037
veh_3---17:19.8---0.173484877---0.109999999---213---273.980011
veh_4---44:12.4---18.64673424---19.22999954---316---388.9299927
veh_5---45:13.0---18.13533401---19.10000038---316---389.1700134"""
#after testing replace StringIO(temp) to filename
df = pd.read_csv(StringIO(temp), sep="-{3,}", engine='python')

print (df)
  vehicle     time  aspd[m/s]  gspd[m/s]  hdg  alt[m-msl]
0   veh_1  17:19.5   0.163472       0.14  213  273.890015
1   veh_2  17:19.5   0.505787       0.17  214  273.910004
2   veh_3  17:19.8   0.173485       0.11  213  273.980011
3   veh_4  44:12.4  18.646734      19.23  316  388.929993
4   veh_5  45:13.0  18.135334      19.10  316  389.170013

Then convert column time to_timedelta, filter all rows above 10m/s by boolean indexing, sort_values, group on vehicles using groupby, then get the first value in each group and last zip columns vehicle and time and convert to list:

df.time = pd.to_timedelta('00:' + df.time, unit='h').\
              astype('timedelta64[m]').astype(int)
req = df[df['gspd[m/s]'] > 10].\
          sort_values('time', ascending=True).\
          groupby('vehicle', as_index=False).head(1)
print(req)
  vehicle  time  aspd[m/s]  gspd[m/s]  hdg  alt[m-msl]
4   veh_5    45  18.135334      19.10  316  389.170013
3   veh_4    44  18.646734      19.23  316  388.929993

L = list(zip(req['vehicle'],req['time']))
print (L)
[('veh_5', 45), ('veh_4', 44)]