# How to clean messy column and reshape data structure in pandas

I have a dataset that looks like this.

There are 122 columns of coordinates excluding the date (Time) column and 7000 rows.

What I want to do is split the lat and lon into two columns, and then reshape the data so that the lat and lon become the index, and the dates become the columns.

So the header would look like: lat -- lon -- date1 -- date2 -- date3...

I am new to Pandas and I have looked for solutions online but I can't find any solution to my issue. So far my progress is on splitting the lat and lon, now I'm stuck figuring out the next step - do I create two columns to store the lat and lon first, or do I reshape the data structure first?

• Quick recipe. Please start from df.transpose(), then make indexes to be the column. Finally, parse the column into two by using pd.DataFrame.apply() function. – kate-melnykova May 12 at 6:44
• Thanks, the transpose worked, but now the 'Time' and dates have become part of the dataset instead of columns. Not only that, the coordinates and time are sort of treated as the same thing (they are the index). I've tried reset_index, but all I get is an extra column called 'index', and in that column, the row starts with 'Time', followed by the coordinates. The dates continue to be the first row of data rather than column/header. I'm sorry, I'm really lost. – cwmwl May 13 at 1:53
• Hm... Could you please make date your index before applying the transpose? Then, after transpose, you will get the df whose columns are dates and whose row index coordinates. Then, make indexes to be the column and parse the column. – kate-melnykova May 13 at 18:04