Filter groupby by max and recombine into new df

Question

My code is as follows like this:

   import pandas as pd
    import numpy as np
    import matplotlib.pyplot as plt


forex_11 = pd.read_csv('FOREX_20180111.csv', sep=',', parse_dates=['Date'])
forex_12 = pd.read_csv('FOREX_20180112.csv', sep=',', parse_dates=['Date'])
time_format = '%d-%m-%Y'

forex = forex_11.append(forex_12, ignore_index=False)
forex['Date'] = forex['Date'].dt.strftime(time_format)
# print(forex)
forex = forex.loc[forex.groupby("Symbol")["Open"].idxmax()]
# forex = forex.drop_duplicates(subset=['Symbol'], keep='first', inplace=False)
print(forex)

What I get back:

Symbol        Date       Open       High        Low      Close  Volume
0     AEDAUD  11-01-2018     0.3470     0.3470     0.3448     0.3448       0
0     AEDAUD  12-01-2018     0.3448     0.3459     0.3448     0.3456       0
1     AEDCAD  11-01-2018     0.3415     0.3419     0.3408     0.3408       0
1     AEDCAD  12-01-2018     0.3408     0.3415     0.3408     0.3410       0
2     AEDCHF  11-01-2018     0.2663     0.2665     0.2656     0.2656       0
2     AEDCHF  12-01-2018     0.2656     0.2657     0.2647     0.2647       0

I'm still getting duplicate symbols and I can't figure out why

I think need `df1 = df.loc[df.groupby("Symbol")["Open"].idxmax()]` — jezrael, Mar 08 '18 at 13:06
I think I need something different to both - I want your version with all the columns, but without rows with lower values - just the max rows — Davtho1983, Mar 08 '18 at 13:24
Yeah that's because the first ten Symbols have their highest 'Open' values on the first day — Davtho1983, Mar 08 '18 at 13:36
Ok imagine if the data was such that the AEDAUD on the 12th was higher at 0.5000, so that was shown instead — Davtho1983, Mar 08 '18 at 13:42
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166465/discussion-between-davtho1983-and-jezrael). — Davtho1983, Mar 08 '18 at 13:54
I test dupe solution and for me it working nice, check chat with sample data and output (index has to be unique) — jezrael, Mar 08 '18 at 14:19
Ok, can you explain why dupe solution dos not work? then I can reopen question. — jezrael, Mar 08 '18 at 14:22
I'm not getting what you're getting - I have to use forex = forex.drop_duplicates(subset=['Symbol'], keep='first', inplace=False) to get rid of the second row for each symbol — Davtho1983, Mar 08 '18 at 14:45
Please about patience with me. I add sample data in chat, for you it does not work? — jezrael, Mar 08 '18 at 14:46
Honestly it doesn't - I feel like it should, but it's really not — Davtho1983, Mar 08 '18 at 14:48
OK, so you try solution in chat and get different output? Can you show what you get? — jezrael, Mar 08 '18 at 14:49
It seems I see problem, need `forex = forex.reset_index(drop=True)` before `forex = forex.loc[forex.groupby("Symbol")["Open"].idxmax()]` — jezrael, Mar 08 '18 at 14:56

Filter groupby by max and recombine into new df

0 Answers0