#332 Remove duplicates by columns and keep the row with the highest value in the selected column by pandas

Jan 5, 2023 | pandas, python

table.txt

quad lcid eid fx fy fxy
QUAD 1 23 1.2 1.0 21.0
QUAD 4 24 2.6 2.0 22.0
QUAD 1 25 3.2 3.0 23.0
QUAD 2 23 4.6 4.0 24.0
QUAD 4 24 5.6 5.0 25.0
QUAD 2 25 6.2 6.0 26.0
QUAD 3 23 7.2 7.0 27.0
QUAD 3 24 6.2 8.0 28.0
QUAD 2 25 5.2 9.0 29.0
QUAD 4 23 3.3 10.0 30.0
QUAD 4 24 12.2 11.0 31.0
QUAD 1 25 1.1 12.0 32.0

ex332.py

import pandas as pd
df = pd.read_csv("table.txt", sep=" ")
# display DataFrame
print("Input:")
print(df)
# Keeping the row with the highest value
# Remove duplicates by columns eid and keeping the
# row with the highest value in column fx
print(" ")
# max fx
df1=df.sort_values('fx', ascending=False).drop_duplicates('eid').sort_index()
df1=df1.sort_values('eid', ascending=True)
print("Result max fx:")
print(df1)
# min fx
df1=df.sort_values('fx', ascending=True).drop_duplicates('eid').sort_index()
df1=df1.sort_values('eid', ascending=True)
print("Result min fx:")
print(df1)

# max fy
df1=df.sort_values('fy', ascending=False).drop_duplicates('eid').sort_index()
df1=df1.sort_values('eid', ascending=True)
print("Result max fy:")
print(df1)
# min fy
df1=df.sort_values('fy', ascending=True).drop_duplicates('eid').sort_index()
df1=df1.sort_values('eid', ascending=True)
print("Result min fy:")
print(df1)

# max fxy
df1=df.sort_values('fxy', ascending=False).drop_duplicates('eid').sort_index()
df1=df1.sort_values('eid', ascending=True)
print("Result max fxy:")
print(df1)
# min fxy
df1=df.sort_values('fxy', ascending=True).drop_duplicates('eid').sort_index()
df1=df1.sort_values('eid', ascending=True)
print("Result min fxy:")
print(df1)

Output:

Input:
    quad  lcid  eid    fx    fy   fxy
0   QUAD     1   23   1.2   1.0  21.0
1   QUAD     4   24   2.6   2.0  22.0
2   QUAD     1   25   3.2   3.0  23.0
3   QUAD     2   23   4.6   4.0  24.0
4   QUAD     4   24   5.6   5.0  25.0
5   QUAD     2   25   6.2   6.0  26.0
6   QUAD     3   23   7.2   7.0  27.0
7   QUAD     3   24   6.2   8.0  28.0
8   QUAD     2   25   5.2   9.0  29.0
9   QUAD     4   23   3.3  10.0  30.0
10  QUAD     4   24  12.2  11.0  31.0
11  QUAD     1   25   1.1  12.0  32.0
 
Result max fx:
    quad  lcid  eid    fx    fy   fxy
6   QUAD     3   23   7.2   7.0  27.0
10  QUAD     4   24  12.2  11.0  31.0
5   QUAD     2   25   6.2   6.0  26.0
Result min fx:
    quad  lcid  eid   fx    fy   fxy
0   QUAD     1   23  1.2   1.0  21.0
1   QUAD     4   24  2.6   2.0  22.0
11  QUAD     1   25  1.1  12.0  32.0
Result max fy:
    quad  lcid  eid    fx    fy   fxy
9   QUAD     4   23   3.3  10.0  30.0
10  QUAD     4   24  12.2  11.0  31.0
11  QUAD     1   25   1.1  12.0  32.0
Result min fy:
   quad  lcid  eid   fx   fy   fxy
0  QUAD     1   23  1.2  1.0  21.0
1  QUAD     4   24  2.6  2.0  22.0
2  QUAD     1   25  3.2  3.0  23.0
Result max fxy:
    quad  lcid  eid    fx    fy   fxy
9   QUAD     4   23   3.3  10.0  30.0
10  QUAD     4   24  12.2  11.0  31.0
11  QUAD     1   25   1.1  12.0  32.0
Result min fxy:
   quad  lcid  eid   fx   fy   fxy
0  QUAD     1   23  1.2  1.0  21.0
1  QUAD     4   24  2.6  2.0  22.0
2  QUAD     1   25  3.2  3.0  23.0