CODEWITHSUNDEEP
pythonpandasmaxmin
anniejcannon
anniejcannon

Reputation: 143

Pandas: How to get max and min values and write for every row?

I have a data like that; 

>> df
    A  B  C  
0   1  5  1  
1   1  7  1
2   1  6  1
3   1  7  1
4   2  5  1
5   2  8  1
6   2  6  1
7   3  7  1
8   3  9  1
9   4  6  1
10  4  7  1
11  4  1  1

I want to take max and minimum values of the B column depending on the column A(For the each same value of column A, I want to find the min and max in column B) and want to write results on the original table. My code is:

df1 = df.groupby(['A']).B.transform(max)
df1 = df1.rename(columns={'B':'B_max'})
df2 = df.groupby.(['A']).B.transform(min)
df1 = df1.rename(columns={'B':'B_min'})
df3 = df.join(df1['B_max']).join(df2['B_min'])

This is the result. 

    A  B  C  B_max  B_min
0   1  5  1           
1   1  7  1  7
2   1  6  1
3   1  4  1           4
4   2  5  1
5   2  8  1  8
6   2  6  1           6
7   3  7  1           7
8   3  9  1  9
9   4  6  1
10  4  7  1  7
11  4  1  1           1

But I want to table look like this;

    A  B  C  B_max  B_min
0   1  5  1  7        4 
1   1  7  1  7        4
2   1  6  1  7        4
3   1  4  1  7        4
4   2  5  1  8        6
5   2  8  1  8        6
6   2  6  1  8        6
7   3  7  1  9        7
8   3  9  1  9        7
9   4  6  1  7        1
10  4  7  1  7        1
11  4  1  1  7        1

interpret the code for the result to look like this

Upvotes: 2

Views: 1324

Answers (1)

jezrael
jezrael

Reputation: 862591

I think you need only assign values to new columns, because transform return Series with same length as df:

df = pd.DataFrame({
'A': [1, 1, 1, 1, 2, 2, 2, 3, 3, 4, 4, 4], 
'B': [5, 7, 6, 7, 5, 8, 6, 7, 9, 6, 7, 1], 
'C': [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]})

print (df)
    A  B  C
0   1  5  1
1   1  7  1
2   1  6  1
3   1  7  1
4   2  5  1
5   2  8  1
6   2  6  1
7   3  7  1
8   3  9  1
9   4  6  1
10  4  7  1
11  4  1  1

df['B_max'] = df.groupby(['A']).B.transform(max)
df['B_min'] = df.groupby(['A']).B.transform(min)

print (df)
    A  B  C  B_max  B_min
0   1  5  1      7      5
1   1  7  1      7      5
2   1  6  1      7      5
3   1  7  1      7      5
4   2  5  1      8      5
5   2  8  1      8      5
6   2  6  1      8      5
7   3  7  1      9      7
8   3  9  1      9      7
9   4  6  1      7      1
10  4  7  1      7      1
11  4  1  1      7      1

g = df.groupby('A').B
df['B_max'] = g.transform(max)
df['B_min'] = g.transform(min)

print (df)
    A  B  C  B_max  B_min
0   1  5  1      7      5
1   1  7  1      7      5
2   1  6  1      7      5
3   1  7  1      7      5
4   2  5  1      8      5
5   2  8  1      8      5
6   2  6  1      8      5
7   3  7  1      9      7
8   3  9  1      9      7
9   4  6  1      7      1
10  4  7  1      7      1
11  4  1  1      7      1

Upvotes: 2

Related Questions