How to avoid confusion between column and DatetimeIndex when adding column to Pandas dataframe

Question

I have a pandas DataFrame with column headers that are numerical strings and an index that is a DatetimeIndex. For example:

In:
df=pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]], index=pd.DatetimeIndex(['2019-01-01 00:00:00', '2019-01-01 00:05:00',
               '2019-01-01 00:10:00']), columns=['010000','010001','010002'])
df

Out:
                     010000  010001  010002
2019-01-01 00:00:00       1       2       3
2019-01-01 00:05:00       4       5       6
2019-01-01 00:10:00       7       8       9

I m successfully adding columns to the dataframe using, e.g.,

In:
df['010003'] = pd.Series([99,99,99], index= df.index)
df
Out: 
                     010000  010001  010002  010003
2019-01-01 00:00:00       1       2       3      99
2019-01-01 00:05:00       4       5       6      99
2019-01-01 00:10:00       7       8       9      99

BUT, if the column header could be mistaken for a date, Pandas treats it as an index element, tries to add a row instead of a column, and raises an exception:

In:
df['010119'] = pd.Series([99,99,99], index= df.index)
Out:
Traceback (most recent call last):
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 3325, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-16-1f55509f2987>", line 1, in <module>
    df['010119'] = pd.Series([99,99,99], index= df.index)
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\frame.py", line 3362, in __setitem__
    return self._setitem_slice(indexer, value)
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\frame.py", line 3374, in _setitem_slice
    self.loc._setitem_with_indexer(key, value)
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\indexing.py", line 656, in _setitem_with_indexer
    value=value)
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\internals\managers.py", line 510, in setitem
    return self.apply('setitem', **kwargs)
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\internals\managers.py", line 395, in apply
    applied = getattr(b, f)(**kwargs)
  File "C:\Users\...\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\internals\blocks.py", line 920, in setitem
    values[indexer] = value
ValueError: could not broadcast input array from shape (3) into shape (3,4)

To avoid this confusion, how should I rewrite the assignment to force Pandas to take the numerical string as a header for a new column?

score 3 · Accepted Answer · answered Nov 14 '19 at 01:19

3

We have specific function for insert the columns

df.insert(len(df.columns),column='010119',value=[99,99,99])
df
                     010000  010001  010002  010119
2019-01-01 00:00:00       1       2       3      99
2019-01-01 00:05:00       4       5       6      99
2019-01-01 00:10:00       7       8       9      99

answered Nov 14 '19 at 01:19

BENY

317,841
20
164
234

I was writing this solution:) – ansev Nov 14 '19 at 01:21
@ansev ah, I just by luck :-) LOL – BENY Nov 14 '19 at 01:22
Thanks! This works. It seems wrong that this should be necessary, though. Shouldn't pandas recognise `df[name]` as referring to a column? – doctorer Nov 14 '19 at 02:20
@doctorer that is more like a bug , I will open a issue in github , let us see what happen , I will update here – BENY Nov 14 '19 at 04:53
@WeNYoBen I thought it might be - thank you for keeping me updated! – doctorer Nov 14 '19 at 05:15
I'm accepting this answer as a work-around, but keen to know if the bug gets resolved! – doctorer Nov 24 '19 at 23:57

How to avoid confusion between column and DatetimeIndex when adding column to Pandas dataframe

1 Answers1