2

I have a dataframe as show below,

  df =
   index                value
2014-05-21 10:00:00      13.0
2014-05-21 10:30:00       8.0
2014-05-21 11:00:00       9.0
2014-05-21 11:30:00       7.0
2014-05-21 12:00:00       2.0
....

how can I add a new value 2 in the beginning, and it would be

    df =
   index                value
2014-05-21 09:30:00       2.0   <- new value with new index automatically
2014-05-21 10:00:00      13.0     calculated ( 10 o'clock - 30min(timestep))
2014-05-21 10:30:00       8.0
2014-05-21 11:00:00       9.0
2014-05-21 11:30:00       7.0
2014-05-21 12:00:00       2.0

and 

type(df.index) = pandas.core.indexes.datetimes.DatetimeIndex

I would like to add the value in the first index and the datetime would be calculate automatically by the timestep (in this case is 30 min), is there any better way to do it ?

Thanks in advance !

Chi
  • 187
  • 3
  • 14

2 Answers2

4

Option 1

df.loc[df.index[0] - pd.offsets.Minute(30), 'value'] = 2
df = df.sort_index()

df

                     value
index                     
2014-05-21 09:30:00    2.0
2014-05-21 10:00:00   13.0
2014-05-21 10:30:00    8.0
2014-05-21 11:00:00    9.0
2014-05-21 11:30:00    7.0
2014-05-21 12:00:00    2.0

Option 2

Set the frequency of the index so you can decrement naturally.
This is from an answer by @root HERE

from pandas.tseries.frequencies import to_offset

df.index.freq = to_offset(df.index.inferred_freq)
df.combine_first(pd.DataFrame(dict(value=[2]), [df.index[0] - 1]))

                     value
index                     
2014-05-21 09:30:00    2.0
2014-05-21 10:00:00   13.0
2014-05-21 10:30:00    8.0
2014-05-21 11:00:00    9.0
2014-05-21 11:30:00    7.0
2014-05-21 12:00:00    2.0
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • because I have some other dataframe with different timesteps (15mins, 60mins ...etw), is it possible to do it without write for example "Minute(30)" manually but calculated automatically? – Chi Apr 12 '18 at 22:39
  • you can calculate the time step with `df.index[1] - df.index[0]` – piRSquared Apr 12 '18 at 22:40
  • might be safer to use something like `df.index.freq = df.index.inferred_freq` – root Apr 12 '18 at 22:45
  • @root remember that question I asked (long time ago). Need to convert that string to a time delta. [>>>found it<<<](https://stackoverflow.com/a/40223868/2336654) – piRSquared Apr 12 '18 at 22:47
  • maybe I'm missing something, but `df.index[0] - 1` gives me the expected result after setting freq as specified in my previous comment. – root Apr 12 '18 at 22:50
  • Hmm, maybe they changed it. As of the time I asked that question, it would not work. Testing now. – piRSquared Apr 12 '18 at 22:51
  • @root I get `AttributeError: 'str' object has no attribute 'freqstr'` which was the same error I was getting when I asked that question. So, to use your suggestion I have to refer to **your** answer to that question `from pandas.tseries.frequencies import to_offset; df.index.freq = to_offset(df.index.inferred_freq)` – piRSquared Apr 12 '18 at 22:54
  • @piRSquared: are you on 0.22.0? Working for me on 0.22.0+. – root Apr 12 '18 at 22:55
  • Yes... dbl checking, yes. `0.22.0`. `df.index.inferred_freq` is the string `'30T'`. What do you get with `type(df.index.inferred_freq)` – piRSquared Apr 12 '18 at 22:56
  • Wow...This is very strange. Calling`df.index` after setting the freq as a string gives me the `AttributeError`, but `df.index[0]` works fine and gives a `Timestamp` with the freq set appropriately. Must be a bug. – root Apr 12 '18 at 23:01
1

You can also using reindex with index append

df.reindex(pd.Index(['2014-05-21 09:30:00']).append(df.index),fill_value=2)
Out[116]: 
                     value
2014-05-21 09:30:00    2.0
2014-05-21 10:00:00   13.0
2014-05-21 10:30:00    8.0
2014-05-21 11:00:00    9.0
2014-05-21 11:30:00    7.0
2014-05-21 12:00:00    2.0
BENY
  • 317,841
  • 20
  • 164
  • 234