Extract data based on condition

user_17387530 0
Viewed 602

I have the following dataset (sample) from which I have to extract dataframe based on a condition. The dataset consists of machines (over 1000), the downtime in hours, the status, and the downtime reason. There are many instances of each machine in the dataset (at least 1000). The condition for extraction is the dataframe must only contain those machines that have been active for the past 7 days consecutively (including the date). The dataset does not contain records for every day for each machine. Some days have been skipped. For those days, I'm not sure if the machine was active or not. I'm trying to do it on a rolling basis, for example, for the date in each row, I'm trying to check if that unique machine has been active for the past 7 days (pd.Timedelta(days = 6)). I think there is a neater and more organized way to achieve this but so far, I've not been able to come up with an algorithm. I'm pretty much a novice programmer in Python. Is there someway I can organize the machines and then check for each date. I would also like to be able to keep track of those machines for which records for some days have been skipped. I don't need the code in Python. I'd just greatly appreciate if someone could help me come up with an effective algorithm through which I can achieve the new dataframe.

Thank you very much in advance and happy holidays! :)

DATE MACHINE DT_IN_H MACHINE_STATUS DT_REASON
11/5/2021 710 0 ACTIVE In Service
8/1/2020 847 0 ACTIVE In Service
2/4/2020 1334 0 ACTIVE In Service
5/16/2020 855 24 ACTIVE Under mgmt
1/4/2019 669 0 ACTIVE In Service
7/24/2021 1831 0 ACTIVE In Service
10/19/2018 134 24 ACTIVE Under mgmt
8/18/2019 395 0 ACTIVE In Service
1/19/2019 499 24 ACTIVE Under mgmt
7/24/2020 2085 0 ACTIVE In Service
data-extraction pseudocode dataset algorithm python
1 Answers

Approach

Adapting method from How to group by date and find consecutive day count we proceed as follows

  1. Take Dataframe rows only where MACHINE_STATUS is ACTIVE
  2. convert DATE column from string to DATE
  3. Sort by DATE (ascending)
  4. Add a unique identifier for each group of ascending dates per MACHINE
  5. Filter to obtain only when group size >= 7

Code

df = pd.read_csv("https://pastebin.com/raw/L0GSYrjf")    # Read data from url
df = df[df.MACHINE_STATUS == "ACTIVE"]                   # 1 when machine is ACTIVE

df["DATE"] = pd.to_datetime(df.DATE)                     # 2 Convert data field from string to date
df = df.sort_values(by='DATE', ascending=True)           # 3 sort by date 
df['successive'] = df.groupby(["MACHINE"]).DATE.transform(func = lambda x: x.diff().dt.days.ne(1).cumsum()) # 4 add unique identifier

# 5 Filter keeping only groups of 7 or more active
df2 = df.groupby(["MACHINE", "successive"]).filter(lambda g: len(g) >= 7)

print(df2.groupby(['successive']).size()) 
# Output (shows only 1 group of length 1471, which means machine was active for 1471 successive days)
successive
1    1471
dtype: int64

print(df2.head(10))
# Output

       DATE  MACHINE  DT_IN_H MACHINE_STATUS   DT_REASON  successive
0 2018-01-01     1887      0.0         ACTIVE  In Service           1
1 2018-01-02     1887      0.0         ACTIVE  In Service           1
2 2018-01-03     1887      0.0         ACTIVE  In Service           1
3 2018-01-04     1887      0.0         ACTIVE  In Service           1
4 2018-01-05     1887      0.0         ACTIVE  In Service           1
5 2018-01-06     1887      0.0         ACTIVE  In Service           1
6 2018-01-07     1887      0.0         ACTIVE  In Service           1
7 2018-01-08     1887      0.0         ACTIVE  In Service           1
8 2018-01-09     1887      0.0         ACTIVE  In Service           1
9 2018-01-10     1887      0.0         ACTIVE  In Service           1

**Using DT_REASON **

df = pd.read_csv("https://pastebin.com/raw/L0GSYrjf")
df = df[df.DT_REASON == "In Service"]                   # 1 when machine is ACTIVE
df["DATE"] = pd.to_datetime(df.DATE)                    # 2 Convert data field from string to date
df = df.sort_values(by='DATE', ascending=True)          # 3 sort by date 
df['successive'] = df.groupby(["MACHINE"]).DATE.transform(func = lambda x: x.diff().dt.days.ne(1).cumsum()) # 4 add unique identifier

df2 = df.groupby(["MACHINE", "successive"]).filter(lambda g: len(g) >= 7)

    df2.head(10)
    # Output shows first 10 of first group
    DATE    MACHINE DT_IN_H MACHINE_STATUS  DT_REASON   successive
0   2018-01-01  1887    0.0 ACTIVE  In Service  1
1   2018-01-02  1887    0.0 ACTIVE  In Service  1
2   2018-01-03  1887    0.0 ACTIVE  In Service  1
3   2018-01-04  1887    0.0 ACTIVE  In Service  1
4   2018-01-05  1887    0.0 ACTIVE  In Service  1
5   2018-01-06  1887    0.0 ACTIVE  In Service  1
6   2018-01-07  1887    0.0 ACTIVE  In Service  1
7   2018-01-08  1887    0.0 ACTIVE  In Service  1
8   2018-01-09  1887    0.0 ACTIVE  In Service  1
9   2018-01-10  1887    0.0 ACTIVE  In Service  1

df2.groupby(['successive']).size()
# Shows there are groups
successive
1     20
3    113
4    151
5     58
6    386
7    437
8    292
dtype: int64
user_30660770
Related