I am trying to sample data based on operational hours. For example, Serial C was introduced in 2014-01-01 and it was was failed in 2014-01-03. Serial B, and D was never failed. I want to compute the operational hours as follows:
I was able to do it using PySpark as follows:
PySpark version:
drive_spans = spark.sql("""
select
serial_number,
max(date) as retired_date,
min(date) as launched_date,
count(date) as observed_days,
min(case when failure=1 then date end) as failed_date,
max(smart_187_raw) as max_hours,
min(case when failure=1 then smart_187_raw end) as failed_hours,
max(failure) as failure
from df
group by serial_number
""").cache()
drive_spans.count()
dfsurv = spark.sql("""
select
drive_spans.*,
datediff(coalesce(failed_date, retired_date), launched_date) as duration,
min(launched_date) over (partition by serial_number) as model_introduced
from drive_spans
""")
However, I couldn't use the same PySpark functionality in Pandas. I appreciate your suggestions for the following dataframe. I wanna get the above table for this data. Thanks!
import pandas as pd
import numpy as np
import datetime
from datetime import date, timedelta
df = pd.read_csv('https://gist.githubusercontent.com/JishanAhmed2019/e464ca4da5c871428ca9ed9264467aa0/raw/da3921c1953fefbc66dddc3ce238dac53142dba8/failure.csv',sep='\t')
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(by="date")
gb = df.groupby(['serial_number'])
gb.agg(
first_date=('date', np.min),
last_date=('date', np.max)
).reset_index()