How do I group by date with Pandas？

Question

I made a game and got the players’s data like this:

StartTime                    Id    Rank Score
2018-04-24 08:46:35.684000    aaa    1    280
2018-04-24 23:54:47.742000    bbb    2    176
2018-04-25 15:28:36.050000    ccc    1    223
2018-04-25 00:13:00.120000    aaa    4    79
2018-04-26 04:59:36.464000    ddd    1    346
2018-04-26 06:01:17.728000    fff    2    157
2018-04-27 04:57:37.701000    ggg    4    78

but I want to group it by day, just like this：

Date    2018/4/24    2018/4/25    2018/4/26    2018/4/27
ID        aaa            ccc          ddd            ggg
          bbb            aaa          fff            NaN

how do I group by date with Pandas？

Answer 1

You can use cumcount to align index by group, then concat to concatenate series.

# normalize to zero out time
df['StartTime'] = pd.to_datetime(df['StartTime']).dt.normalize()

# get unique days and make index count by group
cols = df['StartTime'].unique()
df.index = df.groupby('StartTime').cumcount()

# concatenate list comprehension of series
res = pd.concat([df.loc[df['StartTime'] == i, 'Id'] for i in cols], axis=1)
res.columns = cols

print(res)

  2018-04-24 2018-04-25 2018-04-26 2018-04-27
0        aaa        ccc        ddd        ggg
1        bbb        aaa        fff        NaN

Performance

For smaller dataframes, use @ScottBoston's more succinct solution. For larger dataframes, concat seems to scale better than unstack:

def scott(df):
    df['StartTime'] = pd.to_datetime(df['StartTime'])
    return df.set_index([df['StartTime'].dt.floor('D'),
                  df.groupby(df['StartTime'].dt.floor('D')).cumcount()])['Id'].unstack(0)

def jpp(df):
    df['StartTime'] = pd.to_datetime(df['StartTime']).dt.normalize()
    df.index = df.groupby('StartTime').cumcount()
    res = pd.concat([df.loc[df['StartTime'] == i, 'Id'] for i in df['StartTime'].unique()], axis=1)
    res.columns = cols
    return res

df2 = pd.concat([df]*100000)

%timeit scott(df2)  # 1 loop, best of 3: 681 ms per loop
%timeit jpp(df2)    # 1 loop, best of 3: 271 ms per loop

Answer 2

Use set_index and cumcount:

df.set_index([df['StartTime'].dt.floor('D'),
              df.groupby(df['StartTime'].dt.floor('D')).cumcount()])['Id'].unstack(0)

OUtput:

StartTime 2018-04-24 2018-04-25 2018-04-26 2018-04-27
0                aaa        ccc        ddd        ggg
1                bbb        aaa        fff        NaN

Answer 3

import pandas as pd

df = pd.DataFrame({'StartTime': ['2018-04-01 15:25:11', '2018-04-04 16:25:11', '2018-04-04 15:27:11'], 'Score': [10, 20, 30]})
print(df)

This yields

   Score            StartTime
0     10  2018-04-01 15:25:11
1     20  2018-04-04 16:25:11
2     30  2018-04-04 15:27:11

Now we create a new column based on the StartTime column, which contains only the date:

df['Date'] = df['StartTime'].apply(lambda x: x.split(' ')[0])
print(df)

Output:

   Score            StartTime        Date
0     10  2018-04-01 15:25:11  2018-04-01
1     20  2018-04-04 16:25:11  2018-04-04
2     30  2018-04-04 15:27:11  2018-04-04

We can now use the pd.DataFrame.groupby method to group the rows by the values of the new Datecolumn. In the example below, I first group the columns and then iterate over them to print the name (the value of the Date column of this group) and the mean score achieved:

for name, group in df.groupby('Date'):
    print(name)
    print(group)
    print(group['Score'].mean())

Gives:

2018-04-01
   Score            StartTime        Date
0     10  2018-04-01 15:25:11  2018-04-01
10.0
2018-04-04
   Score            StartTime        Date
1     20  2018-04-04 16:25:11  2018-04-04
2     30  2018-04-04 15:27:11  2018-04-04
25.0

Edit: Since you initially did not provide the dataframe data in table format, I leave it as an exercise to you to adapt the dataframe in my answer ;-)