Print indexes of the columns using Pandas in Python

Question

I have an input data frame which looks like this:

print (df)
    Id  A  B  C  D
0  101  0  0  0  1
1  102  0  0  0  0
2  103  1  0  1  0
3  104  1  0  1  1

Output: I want to print the indexes of the columns which contain '1' in it. The output data frame should look like this. If 1 is not present it should return an empty string.

Id 101- D (4th index) has 1
Id 102- None
Id 104- A, C and D which are 1,3,4 indexes

So, a sample output would look like:

print (df)
    Id  Result
0  101       4
1  102        
2  103     1,3
3  104   1,3,4

jezrael · Accepted Answer · 2020-10-08 05:38:28Z

1

Use DataFrame.dot for matrix multiplication by helper RangeIndex by length of columns onverted to strings.

Columns, which cannot be processing are set to index by DataFrame.set_index

#all columns without first df.columns[1:]
c = pd.RangeIndex(1, len(df.columns[1:]) + 1).astype(str) + ','
df = df.set_index('Id').dot(c).str[:-1].reset_index(name=' Result')
print (df)
    Id  Result
0  101       4
1  102        
2  103     1,3
3  104   1,3,4

EDIT:

print (df)
    Id  Quantity  A  B  C  D
0  101        10  0  0  0  1
1  102        50  0  0  0  0
2  103        80  1  0  1  0
3  104        60  1  0  1  1


#all columns without first and second df.columns[2:]
c = pd.RangeIndex(1, len(df.columns[2:]) + 1).astype(str) + ','
df = df.set_index(['Id','Quantity']).dot(c).str[:-1].reset_index(name='Result')
print (df)
print (df)
    Id  Quantity Result
0  101        10      4
1  102        50       
2  103        80    1,3
3  104        60  1,3,4

edited Oct 8, 2020 at 5:38

answered Oct 8, 2020 at 5:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bishu Over a year ago

Thanks a lot for the solution. What if I have one more column 'Quantity' after 'Id'.What changes I have to make in the code? Sorry, but I am quite new to pandas.

jezrael Over a year ago

@AshishKumar - No problem, give ma sec

Derek Eden · Accepted Answer · 2020-10-08 05:30:31Z

0

could just do something like this... I used StringIO to read your data, but same principle if reading from csv

from io import StringIO
import pandas as pd

data = ''' Id | A | B | C | D

 101 | 0 | 0 | 0 | 1

 102 | 0 | 0 | 0 | 0

 103 | 1 | 0 | 1 | 0

 104 | 1 | 0 | 1 | 1'''

df = pd.read_csv(StringIO(x), sep='\s+\|\s+', engine='python', index_col='Id')
df.apply(lambda x: ','.join(map(str, pd.np.where(x==1)[0]+1)), axis=1).reset_index(name='Result')

output:

    Id Result
0  101      4
1  102
2  103    1,3
3  104  1,3,4

answered Oct 8, 2020 at 5:30

Derek Eden

4,6584 gold badges22 silver badges39 bronze badges

Comments

Thamindu DJ · Accepted Answer · 2020-10-08 05:35:17Z

0

You can do this using apply and lambda functionality too. I stored the answer in a separate dataframe.

df = pd.DataFrame({'Id':[101,102,103,104], 'A':[0,0,1,1], 'B':[0,0,0,0], 'C':[0,0,1,1], 'D':[1,0,0,1]})

result = pd.DataFrame({'Id': df.Id})

result['Result'] = df.apply(
    lambda x: ','.join(str(ele) for ele in [id+1 for id,val in enumerate(x[1:]) if val is 1]), 
    axis=1
)

print(result)

Result

    Id Result
0  101      4
1  102       
2  103    1,3
3  104  1,3,4

answered Oct 8, 2020 at 5:35

Thamindu DJ

2,0094 gold badges20 silver badges31 bronze badges

Collectives™ on Stack Overflow

Print indexes of the columns using Pandas in Python

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related