How to append keys which are not previously in dataframe 1 but are in dataframe 2 against each name

user_13670188 0
Viewed 74

I have a Dataframe df1 like this

    id  name  day  marks  mean_marks  
0   1  John  Wed     28          28  
1   1  John  Fri     30          30  
2   2  Alex  Fri     40          50  
3   2  Alex  Fri     60          50

and another dataframe df2 as:

    day  we
 0  Mon  29
 1  Wed  21
 2  Fri  31

now when i do :

z = pd.merge(df1, df2, how='outer', on=['day']).fillna(0)

i got:

    id  name  day  marks  mean_marks    we
0  1.0  John  Wed   28.0        28.0    21
1  1.0  John  Fri   30.0        30.0    31
2  2.0  Alex  Fri   40.0        50.0    31
3  2.0  Alex  Fri   60.0        50.0    31
4  0.0     0  Mon    0.0         0.0    29

but i wanted something which would look like :

    id  name  day  marks  mean_marks    we
0  1.0  John  Wed   28.0        28.0    21
1  1.0  John  Mon   0.0         0.0     29
2  1.0  John  Fri   30.0        30.0    31
3  2.0  Alex  Mon   0.0         0.0     29
4  2.0  Alex  Wed   0.0         0.0     21
5  2.0  Alex  Fri   40.0        50.0    31
6  2.0  Alex  Fri   60.0        50.0    31

that is 'day' which are not previously in df1 but are in df2 should be appended to day against each name. Can someone please help me with this.

pandas python
4 Answers

You might need a cross join to create all combinations of days per id and name , then merge should work:

u = df1[['id','name']].drop_duplicates().assign(k=1).merge(df2.assign(k=1),on='k')
out = df1.merge(u.drop('k',1),on=['day','name','id'],how='outer').fillna(0)

print(out.sort_values(['id','name']))

   id  name  day  marks  mean_marks  we
0   1  John  Wed   28.0        28.0  21
1   1  John  Fri   30.0        30.0  31
4   1  John  Mon    0.0         0.0  29
2   2  Alex  Fri   40.0        50.0  31
3   2  Alex  Fri   60.0         5.0  31
5   2  Alex  Mon    0.0         0.0  29
6   2  Alex  Wed    0.0         0.0  21
user_98406370

The following code should do it:

z = df1.groupby(['name']).apply(lambda grp: grp.merge(df2, how='outer', on='day').
                                fillna({'name': grp.name, 'id': grp.id})).reset_index(drop=True).fillna(0)

It gives the following output:

    id  name  day marks mean_marks  we
0  2.0  Alex  Fri    40         50  31
1  2.0  Alex  Fri    60         50  31
2  2.0  Alex  Mon     0          0  29
3  2.0  Alex  Wed     0          0  21
4  1.0  John  Wed    28         28  21
5  1.0  John  Fri    30         30  31
6  1.0  John  Mon     0          0  29
user_140140930

you can create df3 with all names and day combination:

df3 = pd.DataFrame([[name, day] for name in df1.name.unique() for day in df2.day.unique()], columns=['name', 'day'])

Then add id's from df1:

df3 = df3.merge(df1[['id', 'name']]).drop_duplicates()[['id', 'name', 'day']]

Then add marks and mean marks from df1:

df3 = df3.merge(df1, how='left')

Then merge:

z = df3.merge(df2, how='outer', on=['day']).fillna(0).sort_values('id')

Out:

    id  name    day marks   mean_marks  we
0   1   John    Mon 0.0     0.0         29
2   1   John    Wed 28.0    28.0        21
4   1   John    Fri 30.0    30.0        31
1   2   Alex    Mon 0.0     0.0         29
3   2   Alex    Wed 0.0     0.0         21
5   2   Alex    Fri 40.0    50.0        31
6   2   Alex    Fri 60.0    50.0        31
user_49089000

To have the result ordered by weekdays (within each group by id), we should convert day column in both DataFrames to Categorical type. I think, it is better than in your original concept, where you don't care about the days order.

To do it, run:

wDays = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
days = pd.Categorical(wDays, wDays, ordered=True)
df1.day = df1.day.astype(days)
df2.day = df2.day.astype(days)

Then define the following function, performing merge within a group by id and filling NaN values (using either ffill or fillna):

def myMerge(grp):
    res = pd.merge(grp, df2, how='right', on=['day'])
    res[['id', 'name']] = res[['id', 'name']].ffill()
    res[['marks', 'mean_marks']] = res[['marks', 'mean_marks']].fillna(0)
    return res.sort_values('day')

Then group df1 by id and apply the above function to each group:

df1.groupby('id', sort=False).apply(myMerge).reset_index(drop=True)

The final step above is reset_index, to re-create "ordinary" index.

I added also sort=False to keep your desired (original) order of groups.

user_73884770
Related