My df:
{'city1': {0: 'Chicago',
1: 'Chicago',
2: 'Chicago',
3: 'Chicago',
4: 'Miami',
5: 'Houston',
6: 'Austin'},
'city2': {0: 'Toronto',
1: 'Detroit',
2: 'St.Louis',
3: 'Miami',
4: 'Dallas',
5: 'Dallas',
6: 'Dallas'},
'p234_r_c': {0: 5.0, 1: 4.0, 2: 2.0, 3: 0.5, 4: 1.0, 5: 4.0, 6: 3.0},
'plant1_type': {0: 'COMBCYCL',
1: 'COMBCYCL',
2: 'NUKE',
3: 'COAL',
4: 'NUKE',
5: 'COMBCYCL',
6: 'COAL'},
'plant2_type': {0: 'COAL',
1: 'COAL',
2: 'COMBCYCL',
3: 'COMBCYCL',
4: 'COAL',
5: 'NUKE',
6: 'NUKE'}}
I want to do 2 groupby operations and take the largest 1 of each group using column p234_r_c
.
1st groupby = ['plant1_type', 'plant2_type', 'city1']
2nd groupby = ['plant1_type', 'plant2_type', 'city2']
As such I do the following:
df.groupby(['plant1_type','plant2_type','city1'])['p234_r_c'].
nlargest(1).reset_index()
plant1_type plant2_type city1 level_3 p234_r_c
0 COAL COMBCYCL Chicago 3 0.5
1 COAL NUKE Austin 6 3.0
2 COMBCYCL COAL Chicago 0 5.0
3 COMBCYCL NUKE Houston 5 4.0
4 NUKE COAL Miami 4 1.0
5 NUKE COMBCYCL Chicago 2 2.0
The result of the 1st groupby makes sense. However, I am confused by the result of the 2nd groupby:
df.groupby(['plant1_type','plant2_type','city2'])['p234_r_c'].
nlargest(1).reset_index()
index p234_r_c
0 0 5.0
1 1 4.0
2 2 2.0
3 3 0.5
4 4 1.0
5 5 4.0
6 6 3.0
What happened to columns plant1_type
, plant2_type
and city2
in the result? Shouldnt they appear in the result just like how plant1_type
, plant2_type
and city1
appeared in the result of the 1st groupby?
See Question&Answers more detail:
os