Find largest value from multiple colums in each group of row index in Python, arrange those values diagonally in matrix, and find determinant

Question

I am new to Python. I want to find the largest values from all the columns for repetitive row indexes (i.e. 5 to 130), and also show its row and column index label in output.The largest values should be absolute. (Irrespective of + or - sign). There should not be duplicates for row indexes in different groups. After finding largest from each group,I want to arrange those values diagonally in square matrix. Then fill the remaining array with the corresponding values of indexes for each group from the main dataframe and find its Determinant.

df=pd.DataFrame(
    {'0_deg': [43, 50, 45, -17, 5, 19, 11, 32, 36, 41, 19, 11, 32, 36, 1, 19, 7, 1, 36, 10], 
     '10_deg': [47, 41, 46, -18, 4, 16, 12, 34, -52, 31, 16, 12, 34, -71, 2, 9, 52, 34, -6, 9], 
     '20_deg': [46, 43, -56, 29, 6, 14, 13, 33, 43, 6, 14, 13, 37, 43, 3, 14, 13, 25, 40, 8], 
     '30_deg': [-46, 16, -40, -11, 9, 15, 33, -39, -22, 21, 15, 63, -39, -22, 4, 6, 25, -39, -22, 7]
    }, index=[5, 10, 12, 101, 130, 5, 10, 12, 101, 130, 5, 10, 12, 101, 130, 5, 10, 12, 101, 130]
)

Data set :

Expected Output:

My code is showing only till output 1.

Actual Output:

Code:

df = pd.read_csv ('Matrixfile.csv')
df = df.set_index('Number')

def f(x):
    x1 = x.abs().stack()
    x2 = x.stack()
    x = x2.iloc[np.argsort(-x1)].head(1)
    return x

groups = (df.index == 5).cumsum()
df1 = df.groupby(groups).apply(f).reset_index(level=[1,2])
df1.columns = ['Number','Angle','Value']

print (df1)
df1.to_csv('Matrix_OP.csv', encoding='utf-8', index=True)

I believe your output is incorrect. After the `-52` the next largest number in the 2nd group is `43`. **Not** `41`. Unless I've misunderstood. — piRSquared, May 23 '19 at 16:02
@piRSquared - Yes you are right, but if you see, even ```43``` in the 2nd group is from row index ```101``` only, My query is that ```101``` has largest value in group 3 . i.e ```-71``` , so it must get allocated to group 3. Thanks. — axay, May 23 '19 at 16:24
@piRSquared - For your understanding , I have edited my outputs, if you could provide some help. — axay, May 23 '19 at 17:13
@piRSquared - The code you deleted very closely matched to my expected output. Request you to kindly provide the same again. — axay, May 23 '19 at 17:53

Yacola · Accepted Answer · 2019-05-24T17:49:19.267

I am not sure about @piRSquared output from what I understood from your question. There might be some errors in there, for instance, in group 2, max(abs(values)) = 52 (underline in red in picture) but 41 is displayed on left...

Here is a less elegant way of doing it but maybe easier for you to understand :

import numpy as np

# INPUT
data_dict ={'0_deg': [43, 50, 45, -17, 5, 19, 11, 32, 36, 41, 19, 11, 32, 36, 1, 19, 7, 1, 36, 10], 
   '10_deg': [47, 41, 46, -18, 4, 16, 12, 34, -52, 31, 16, 12, 34, -71, 2, 9, 52, 34, -6, 9], 
   '20_deg': [46, 43, -56, 29, 6, 14, 13, 33, 43, 6, 14, 13, 37, 43, 3, 14, 13, 25, 40, 8], 
   '30_deg': [-46, 16, -40, -11, 9, 15, 33, -39, -22, 21, 15, 63, -39, -22, 4, 6, 25, -39, -22, 7],
   }

# Row idx of a group in this list
idx = [5, 10, 12, 101, 130]


# Getting some dimensions and sorting the data
row_idx_length = len(idx) 
group_length = len(data_dict['0_deg'])
number_of_groups = len(data_dict.keys())  
idx = idx*number_of_groups   
data_arr = np.zeros((group_length,number_of_groups),dtype=np.int32) 
#
col = 0
keys = []
for key in sorted(data_dict):
    data_arr[:,col] = data_dict[key]
    keys.append(key)
    col+=1
