7
\$\begingroup\$

I have a PANDAS DataFrame that contains sensor data that is recorded every hour (sample included below). It is important to note that every hour is not necessarily in the dataframe, as sometimes the sensor is down and nothing is recorded. This data is going to be used as input into a predictive model, but before that happens, I need to first figure out a way to following:

  • If failure switches from 0 to 1, keep only the first row where failure = 1, and remove rows until failure switches back to 0.
  • Create a new variable that shifts the time when the failure happens up by 2 hours and remove records from when new failure indicator starts until the period until the original failure ends.

In order to solve this problem, below are the steps I have taken. However, I realize that it is probably not ideal and could use a lot of improvement.

import pandas as pd

# load into Pandas
df = pd.DataFrame(data)

# convert index to datetime value
df.index = pd.to_datetime(df.index)

# find changes in failure
df['failure_change'] = df.Failure.diff()

# find datetimes where multiple failures happen in a row and drop
df = df.drop(df[(df['Failure'] == 1) & (df['failure_change'] != 1.0)].index)

# find index values for when the failure happened
fail_dt = df[df['Failure'] == 1].index

# subtract 2 hours from the datetime of each failure
new_fail_dts = set()
for dt in fail_dt:
    new_fail_dts.add((dt - pd.to_timedelta(2, unit='h'), dt))

# go through df and create new failure indicator if datetime is in range leading up to failures as identified above
df['new_fail'] = 0
for i in df.index:
    for dt in new_fail_dts:
        if dt[0] <= i <= dt[1]:
            df.set_value(i, 'new_fail', 1)

# look for when new_fail changes and only keep first one
df['new_fail_change'] = df.new_fail.diff()

# find datetimes where multiple new failures happen in a row and keep first
df = df.drop(df[(df['new_fail'] == 1) & (df['new_fail_change'] == 0)].index)

Example Data in Current Format

+----------------------+----------+------------+
|                      | Failure  |   Speed    |
+----------------------+----------+------------+
| 2015-01-01 00:00:00  |       0  | 0.000000   |
| 2015-01-01 01:00:00  |       0  | 63.094019  |
| 2015-01-01 02:00:00  |       1  | 90.006264  |
| 2015-01-01 03:00:00  |       0  | 42.412872  |
| 2015-01-01 04:00:00  |       0  | 0.000000   |
| 2015-01-01 05:00:00  |       0  | 0.000000   |
| 2015-01-01 06:00:00  |       0  | 81.793235  |
| 2015-01-01 07:00:00  |       0  | 56.533471  |
| 2015-01-01 08:00:00  |       0  | 152.326947 |
| 2015-01-01 09:00:00  |       0  | 238.293261 |
| 2015-01-01 10:00:00  |       1  | 1.220514   |
| 2015-01-01 11:00:00  |       1  | 17.038855  |
| 2015-01-01 12:00:00  |       1  | 13.485625  |
| 2015-01-01 13:00:00  |       0  | 69.488021  |
| 2015-01-01 14:00:00  |       0  | 0.000000   |
| 2015-01-01 15:00:00  |       0  | 84.858909  |
| 2015-01-01 16:00:00  |       0  | 20.160277  |
| 2015-01-01 17:00:00  |       0  | 0.000000   |
| 2015-01-01 18:00:00  |       0  | 0.000000   |
| 2015-01-01 19:00:00  |       0  | 90.718714  |
| 2015-01-01 20:00:00  |       0  | 164.629853 |
| 2015-01-01 21:00:00  |       0  | 0.000000   |
| 2015-01-01 22:00:00  |       1  | 82.629209  |
| 2015-01-01 23:00:00  |       1  | 24.913644  |
+----------------------+----------+------------+

Data Stored as Dict

