Python pandas shift dataframe with time index value

Question

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

If you're planning to shift the entire df, you'd be better off shifting the index and then reindex or np.roll(df.index, some_value) and use the returning array to reindex — EdChum
– EdChum, Commented Jan 13, 2017 at 9:55
When you say shift up or down, are you expecting NaN at top or bottom after shifting or you want a truncated df? — EdChum
– EdChum, Commented Jan 13, 2017 at 10:11
@EdChum: I have realized that I have asked the wrong question, as both your answers were helpful, it was not what what I wanted to accomplish. I feel embarrassed to say I need to rewrite the whole question. I realized, I actually want to use shift to only shift the acc, but I would like to shift the value with the 'exstra time'. I will edit my question so that it also indicates what I am looking for — Annemie
– Annemie, Commented Jan 13, 2017 at 12:01
It generally helps to post the desired result to avoid ambiguity — EdChum
– EdChum, Commented Jan 13, 2017 at 12:03
@piRSquared: I have realized that I have asked the wrong question, as both your answers were helpful, it was not what what I wanted to accomplish. I feel embarrassed to say I need to rewrite the whole question. I realized, I actually want to use shift to only shift the acc, but I would like to shift the value with the 'exstra time'. I will edit my question so that it also indicates what I am looking for — Annemie
– Annemie, Commented Jan 13, 2017 at 12:04

EdChum · Accepted Answer · 2017-01-13 13:06:03Z

3

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

edited Jan 13, 2017 at 13:06

answered Jan 13, 2017 at 10:07

EdChum

397k204 gold badges836 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

piRSquared Over a year ago

Am I off base here?

EdChum Over a year ago

@piRSquared I actually don't fully understand if the OP simply wants to add some scalar value to the index like in your answer, shift the df up/down with/without NaN so I commented to the OP, possibly your answer is what they really want

piRSquared Over a year ago

I guess we'll see :-)

Annemie Over a year ago

@EdChum I edited the question now, and thus your answer no longer fits the question. Would you maybe mind removing it? Maybe I should have just deleted this whole question and started anew...

piRSquared · Accepted Answer · 2017-01-13 10:16:11Z

3

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

I can "shift" the entire dataframe down like this

df.index = df.index + 5
df

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

edited Jan 13, 2017 at 10:16

answered Jan 13, 2017 at 10:06

piRSquared

296k68 gold badges509 silver badges654 bronze badges

3 Comments

Annemie Over a year ago

I have realized that I have asked the wrong question, as both your answers were helpful, it was not what what I wanted to accomplish. I feel embarrassed to say I needed to rewrite the whole question. I realized, I actually want to use shift to only shift the acc, but I would like to shift the value with the 'extra time'. I edited the question now, and thus your answer no longer fits the question. Would you maybe mind removing it? Maybe I should have just deleted this whole question and started anew...

piRSquared Over a year ago

@Annemie27 I'd copy what you have and start a new question. Leave the old one here. You can, or I will roll back these edits after you've started new question. This way, even if slightly confusing, the two answers might help others who search and find your question. And you also get another question

Annemie Over a year ago

@ piRSquared I think I'll have to rewrite the old question and put a link to that question here, then you can maybe post this answer there again? Because my new edited question was answered.

Collectives™ on Stack Overflow

Python pandas shift dataframe with time index value

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related