patrick.net

HeadSet says

Wow, that was fast. Works perfectly. Was this even much of a challenge?

No it was easier than what I did before. I don't know why I was just marking the date range, starting from 1 for every record.

Sweet!!!

I am going through the original code, how did you get the data to sort by month and then cab? I see no "sort" statements, so is that a function of the Select that makes the pivot table?

Add this to the bottom

Oh you mean on the original code I posted, the results sorted. I don't know why sometimes shit sorts properly on its own, but other times you've got to explicitly set it.

Tenpoundbass says

Oh you mean on the original code I posted, the results sorted. I don't know why sometimes shit sorts properly on its own, but other times you've got to explicitly set it.

Yes, I was wondering how the original code sorted. I guess it just knew what you wanted!

# Not sure if you are done otherwise here is one way to do it in python
# tested with both in/out xl and csv formats ex:
# python cabs.py --infile input.csv --outfile out.xlsx
# python cabs.py --infile input..xlsx --outfile out.csv
# make sure you have pandas installed in your system (recommend to use python 3)
# if not run the following command
# pip3 install pandas
# some time it may need additional modules like xlrd odfpy etc. in that case run
# pip3 install xlrd odfpy
# todo: it needs to add month-year on top of each month series
#
# added #s to the begining of each line to preserve the code indentation
# so need to remove them to get the code back
# if you use vim editor, try :1,$s/####/(type 4 spaces)/g
# let me know if you run into issues
#
#
import pandas as pd
import argparse


def process_cab_usage(infile=None, outfile=None):
####'''
####infile(str): csv or xl file with cab operating periods in the following format.
############cabM strt_dt end_dt
############...
############cabN strt_dt end_dt
####outfile(str): csv or xl file with cab operating periods in the following format.
############date dt1 dt2 ...
############cabM ''|* ...
############cabN ''|* ...
############note: '' - not used, '*' - used
####'''
####df = pd.read_csv(infile) if infile.endswith(
########('.csv', '.CSV')) else pd.read_excel(infile)
####cabs = sorted(set(df['Cab']))
####lease_start = pd.Series.min(df['LeaseStartDate'])
####lease_end = pd.Series.max(df['LeaseEndDate'])
####lease_range = pd.date_range(lease_start, lease_end)

##### mark the days for each cab in service
####dt_cab_used = {i: set() for i in cabs}
####for i in range(len(df)):
########for d in pd.date_range(df['LeaseStartDate'][i], df['LeaseEndDate'][i]):
############dt_cab_used[df['Cab'][i]].add(d)

##### create output data frame
####df_out = {'date': lease_range}
####for cab in cabs:
########df_out[f'cab{cab}'] = [''] len(lease_range) #add a star before len if you do not see it
####for idx, day in enumerate(lease_range):
########for cab in cabs:
############if day in dt_cab_used[cab]:
################df_out[f'cab{cab}'][idx] = '' # add a star in single quotes if you do not see it
####df_out['date'] = lease_range.strftime('%d') # or '%m-%d'

####df_w = pd.DataFrame(df_out)
##### Transpose DataFrame
####df_w = df_w.transpose()
####if outfile.endswith(('.csv', '.CSV')):
########df_w.to_csv(outfile, index=True)
####else:
########writer = pd.ExcelWriter(outfile)
########df_w.to_excel(writer, index=True)
########writer.save()


if _name_ == "__main__": # make sure to have 2 under scores before and after the "name"
####parser = argparse.ArgumentParser()
####parser.add_argument('--infile', help='Cab log file')
####parser.add_argument('--outfile', help='Cab usage file')
####args = parser.parse_args()
####process_cab_usage(infile=args.infile, outfile=args.outfile)

well, tried best to preserve the format while posting. Follow the instruction/comments to restore the code back. Hope you can fix it with your IDE/editor.

Nice job, why Pandas did you use Kubernetes?

Tenpoundbass says

Nice job, why Pandas did you use Kubernetes?

good question, kubernetes is a orchestration system. For data processing pandas has a lot of good features compare to alternative packages.

Thanks, Blue

Python is a bit above my skill set, but I will still try to load and walk through that anyway. What TPB did works, and although I never could have written that code, I understand SQL enough to use and modify it.

And thanks again guys. Automating this task with 5 Fleets of cabs will save a ton of work.

To HeadSet


@HeadSet - you still need my solution, or have you got a solution?

richwicks says

To HeadSet


@HeadSet - you still need my solution, or have you got a solution?

I am currently actively using TPB' s SQL solution, and it works great. I am also looking over what Blue produced. Therefore I no longer need a solution, but I appreciate that you would have put forth the effort.

HeadSet says

I am currently actively using TPB' s SQL solution, and it works great. I am also looking over what Blue produced. Therefore I no longer need a solution, but I appreciate that you would have put forth the effort.

No problem. I have the solution basically done, but it requires multiple steps. I took your spreadsheet, turned it into a csv file, used perl to convert the dates to the number of days from the beginning of the year (1-365, sometimes 366 if it's a leap year) made an array for each cab that was filled info for the days it was leased out, and then stopped at converting it back to months/days - but that's what I had left to do, but it seemed you had a solution and it's frankly less messy than my solution.

It was limited to working on 1 year at a time.

Fun side project. Just wanted to make certain you were set, because it's not much work to complete it - but it's work to use it.

richwicks says

It was limited to working on 1 year at a time.

Fun side project. Just wanted to make certain you were set, because it's not much work to complete it - but it's work to use it.

Yes it was a fun exercise I would recommend anyone giving it a go, just to sharpen their skills. Date manipulation is always a mad skill, you don't need until you need it.

The drawback of the solution I created. It creates a list of past dates going three years back. Where as before I was getting the count of days in the range provided in the dates, and then had to create dates out of that range. I would have had to account for Leap year and other special considerations. So that script would have grown as bugs became apparent.
The second solution just generated a dynamic list of dates, so I didn't have to worry about it. As it allowed the SQL built in GetDate figure out those dates.
So if someone needs this script for booking a range of dates in the future, then they should go with my first solution.

@Tenpoundbass

Just a quick question on that great SQL code you did for me almost 2 years ago:



It seems that the "where datetable.Date between LeaseStartDate and LeaseEndDate" does not include the LeaseEndDate itself. The SQL documentation says that a BETWEEN statement is inclusive of the last date, so I am not sure here. Maybe there is something else I am not seeing.

I really appreciate what you have done so far, and if you want to help here.

@Tenpoundbass

I got it sorted. Updated that WHERE line I pointed at using datepart function. Thanks again for writing that original code.

In your original code line (select DATEADD(day,-(a.a + (10 b.a) + (100 c.a)),getdate()) AS Date , what is the a.a and b.a and c.a? Is that a way to set a reference start date?