data = {'Failure': {('2015-01-01 00:00:00'): 0,
                    ('2015-01-01 01:00:00'): 0,
                    ('2015-01-01 02:00:00'): 1,
                    ('2015-01-01 03:00:00'): 0,
                    ('2015-01-01 04:00:00'): 0,
                    ('2015-01-01 05:00:00'): 0,
                    ('2015-01-01 06:00:00'): 0,
                    ('2015-01-01 07:00:00'): 0,
                    ('2015-01-01 08:00:00'): 0,
                    ('2015-01-01 09:00:00'): 0,
                    ('2015-01-01 10:00:00'): 1,
                    ('2015-01-01 11:00:00'): 1,
                    ('2015-01-01 12:00:00'): 1,
                    ('2015-01-01 13:00:00'): 0,
                    ('2015-01-01 14:00:00'): 0,
                    ('2015-01-01 15:00:00'): 0,
                    ('2015-01-01 16:00:00'): 0,
                    ('2015-01-01 17:00:00'): 0,
                    ('2015-01-01 18:00:00'): 0,
                    ('2015-01-01 19:00:00'): 0,
                    ('2015-01-01 20:00:00'): 0,
                    ('2015-01-01 21:00:00'): 0,
                    ('2015-01-01 22:00:00'): 1,
                    ('2015-01-01 23:00:00'): 1},
          'Speed': {('2015-01-01 00:00:00'): 0.0,
                    ('2015-01-01 01:00:00'): 63.094018515337844,
                    ('2015-01-01 02:00:00'): 90.006264149818463,
                    ('2015-01-01 03:00:00'): 42.412872151481686,
                    ('2015-01-01 04:00:00'): 0.0,
                    ('2015-01-01 05:00:00'): 0.0,
                    ('2015-01-01 06:00:00'): 81.7932352541048,
                    ('2015-01-01 07:00:00'): 56.533470911782281,
                    ('2015-01-01 08:00:00'): 152.32694722397184,
                    ('2015-01-01 09:00:00'): 238.29326083823594,
                    ('2015-01-01 10:00:00'): 1.220514306517468,
                    ('2015-01-01 11:00:00'): 17.038855027411945,
                    ('2015-01-01 12:00:00'): 13.485624530051169,
                    ('2015-01-01 13:00:00'): 69.488020963841421,
                    ('2015-01-01 14:00:00'): 0.0,
                    ('2015-01-01 15:00:00'): 84.858909271558645,
                    ('2015-01-01 16:00:00'): 20.160277319749248,
                    ('2015-01-01 17:00:00'): 0.0,
                    ('2015-01-01 18:00:00'): 0.0,
                    ('2015-01-01 19:00:00'): 90.718713931973625,
                    ('2015-01-01 20:00:00'): 164.62985302109433,
                    ('2015-01-01 21:00:00'): 0.0,
                    ('2015-01-01 22:00:00'): 82.629209162962155,
                    ('2015-01-01 23:00:00'): 24.913643956122016}}

# load into pandas
df = pd.DataFrame(data)
\$\endgroup\$
3
\$\begingroup\$

Here are my recommendations:

  • Use the right dtype for the given data to make comparisons easier
  • Explore the methods available for the DataFrame, and DateTimeIndex objects and useful top-level functions applicable for the relevant objects.
  • list/set/dict/generator comprehensions are nice
  • Try to think of a DataFrame as a database or its columns like vectors. Iterating over one is pretty much a worst-case scenario

Note that your index is a DatetimeIndex which can be individually accessed to yield Timestamp objects.

Here's my take at your code:

import pandas as pd
df = pd.read_csv(data)

df.index = pd.to_datetime(df.index)
df['Failure'] = df['Failure'].astype(bool)
# Drop failures that have failures immediately before
df.drop(df[df['Failure'] & df['Failure'].shift()].index, inplace=True)
# Generate preceding new failure dates from remaining failures
fail_ranges = (pd.date_range(end=date, periods=2, freq='H')
               for date in df[df['Failure']].index)
# Flatten out the list of DateTimeIndex into a set of timestamps
bad_dates = (datetime for daterange in fail_ranges
             for datetime in daterange)
# Drop the generated bad dates that exist in the dataframe
df.drop(df.index.intersection(bad_dates), inplace=True)

Now that Failure is of dtype bool, you no longer need to check whether failure is equal to 1 or 1.0, depending on whether you've shifted (through diff) and added a NaN value.

Since you are already using the same name to refer to new data, might as well use inplace=True where possible to get a little speedup.

You can use pd.date_range to generate a range of Timestamps instead of your for loop. Since the bad timestamps are already your dataframe labels, you can use them to drop the unwanted rows in the DataFrame. Index objects have set notation methods available to use.

I did assume that you didn't need the new_fail column. If you do need that column, you could do something like:

    ...
    # Generate preceding new failure dates from remaining failures
    fail_ranges = (pd.date_range(end=date, periods=3, freq='H')
                   for date in df[df['Failure']].index)
    # Flatten out the list of DateTimeIndex into a set of timestamps
    bad_dates = (datetime for daterange in fail_ranges
                 for datetime in daterange)
    df['new_fail'] = df.index.isin(bad_dates)
    # Drop the determied bad dates that have bad dates immediately before
    df.drop(df[df['new_fail'] & df['new_fail'].shift()].index, inplace=True)

Here we use the isin Index method to do a subset check. Shifting is really useful for checking on adjacent data. Essentially for all the shifts we compare the n data point to the n-1 data point by shifting the data down one index.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.