pandas, how to filter dataframe by column value

Question

I have a DataFrame like this

>>> df
    id    name    score    subject
    0001   'bob'    100    'math'
    0001   'bob'     67    'science'
    0001   'bob'     63    'bio'
    0002  'jack'     67    'math'
    0002  'jack'     98    'science' 
    0002  'jack'     90    'bio'
    0003  'jack'     60    'math'
    0003  'jack'     78    'science' 
    0003  'rose'     87    'bio'

I want to filter every id's data into a new DataFrame and write to an Excel file based on its id. So, the above df will be filtered into 3 DataFrames whose ids are 0001, 0002 and 0003, and all the DataFrames will be written to individual excel files.

Answer 1

First, get a list of the unique ID values

uniquevalues = np.unique(df[['id']].values)

Then iterate on it and export each dataframe with those IDs in a CSV file

for id in uniquevalues:
    newdf = df[df['id'] == id]
    newdf.to_csv("dataframe "+id+".csv", sep='\t')

If you have only those three IDs, then you can just pass the for and do the same thing manually like

newdf = df[df['id'] == "0001"]
newdf.to_csv("dataframe0001.csv", sep='\t')

Answer 2

IIUC, on your example you can just filter the dataframe by id with:

df1 = df[df['id'] == 0001]

and the same for other id values.