Splitting columns and reformat date using pandas

I have an object, slist that I need to split, reformat the date, and export as a tab delimited file. For the splitting I think I’m tripping up understanding the first row? Here is slist:

I’ve tried the following:

df = pd.DataFrame(data=slist) 
newdf['datetime','values'] = df['node_21_Depth_above_invert'].astype(str).str.split('  ',expand=True)

Which gives me something like this:

I’ve spent a ton of time trying to figure this out and I know there are a lot of other pandas questions about column splitting, but I’ve hit a wall and any insight would be helpful. Thanks!

Answer

As you now have the datetime as row index, you can make it a data column by .reset_index() and then rename the columns, as follows:

newdf = df.reset_index()
newdf.columns = ['datetime','values']

---

Test Data Preparation

slist = {'node_21_Depth_above_invert': {pd.Timestamp('1998-01-01 01:00:00'): 1.0, pd.Timestamp('1998-01-01 02:00:00'): 1.519419550895691, pd.Timestamp('1998-01-01 03:00:00'): 2.0, pd.Timestamp('1998-01-01 04:00:00'): 2.0, pd.Timestamp('1998-01-01 05:00:00'): 2.0}}

df = pd.DataFrame(data=slist)

print(df)

                     node_21_Depth_above_invert
1998-01-01 01:00:00                     1.00000
1998-01-01 02:00:00                     1.51942
1998-01-01 03:00:00                     2.00000
1998-01-01 04:00:00                     2.00000
1998-01-01 05:00:00                     2.00000

Run New Codes

newdf = df.reset_index()
newdf.columns = ['datetime','values']

Result:

print(newdf)

             datetime   values
0 1998-01-01 01:00:00  1.00000
1 1998-01-01 02:00:00  1.51942
2 1998-01-01 03:00:00  2.00000
3 1998-01-01 04:00:00  2.00000
4 1998-01-01 05:00:00  2.00000