selecting rows from dataframe with specific column and condition(without using column name)

Question

I have two dataframe df1 and df2,

df1

A B
2 6
5 1
7 3
1 2
9 7
4 7
3 4
8 9

and df2 contains

A  B  A_bin  B_bin  C  D  E
2  6  1      2      5  4  1
5  1  2      1      2  2  4
7  3  3      1      5  1  7
1  2  1      1      8  4  9
9  7  3      3      5  5  8
4  7  2      3      1  8  5
3  4  1      2      2  9  3
8  9  3      3      4  6  2

I am trying to select only those specific rows selected from df2 to a new data frame df_result_A for all the row that has A_bin = 1 similarily, a separate data frame df_result_B having all those rows of df2 such that B_bin rows contain 1. I am finding it difficult to put my logic incorrect syntax or probably my logic is wrong,

for i in range(len(df1(df2[columns])+len(df)):
    if(row value is 1)
print in df_result_A
print in df_result_B

As the challenge is to not use column name and indexing, as the code should run for other data set as well I am trying to first iterate over the first two column of df2 as len(df1) will let me know that after 2 columns A_bin and B_bin will come. thus, when I am on the first column of df2 then adding len(df1) will put me on A_bin and iterating over it for checking value to be 1 and storing it in a separate dataframe. Similarly, when I am on 2nd column of df2 adding len(df2) will put me on B_bin and thus storing its result in df_result_B. expected result in separate dataframe.

df_result_A

A  B   C  D  E
2  6   5  4  1
1  2   8  4  9
3  4   2  9  3

df_result_b

A B C D E
5 1 2 2 4
7 3 5 1 7
1 2 8 4 9

Answer 1

You can do something like this:

Sample dataframes:

In [31]: df1
Out[31]: 
   A  B
0  2  6
1  5  1
2  7  3
3  1  2
4  9  7
5  4  7
6  3  4
7  8  9

In [36]: df2
Out[36]: 
   A  B  A_bin  B_bin  C  D  E
0  2  6      1      2  5  4  1
1  5  1      2      1  2  2  4
2  7  3      3      1  5  1  7
3  1  2      1      1  8  4  9
4  9  7      3      3  5  5  8
5  4  7      2      3  1  8  5
6  3  4      1      2  2  9  3
7  8  9      3      3  4  6  2

Have a variable count and an empty dictionary to store new dataframes on the fly.

count = 0
d = dict()

I've used your logic to pick every 3rd column based on the length of columns of df1.

for col in df2.columns:
    print(col)
    l = df1.shape[1]
    if count < l:
        d[col] = df2[ df2.iloc[:, count + l] == 1 ]
    count += 1

This loops over columns of df2 until the variable count is less than number of columns of df1. It filters the rows of 3rd and 4th cols(as per your example) with value of 1 and stores in the dictionary d.

Now, you can loop over your dictionary and find the new dataframes you wanted:

In [52]: for key in d.keys():
    ...:     print(d[key][d[key].columns.drop(list(d[key].filter(regex='bin')))])

   A  B  C  D  E
0  2  6  5  4  1
3  1  2  8  4  9
6  3  4  2  9  3


   A  B  C  D  E
1  5  1  2  2  4
2  7  3  5  1  7
3  1  2  8  4  9

So, you don't have to worry about the exact column names. Let me know if this helps.