Create a column with counts of values from multiple columns (if any)

Question

I have a dataframe with date columns:``

import pandas as pd
df = pd.DataFrame({'_id':['633739b043e8750c660feabd'] + ['63373966bf5eb50c6d593b1f'] + ['633738aa43e8750c660fe90e'] + ['63373853bf5eb50c6d5938c1'],

'2022-10-01':['1', '0', '0', '2'],
'2022-10-02':['0', '1', '0', '0'],
'2022-10-03':['2', '8', '3', '9'],
'2022-10-04':['0', '0', '0', '2'],
'2022-10-05':['9', '0', '0', '2'],
'2022-10-06':['1', '0', '3', '2'],

})

I need to get the counts in another column if the Id's date column has a value in it.

The result should look like this:

import pandas as pd

df = pd.DataFrame({'_id':['633739b043e8750c660feabd'] + ['63373966bf5eb50c6d593b1f'] + ['633738aa43e8750c660fe90e'] + ['63373853bf5eb50c6d5938c1'],
    '2022-10-01':['1', '0', '0', '2'],
    '2022-10-02':['0', '1', '0', '0'],
    '2022-10-03':['2', '8', '3', '9'],
    '2022-10-04':['0', '0', '0', '2'],
    '2022-10-05':['9', '0', '0', '2'],
    '2022-10-06':['1', '0', '3', '2'],
    'No_of_Days':['4', '2', '2', '5']
})

Sorry the image was not being uploaded due to server error So I have added a sample codes. It's simple but still I am struggling as I am new to this.

The good news is that adding sample code is the correct way to ask questions. Try to avoid posting images of code or data. — Chris
– Chris, Commented Oct 13, 2022 at 13:40

Chris · Accepted Answer · 2022-10-13 13:38:07Z

1

You can set the index as _id, then convert the rest of the df to integer and take the row-wise sum of values greater than zero.

df['No_of_Days'] = df.set_index('_id').astype(int).gt(0).sum(1).values

answered Oct 13, 2022 at 13:38

Chris

16.3k3 gold badges26 silver badges41 bronze badges

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

2 Comments

Arkam Over a year ago

Worked perfectly for me! But if have many columns in the dataframe which consists of different datatypes, how can I modify this?

Chris Over a year ago

you could check out select_dtypes but the way you posted your sample data, your integers are actually strings. If that is really the case you're need to manually drop columns prior to doing this.

R. Baraiya · Accepted Answer · 2022-10-13 13:49:04Z

0

Here I am putting all date column values in list and counting zeros in it. like [1,0,2,0,9,1] so 2 time zero and total 6 days so 6-2 = 4 will be No of days.

Code:

df['No_of_Days'] = [len(i[1:]) - i[1:].count('0') for i in df.values.tolist()]

or

df['No_of_Days'] = [len(i[1:]) - i[1:].count('0') for i in df.values.tolist()] if 'No_of_Days' not in df else df['No_of_Days']

df

edited Oct 13, 2022 at 13:49

answered Oct 13, 2022 at 13:40

R. Baraiya

1,5281 gold badge6 silver badges20 bronze badges

2 Comments

Arkam Over a year ago

I tried this but the rows which had no 0 values were not being counts. Other than that it was working fine.

R. Baraiya Over a year ago

might be your 0 type is int so just try by .count(0) without ''.

Collectives™ on Stack Overflow

Create a column with counts of values from multiple columns (if any)

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related