Sort multiple row values

Python Help
1 
n = 0

a = 12
b = 31
c = 16
d = 26

while n < len(df)-3: 
    if df.loc[n].isin([a]).any():         
        if df.loc[n+1].isin([b]).any()  :
            if df.loc[n+2].isin([c]).any() :
                if df.loc[n+3].isin([d]).any() :                
                     display (df.loc[n-1:n-1],df.loc[n:n], df.loc[n+1:n+1])
    n+=1
Column 1 Column 2 Column 3 Column 4
1 3 8 8
3 2 12 54
23 31 32 1
16 36 7 56
15 4 78 26
18 8 2 88

I’m trying to find out 3 rows if value a is found in first row from dataframe, b in second, c third and d fourth. If found then print row 0 first and second and continue searching. and output is shown like

Column 1 Column 2 Column 3 Column 4
1 3 8 8
3 2 12 54
23 31 32 1
  1. Is there a way to sort the output of those 3 rows numerically in a line without duplicates?
    For example 1,2,3,8,12,23,31,32,54 (horizontally or vertically)
  2. and how can i show output without column names here? I tried display (df.loc[n-1:n-1],df.loc[n:n], df.loc[n+1:n+1] , (df.to_string(index=False, header=False))) but not working :weary:
2

HI! That is specific question about pandas library, but maybe I can help.

Because a dataframe is stored as tabular format, for your request output you could:

  1. If match, create a sub-dataframe with the results you want to display
  2. Get the values of that df with pandas.DataFrame.values, whch returns a numpy array.
  3. Flatten that multidimensional np array with numpy.ravel.
  4. By (mathematical) definition, a set has no duplicate numbers. And python can generate a set from an iterable.
  5. Finally, python has a method list.sort.

Once you get the sub-dataframe with the solution, the remaining steps can be done in one line:

sorted_values = sorted(set(selected_rows.values.ravel()))

Note that you can unpack the list sorted_values when printing it:

print(*sorted_values, sep=',')

About your nested loops, maybe you can create a mask with pandas.DataFrame.iloc. Something like this:

mask = (df.iloc[0] == a) & (df.iloc[1] == b) & (df.iloc[2] == c) & (df.iloc[3] == d)

if mask.any():
    # if match is found, do the previous
    selected_rows = df.iloc[0:3]
    sorted_values = sorted(set(selected_rows.values.ravel()))
    print(*sorted_values, sep=", ")
1 Like
3

Hello,

to answer your first question:

Your values are a, b, c, d = 12, 31, 16, 26 (four values thus four rows are expected). You want to find out if:

a in row1
b in row2
c in row3
d in row4

Note that you’re bypassing row 0 since panda dataframes begin with the standard starting convention of 0. In your expected outcome you only listed three rows. Plus, the first row does not include any of the values that you have listed. I will interpret this as an oversight / typo(?).

To find the rows as per your requirements, you can try something like this:

import pandas as pd

# Raw data to make original dataframe
data1 = [1,3,23,16,15,18]
data2 = [3,2,31,36,4,8]
data3 = [8,12,32,7,78,2]
data4 = [8,54,1,56,26,88]

# Update dataframe with column titles and raw data
df1 = pd.DataFrame(({'Column 1': data1,
                   'Column 2': data2,
                   'Column 3': data3,
                   'Column 4': data4}))

df2 = pd.DataFrame()
print('\n', df1, '\n', end='', sep='')  # print original dataframe for reference

a, b, c, d = 12, 31, 16, 26  # Values to search for
values = [a, b, c, d]        # Put values in a list so that they may be indexed

# You can use for loops to index through the rows and through the required search values
for col in ('Column 1', 'Column 2', 'Column 3', 'Column 4'):

    for j, value in enumerate(values):

        if df1.loc[j+1, col] == value:
            df2 = pd.concat([df2, df1[df1[col] == value]])

df3 = df2.sort_index()
print(df3)

This will provide this output:

   Column 1  Column 2  Column 3  Column 4
1         3         2        12        54    # row 1, value 12
2        23        31        32         1    # row 2, value 31
3        16        36         7        56    # row 3, value 16
4        15         4        78        26    # row 4, value 26
1 Like
4

Thanks a lot for the help. it worked :slightly_smiling_face: