如何获得某些日期的最大日期的行? [英] How to get rows by max date with certain columns?

问题描述

当列I和II相同时，我想获取最新的列. 因此结果应该是. 1/30/2017胜过1/27/2017.

I want to get the latest column when columns I and II are the same. So the result should be. 1/30/2017 beats out 1/27/2017.

I I III                    IV
A X 1/30/2017  9:33:00 AM  some_data
A Y 1/30/2017  9:33:00 AM  some_data
A Z 1/30/2017  9:33:00 AM  some_data
A X 1/27/2017  4:53:00 PM  some_data
A Y 1/27/2017  4:53:00 PM  some_data
A Z 1/27/2017  4:53:00 PM  some_data
B X 1/30/2017  9:33:00 AM  some_data
B Y 1/30/2017  9:33:00 AM  some_data
B Z 1/30/2017  9:33:00 AM  some_data
B X 1/27/2017  4:53:00 PM  some_data
B Y 1/27/2017  4:53:00 PM  some_data
B Z 1/27/2017  4:53:00 PM  some_data

这是我想要的结果.

I I III                    IV
A X 1/30/2017  9:33:00 AM  some_data
A Y 1/30/2017  9:33:00 AM  some_data
A Z 1/30/2017  9:33:00 AM  some_data
B X 1/30/2017  9:33:00 AM  some_data
B Y 1/30/2017  9:33:00 AM  some_data
B Z 1/30/2017  9:33:00 AM  some_data

有人可以帮我弄清楚如何提取这些行吗?

Can someone help me figure out how to extract these rows?

推荐答案

您想要的是groupby()，transform()和max():

代码:

data = [
    ('I', 'II', 'III', 'IV'),
    ('A', 'X', '1/30/2017 9:33:00 AM', 'some_data'),
    ('A', 'Y', '1/30/2017 9:33:00 AM', 'some_data'),
    ('A', 'Z', '1/30/2017 9:33:00 AM', 'some_data'),
    ('A', 'X', '1/27/2017 4:53:00 PM', 'some_data'),
    ('A', 'Y', '1/27/2017 4:53:00 PM', 'some_data'),
    ('A', 'Z', '1/27/2017 4:53:00 PM', 'some_data'),
    ('B', 'X', '1/30/2017 9:33:00 AM', 'some_data'),
    ('B', 'Y', '1/30/2017 9:33:00 AM', 'some_data'),
    ('B', 'Z', '1/30/2017 9:33:00 AM', 'some_data'),
    ('B', 'X', '1/27/2017 4:53:00 PM', 'some_data'),
    ('B', 'Y', '1/27/2017 4:53:00 PM', 'some_data'),
    ('B', 'Z', '1/27/2017 4:53:00 PM', 'some_data'),
]

import pandas as pd
df = pd.DataFrame(data[1:], columns=data[0])
df['III'] = pd.to_datetime(df['III'])

# groupby first two columns, then get the maximum value in the third column
idx = df.groupby(['I', 'II'])['III'].transform(max) == df['III']

# use the index to fetch correct rows in dataframe
print(df[idx])

结果:

   I II                 III         IV
0  A  X 2017-01-30 09:33:00  some_data
1  A  Y 2017-01-30 09:33:00  some_data
2  A  Z 2017-01-30 09:33:00  some_data
6  B  X 2017-01-30 09:33:00  some_data
7  B  Y 2017-01-30 09:33:00  some_data
8  B  Z 2017-01-30 09:33:00  some_data

这篇关于如何获得某些日期的最大日期的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！