Parse date time in nested Pandas data frame

Question

I am struggling to parse the date time in Pandas. Here is my short example:

df.iloc[:10,10:]
Out[45]: 
                                 response_date         revision scheduleClosedAt  scheduleEventIndex scheduleId scheduleOpenedAt
0  {u'$date': u'2012-01-10T11:00:00.000+0000'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
1  {u'$date': u'2012-01-19T13:00:00.000+0000'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
2  {u'$date': u'2011-06-15T09:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
3  {u'$date': u'2011-06-22T00:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
4  {u'$date': u'2011-06-30T09:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
5  {u'$date': u'2011-07-05T00:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
6  {u'$date': u'2011-07-14T10:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
7  {u'$date': u'2011-07-20T09:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
8  {u'$date': u'2011-07-26T00:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN
9  {u'$date': u'2011-08-02T00:00:00.000+0100'}  {u'Measure': 1}              NaN                 NaN        NaN              NaN

I need to get rid of the nested column 'response_date' and convert it into normal timedate, while keeping the column name 'response_date'/

I tried:

>> df_respons = df.response_date.apply(pd.Series)
>> df_new_response = pd.to_datetime(df_respons)

but got the error:

ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day,month,year] is missing

Any neat way of processing the nested datetime into nice looking columns?

EDIT

How to ignore missing values?

43025    {u'$date': u'2015-11-18T10:35:00.000+0000'}
43026    {u'$date': u'2015-11-18T14:23:00.000+0000'}
43027    {u'$date': u'2015-11-18T14:23:00.000+0000'}
43028    {u'$date': u'2015-11-18T15:20:00.000+0000'}
43029    {u'$date': u'2015-11-18T15:20:00.000+0000'}
43030                                            NaN
43031                                            NaN
43032    {u'$date': u'2015-11-19T08:00:00.000+0000'}
43033    {u'$date': u'2015-11-19T08:00:00.000+0000'}
43034    {u'$date': u'2015-11-24T08:00:00.000+0000'}

that give a new '0' column:

        0                 response_date
43027 NaN  2015-11-18T14:23:00.000+0000
43028 NaN  2015-11-18T15:20:00.000+0000
43029 NaN  2015-11-18T15:20:00.000+0000
43030 NaN                           NaN
43031 NaN                           NaN
43032 NaN  2015-11-19T08:00:00.000+0000
43033 NaN  2015-11-19T08:00:00.000+0000
43034 NaN  2015-11-24T08:00:00.000+0000

Answer 1

You can use combine_first or fillna for replace NaN to empty dict and then is possible use DataFrame constructor with values for convert to numpy array and then tolist:

d = {'$date':'response_date'}
s = pd.Series([{}], index=df.index)
df = pd.DataFrame(df['0'].combine_first(s).values.tolist()).rename(columns=d)
#alternatively
#df = pd.DataFrame(df['0'].fillna(s).values.tolist()).rename(columns=d)
df['response_date'] = pd.to_datetime(df['response_date'])
print (df)
        response_date
0 2015-11-18 10:35:00
1 2015-11-18 14:23:00
2 2015-11-18 14:23:00
3 2015-11-18 15:20:00
4 2015-11-18 15:20:00
5                 NaT
6                 NaT
7 2015-11-19 08:00:00
8 2015-11-19 08:00:00
9 2015-11-24 08:00:00

Another solution with map:

df['response_date'] = \
pd.to_datetime(df['response_date'].map(lambda x: x['$date'] if type(x) == dict else x))
print (df)
            response_date
43025 2015-11-18 10:35:00
43026 2015-11-18 14:23:00
43027 2015-11-18 14:23:00
43028 2015-11-18 15:20:00
43029 2015-11-18 15:20:00
43030                 NaT
43031                 NaT
43032 2015-11-19 08:00:00
43033 2015-11-19 08:00:00
43034 2015-11-24 08:00:00

Answer 2

It sounds like you want something like df.apply(lambda row: pd.to_datetime(row['response_date']['$date']), axis=1);

In [41]: df
Out[41]:
                               response_date
0  {'$date': '2011-06-15T09:00:00.000+0100'}

In [42]: df['response_date'] = df.apply(lambda row: pd.to_datetime(row['response_date']['$date']), axis=1)

In [43]: df
Out[43]:
        response_date
0 2011-06-15 08:00:00