Formating a calendar table to a datetime dataframe

user_13100513 0
Viewed 146

I have a calendar data in the following format:

        df = pd.read_csv('2021.txt', sep="  ")
        df.head()

enter image description here

I'd like to have it as:

   Date       y
 2021-01-01 17:26
 2021-01-02 17:27
 2021-01-03 17:28
 2021-01-04 17:28
      ...
 2021-12-31 17:25

I've searched and found no similar questions. I'm trying to provide a minimal example but don't know where to start. I know I have to use pandas.to_datetime function but I don't even know how to apply it in this case because everything is separated.

pandas timestamp time datetime python
1 Answers

Use DataFrame.melt with to_datetime and errros='coerce' for convert wrong datetimes like 2021-02-30 to missing values and then remove this rows by DataFrame.dropna:

df1 = df.melt('Day', var_name='Date', value_name='y')
df1['Date'] = pd.to_datetime('2021'  + df1['Date'] + df1.pop('Day').astype(str), 
                             format='%Y%b%d', errors='coerce')
df1 = df1.dropna(subset=['Date'])
print (df1)  
         Date      y
0  2021-01-01  17:28
1  2021-01-02  17:27
2  2021-01-03  17:28
3  2021-01-04  17:28
4  2021-01-05  17:29
..        ...    ...
67 2021-12-02  17:15
68 2021-12-03  17:15
69 2021-12-04  17:15
70 2021-12-05  17:15
71 2021-12-06  17:15

[72 rows x 2 columns]
user_29010020
Related