def get_extrema_value_group(arr):
    # function to find absolute extrema value of a 2d array
    extrema = 0
    for i in range(0, len(arr)):
        max_value = max(arr[i])
        min_value = min(arr[i])
        if (abs(min_value) > max_value) and (abs(extrema) < abs(min_value)):
            extrema = min_value
        elif (abs(min_value) < max_value) and (abs(extrema) < max_value):
            extrema = max_value         
    return extrema 

# For output 1
max_values = []  
for i in range(0,row_idx_length*number_of_groups,row_idx_length):
    # get the max value for the current group
    value = get_extrema_value_group(data_arr[i:i+row_idx_length])
    # get the row and column idx associated with the max value
    idx_angle_number = np.nonzero(abs(data_arr[i:i+row_idx_length,:])==value)
    print('Group number : ' + str(i//row_idx_length+1))
    print('Number : '+ str(idx[idx_angle_number[0][0]]))
    print('Angle : '+ keys[idx_angle_number[1][0]])
    print('Absolute extrema value : ' + str(value))   
    print('------')
    max_values.append(value)

# Arrange those values diagonally in square matrix for output 2
A = np.diag(max_values)   
print('A = ' + str(A))

# Fill A with desired values
for i in range(0,number_of_groups,1):
    A[i,0] = data_arr[i*row_idx_length+2,2]   # 20 deg 12
    A[i,1:3] = data_arr[i*row_idx_length+3,1] # x2 : 10 deg 101
    A[i,3] = data_arr[i*row_idx_length+1,1]   # 10 deg 10

# Final output
# replace the diagonal of A with max values
# get the idx of diag
A_di = np.diag_indices(number_of_groups)
# replace with max values
A[A_di] = max_values
print ('A = ' + str(A)) 

# Compute determinant of A
det_A = np.linalg.det(A)
print ('det(A) = '+str(det_A))

Output 1:

Group number : 1
Number : 12
Angle : 20_deg
Absolute extrema value : -56
------
Group number : 2
Number : 101
Angle : 10_deg
Absolute extrema value : -52
------
Group number : 3
Number : 101
Angle : 10_deg
Absolute extrema value : -71
------
Group number : 4
Number : 10
Angle : 10_deg
Absolute extrema value : 52
------

Output 2 :

A = [[-56  0  0  0]
     [ 0 -52  0  0]
     [ 0  0 -71  0]
     [ 0  0  0  52]]

Output 3 :

A = [[-56 -18 -18  41]
     [ 33 -52 -52  12]
     [ 37 -71 -71  12]
     [ 25  -6  -6  52]]

det(A) = -5.4731330578761246e-11

Your code provides me the output 1. I have edited my question, if you can provide help regarding my further Output 2 and Final Output. Thanks. — axay, May 23 '19 at 17:05
For the final output, the instructions are pretty unclear, add more explanations please — Yacola, May 23 '19 at 17:37
just need to fill the remaining spaces with corresponding index values of columns. i.e for Array A12 - its value from index```101 , 10_deg``` for A13 - value from index```101, 10_deg``` , for A14 its value from index```10``` ,```10_deg``` and for A21 - value from index ```12```, ```20_deg``` of ```group 2```.and so on for ```group 3``` and ```group 4``` respectively.. — axay, May 23 '19 at 17:46
It's done, I used absolute value for diagonal values, it could be simpler but it's doing what you intended I guess... — Yacola, May 23 '19 at 18:12
Thanks. Your code does the magic, If its possible , could you please provide alternate solution to the output. Where I want 1 max value for each group without repeating **Row Index**. I have edited the output slightly to avoid repetition of indexes . — axay, May 24 '19 at 14:20
Now, row_idx is just an input of the code with no repetition. Nonetheless, the repetition is still there after that but could be easily avoided, I spent already too much time on this and the instructions are still rather unclear... Hope this still help. — Yacola, May 24 '19 at 15:13
Actually in case of repetition of index ```101``` , 2 columns would be same, so my determinant value would be 0. The diagonal values should not be absolute. I want as it is, i.e. ```-56``` ,```-52```, ```-71``` , ```52```. Or i will put up a simplier question. — axay, May 24 '19 at 15:41
For your understanding, I have put up a simplier question. If you could please provide some addition to this code. — axay, May 24 '19 at 17:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193898/discussion-between-yacola-and-akshay-k). — Yacola, May 24 '19 at 17:50

Find largest value from multiple colums in each group of row index in Python, arrange those values diagonally in matrix, and find determinant

1 Answers1