pandas create a subset according to a value in a column

Question

I have this dataframe:

86,1/28/2004 0:00:00,16.9
86,5/25/2004 0:00:00,17.01
86,7/22/2004 0:00:00,17.06
87,11/15/2004 0:00:00,7.39
87,3/14/2005 0:00:00,7.59
86,11/15/2004 0:00:00,17.29
86,3/14/2005 0:00:00,17.38
86,4/19/2005 0:00:00,17.43
86,5/19/2005 0:00:00,17.28
87,1/22/2004 0:00:00,7.44
87,5/13/2004 0:00:00,7.36

I would like to work on two separate dataframe according to the value (id) of the first column. Ideally, I would like to have:

87,11/15/2004 0:00:00,7.39
87,3/14/2005 0:00:00,7.59
87,1/22/2004 0:00:00,7.44
87,5/13/2004 0:00:00,7.36

and

86,1/28/2004 0:00:00,16.9
86,5/25/2004 0:00:00,17.01
86,7/22/2004 0:00:00,17.06
86,11/15/2004 0:00:00,17.29
86,3/14/2005 0:00:00,17.38
86,4/19/2005 0:00:00,17.43
86,5/19/2005 0:00:00,17.28

As you can see I have one dataframe with all 87 in the first column and another with 86.

This is how I read the dataframe:

dfr = pd.read_csv(fname,sep=',',index_col=False,header=None)

I think that groupby is not the right options, if I have understood correctly the command.

I was thinking about query as:

aa = dfr.query(dfr.iloc[:,0]==86)

However, I have this error:

 expr must be a string to be evaluated, <class 'pandas.core.series.Series'> given

Does this answer your question? How to filter Pandas dataframe using 'in' and 'not in' like in SQL — Paul
– Paul, Commented Nov 30, 2021 at 16:12

Federico Raimondi Cominesi · Accepted Answer · 2021-11-30 16:09:09Z

3

You can simply slice your dataframe:

df_86 = df.loc[df['ColName'] == 86,:]

answered Nov 30, 2021 at 16:09

Federico Raimondi Cominesi

8069 silver badges10 bronze badges

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

Comments

cazman · Accepted Answer · 2021-11-30 16:16:10Z

1

Another way to do it dynamically without having to specify the group beforehand.

df = pd.DataFrame({'ID': np.repeat([1, 2, 3], 4), 'col2': np.repeat([10, 11, 12], 4)})

Get the unique groupings:

groups = df['ID'].unique()

Create an empty dict to store new data frames

new_dfs = {}

Loop through and create new data frames from the slice:

for group in groups:
    name = "ID" + str(group)
    new_dfs[name] = df[df['ID'] == group]

new_dfs['ID1']

Which gives:

answered Nov 30, 2021 at 16:16

cazman

1,5021 gold badge6 silver badges11 bronze badges

Collectives™ on Stack Overflow

pandas create a subset according to a value in a column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related