Use values from list in order to create few new Dataframe based on existing one

My current DF looks like below

x  y  z  x  c name  status
1  2  3  2  5 Jon   Work
1  2  5  4  5 Adam  Work
9  7  3  9  5 Adam  Holiday
3  2  3  4  5 Anna  Work
1  4  6  8  5 Anna  Work
4  1  6  8  5 Kate  Off
2  1  6  1  5 Jon   Off

My lists with specific values looks like below:

name = [Jon, Adam]

status = [Off, Work]

I need using those lists create new dataframes for all unique elements in “status” list. So it should looks like below:

df_off:

x  y  z  x  c name  status
2  1  6  1  5 Jon   Off

there is only one values, because name Kate in not in the list name

df_Work:

x  y  z  x  c name  status
1  2  3  2  5 Jon   Work
1  2  5  4  5 Adam  Work

In second DF there is no “Anna” because she is not in list “name”.

I hope it is clear. Do you have any idea how can I solve this issue?

Regard Tomasz

Answer

First part, filter your data using:

name = ['Jon', 'Adam']
status = ['Off', 'Work']
df[df['name'].isin(name)&df['status'].isin(status)]

Then use groupby and transform the output to dictionary:

conditions = df['name'].isin(name)&df['status'].isin(status)
dfs = {'df_%s' % k:v for k,v in df[conditions].groupby('status')}

Then access your dataframes using:

>>> dfs['df_Work']
   x  y  z  x.1  c  name status
0  1  2  3    2  5   Jon   Work
1  1  2  5    4  5  Adam   Work

You can even use multiple groups:

dfs = {'df_%s_%s' % k:v for k,v in df.groupby(['name', 'status'])}
dfs['df_Adam_Work']

If you goal is to save the subframes:

for groupname, df in df[conditions].groupby('status'):
    df.to_excel(f'df_{groupname}.xlsx')