Search code examples
python-3.xpandasdataframegroup-by

Extract only natural numbers belonging to a specific column, for every group/id in Python

#Load the required libraries
import pandas as pd

#Create dataset
data = {'id': [1, 1, 1, 1, 1, 1,1, 1, 1, 1, 1, 1,
               2, 2, 2, 2, 2, 2, 2,
               3, 3, 3, 3, 3, 3, 3,3,
               4, 4, 4, 4, 4,4,
               5, 5, 5, 5, 5, 5,5, 5, 5,5,     5,5, 5,5, 5, 5,5],
        'cycle': [0.0, 0.2,0.4, 0.6, 0.8, 1,1.2,1.4,1.6,1.8,2.0,2.2,
                  0.0, 0.2,0.4, 0.6,0.8,1.0,1.2,
                  0.0, 0.2,0.4, 0.6, 0.8,1.0,1.2,1.4,
                  0.0, 0.2,0.4, 0.6, 0.8,1.0,
                  0.0, 0.2,0.4, 0.6, 0.8, 1.0,1.2,1.4,1.6,1.8,   2.0,2.2,2.4,2.6,2.8,3.0,3.2],
        'Salary': [6, 7, 7, 7,8,9,10,11,12,13,14,15,
                   3, 4, 4, 4,4,5,6,
                   2, 8,9,10,11,12,13,14,
                   1, 8,9,10,11,12,
                   6, 7, 7,9,10,11,12,13,14,15, 9,10,11,12,13,14,15],
        'Children': ['Yes', 'No', 'Yes', 'Yes', 'Yes', 'Yes', 'No','No', 'Yes', 'Yes', 'Yes', 'No',
                     'Yes', 'Yes', 'Yes', 'No', 'Yes', 'Yes', 'Yes', 
                     'Yes', 'No','Yes', 'Yes', 'No','No', 'Yes','Yes',
                     'Yes', 'Yes', 'No','Yes', 'Yes','Yes',
                     'Yes', 'No',  'Yes', 'No', 'No', 'Yes', 'Yes', 'Yes', 'Yes', 'No',    'No',  'Yes', 'No', 'No', 'Yes', 'Yes', 'Yes'],
        'Days': [141, 123, 128, 66, 66, 120, 141, 52,96, 120, 141, 52,
                 141, 96, 120,120, 141, 52,96,
                 141,  15,123, 128, 66, 120, 141, 141,
                 141, 141,123, 128, 66,67,
                 141, 123, 128, 66, 123, 128, 66, 120, 141, 52,   123, 128, 66, 123, 128, 66, 120,],
        }

#Convert to dataframe
df = pd.DataFrame(data)
print("\n df = \n",df)

The above dataset looks as such:

enter image description here

Here for every 'id', I wish to extract only those rows, whose 'cycle' is an natural number, as shown in the boxes of the above image file.

The expected dataframe is as such:

enter image description here

Can somebody please let me know how to achieve this task in Pyhton?

Solution

  • Filter out 0 values and compare if integer values is same like float value:

    out=df[df['cycle'].ne(0) & df['cycle'].astype(int).eq(df['cycle'])].reset_index(drop=True)
print (out)
   id  cycle  Salary Children  Days
0   1    1.0       9      Yes   120
1   1    2.0      14      Yes   141
2   2    1.0       5      Yes    52
3   3    1.0      12       No   120
4   4    1.0      12      Yes    67
5   5    1.0      11      Yes   128
6   5    2.0       9       No   123
7   5    3.0      14      Yes    66