如何对每个组的datetime应用升序排序

Question

我有一个数据集要根据'user_id'和'contest_id'进行分组，其中，我必须根据日期和时间按升序对每个参加比赛的用户进行排序。你知道吗

我尝试过首先根据contest_id和user handle对数据进行分组，然后在将datetime列转换为“to\u datetime”后，尝试使用sort\u值对日期进行升序排序

当我试图保存代码时，它给出了一个错误 ''

      Excel doesn't support timezones in datetimes. Set the tzinfo in the 

       datetime/time object to None or use the 'remove_timezone' Workbook() 

       option

''

       dftotal.groupby(["contestID", "userHandle"])

       dftotal["registerDateTime"]=pd.to_datetime(dftotal.registerDateTime)

       dftotal["RegistrationDateTime"] = dftotal["registerDateTime"]

       dftotal["submitDateTime"] = pd.to_datetime(dftotal.submitDateTime)

       dftotal["SubmissionDateTime"] = dftotal["submitDateTime"]

       dftotal = dftotal.sort_values(by=['RegistrationDateTime'])

数据是

        contest_id user_id  registration    submission          score 
        1234       abc     2012-01-09       2012-01-09           90
                          21:51:00+00:00   22:51:00+00:00 


        4489      pabc     2013-01-09     2013-01-09             39
                         21:51:00+00:00   22:55:00+00:00 

        1234     tiop      2012-01-09      2012-01-09            100
                        23:51:00+00:00   23:55:00+00:00 

        4489    pabceu    2013-01-09      2013-01-09              39
                        23:20:00+00:00   23:55:00+00:00  

预期为

        contest_id user_id   registration     submission             score 
        1234       abc      2012-01-09       2012-01-09              90
                         21:51:00+00:00   22:51:00+00:00 

        1234      tiop    2012-01-09       2012-01-09               100
                        23:51:00+00:00    23:55:00+00:00 

        4489      pabc    2013-01-09      2013-01-09                 39
                        21:51:00+00:00   22:55:00+00:00 

        4489     pabceu   2013-01-09     2013-01-09                  39
                        23:20:00+00:00  23:55:00+00:00

Answer 1

我终于可以复制和修复了。你知道吗

复制步骤

import pandas as pd
import io

t = '''contest_id  user_id  registration    submission          score 
        1234       abc     2012-01-09 21:51:00+00:00       2012-01-09 22:51:00+00:00           90
        4489      pabc     2013-01-09 21:51:00+00:00     2013-01-09 22:55:00+00:00             39
        1234     tiop      2012-01-09 23:51:00+00:00      2012-01-09 23:55:00+00:00            100
        4489    pabceu    2013-01-09 23:20:00+00:00      2013-01-09 23:55:00+00:00              39'''

dftotal=pd.read_csv(io.StringIO(t), sep=r'\s\s+', engine='python')

print(dftotal.to_string())

dftotal['registration'] = pd.to_datetime(dftotal.registration, utc=True)
dftotal['submission'] = pd.to_datetime(dftotal.submission, utc=True)

print(dftotal.to_string())
dftotal.to_excel('contest_new.xlsx')

其中显示：

   contest_id user_id               registration                 submission  score
0        1234     abc  2012-01-09 21:51:00+00:00  2012-01-09 22:51:00+00:00     90
1        4489    pabc  2013-01-09 21:51:00+00:00  2013-01-09 22:55:00+00:00     39
2        1234    tiop  2012-01-09 23:51:00+00:00  2012-01-09 23:55:00+00:00    100
3        4489  pabceu  2013-01-09 23:20:00+00:00  2013-01-09 23:55:00+00:00     39
   contest_id user_id              registration                submission  score
0        1234     abc 2012-01-09 21:51:00+00:00 2012-01-09 22:51:00+00:00     90
2        1234    tiop 2012-01-09 23:51:00+00:00 2012-01-09 23:55:00+00:00    100
1        4489    pabc 2013-01-09 21:51:00+00:00 2013-01-09 22:55:00+00:00     39
3        4489  pabceu 2013-01-09 23:20:00+00:00 2013-01-09 23:55:00+00:00     39

并提出：

TypeError: Excel doesn't support timezones in datetimes. Set the tzinfo in the datetime/time object to None or use the 'remove_timezone' Workbook() option

可能的修复方法：

1. 使用openpyxl：

   此错误由xlsxwriter后端引发。如果安装了openpyxl，那么要求使用该引擎就足够了：

   ...
   dftotal.to_excel('contest_new.xlsx', engine='openpyxl')
   

   它会自动删除tz信息并正确写入excel文件

2. 明确删除ts信息：

   时区信息可以用tz_localize(None)明确删除：

   ...
   dftotal['registration'] = pd.to_datetime(dftotal.registration).dt.tz_localize(None)
   dftotal['submission'] = pd.to_datetime(dftotal.submission).dt.tz_localize(None)
   dftotal = dftotal.sort_values(by=['registration'])
   print(dftotal.to_string())
   
   dftotal.to_excel('contest_new.xlsx')
   

   数据帧显示为：

      contest_id user_id        registration          submission  score
   0        1234     abc 2012-01-09 21:51:00 2012-01-09 22:51:00     90
   2        1234    tiop 2012-01-09 23:51:00 2012-01-09 23:55:00    100
   1        4489    pabc 2013-01-09 21:51:00 2013-01-09 22:55:00     39
   3        4489  pabceu 2013-01-09 23:20:00 2013-01-09 23:55:00     39
   

   默认的xlsxwriter引擎不会出错地写入。