CODEWITHSUNDEEP
pythonpandasdataframe
Annemie
Annemie

Reputation: 337

Python pandas shift dataframe with time index value

I am quite new with python and am struggling with the shift in pandas.

I am comparing data, but it needs to be aligned to compare it. To align the data, I only need to shift one of the data's index values.

Reference data:                        Data to be shifted:
                          acc                                   acc
index                                  index            
1480681219**96**0000000     1          1480681220**04**0000000    8
1480681220**00**0000000     2          1480681220**08**0000000    9    
1480681220**04**0000000     3          1480681220**12**0000000    7
1480681220**08**0000000     4          1480681220**16**0000000   10
1480681220**12**0000000     5          1480681220**20**0000000    6

(The bold editing option did not seem to work, but I wanted to highlight those parts of the indexes)

I would like to shift my data frame with amount of extra time given. Please note, the time is in nanoseconds. I realized that something like df.shift(2) shifts my data 2 places, but I would like to shift my data with -80000000 nanoseconds which in this case is 2 places:

Input:

                     acc
index                   
1480681220040000000    8
1480681220080000000    9
1480681220120000000    7
1480681220160000000   10
1480681220200000000    6

Desired output:

                      acc
index          
1480681219960000000     8
1480681220000000000     9          
1480681220040000000     7
1480681220080000000    10
1480681220120000000     6
1480681220160000000   NaN
1480681220200000000   NaN

This is a smaller scale of my code:

class device_data(object):
    def __init__(self):

        _index = [1480681220040000000,
                 1480681220080000000,
                 1480681220120000000,
                 1480681220160000000,
                 1480681220200000000]

        self.df = pd.DataFrame({'acc': [8, 9, 7, 10, 6], 'index': _index})
        self.df = self.df.set_index('index')

if __name__ == '__main__':
    extratime = np.int64(-40000000)

    session = dict()
    session[2] = {'testnumber': '401',
              'devicename': 'peanut'}
    session[2]['data_in_device_class'] = device_data()

    print session[2]['data_in_device_class'].df

    if hasattr(session[2]['data_in_device_class'], 'df'):
        session[2]['data_in_device_class'].df = session[2]['data_in_device_class'].df.shift(int(round(extratime)))
    else:
        pass

    print session[2]['data_in_device_class'].df

When I ran the original code, it gave me this error: OverflowError: Python int too large to convert to C long

I used extratime = np.int64(extratime) to solve the problem. I notice that with the scaled down version of my code, that it is not really needed.

My question still stands as how I could use shift to move my index with a value amount and not with the amount of places it needs to move?

Thank you

Upvotes: 1

Views: 1715

Answers (2)

EdChum
EdChum

Reputation: 393893

First you want to shift your index by the desired amount, and then reindex, to make things easier I take a copy here, shift the index, and we reindex on the union of the shifted index and the original index to introduce NaN rows:

In [232]:
df1 = df.copy()
df1.index -= 80000000
df1.reindex(df1.index.union(df.index))

Out[232]:
                      acc
index                    
1480681219960000000   8.0
1480681220000000000   9.0
1480681220040000000   7.0
1480681220080000000  10.0
1480681220120000000   6.0
1480681220160000000   NaN
1480681220200000000   NaN

Upvotes: 3

piRSquared
piRSquared

Reputation: 294218

IIUC:
You can just reassign your index with itself added to extra time.

Consider the dataframe df as an example

df = pd.DataFrame(np.arange(100).reshape(5, -1))
df

enter image description here

I can "shift" the entire dataframe down like this

df.index = df.index + 5
df

enter image description here

Let me know if this is on the mark. Otherwise, I'll delete it.

Upvotes: 3

Related Questions