How to overwrite data in a row of one dataframe with slice from another

user_10594317 0
Viewed 1550

I have two dataframes:

df_GB is a list of students and student data for a class df_EV is a set of survey responses from a subset of students in the class

import pandas as pd
import numpy as np

# Create the two dataframes
In [82]: gradebook=[['Jim','T'],['Susan','F'],['Bob','F'],['Ellen','T']]
In [83]: df_GB=pd.DataFrame(gradebook,columns=['Name','Attend'])
In [84]: survey=[['Jim',1,3,4,'Awesome'],['Ellen',1,4,3,'Splendid'],['Fred',0,1,2,'Passable']]
In [85]: df_EV=pd.DataFrame(survey,columns=['Name','Q1','Q2','Q3','Comment'])

#Display the two dataframes
In [86]: df_GB
Out[86]: 
    Name   Attend
0    Jim        T
1  Susan        F
2    Bob        F
3  Ellen        T

In [87]: df_EV
Out[87]: 
    Name  Q1  Q2  Q3   Comment
0    Jim   1   3   4   Awesome
1  Ellen   1   4   3  Splendid
2   Fred   0   1   2  Passable

I want to add survey responses for each student listed in df_EV to the correct rows in df_GB to get the following:

In [90]: df_result
Out[90]: 
    Name Attend   Q1   Q2   Q3   Comment
0    Jim      T  1.0  3.0  4.0   Awesome
1  Susan      F  NaN  NaN  NaN       NaN
2    Bob      F  NaN  NaN  NaN       NaN
3  Ellen      T  1.0  4.0  3.0  Splendid
4   Fred    NaN  0.0  1.0  2.0  Passable

screenshot of what df_GB, df_EV, and df_result look like as tables

I've tried creating df_result from the list of names in df_GB, then looping through the names in df_result, searching for those names in df_EV, and "pasting" in the data using loc, but it doesn't work because I'm trying to paste a dataframe as a series into a dataframe, and it throws a "ValueError: Incompatible indexer with Series" error.

df_result['Name']=pd.DataFrame({'Name' : df_GB['Name']})
i=0
while i<df_result.shape[0]
   name=df_result.at[i,'Name']
   df_result.loc[i,'Q1':'Comment']=df_EV.loc[lambda df_EV: df_EV['Name']==name,['Q1','Q2','Q3','Comment']]
   i +=1

I've been beating my head on the keyboard for a while now trying to figure out how to do this. Tips? I'm brand new to python--downloaded it yesterday after using Matlab for years for tasks like this--and this seems like too simple a thing to be so hard to figure out.

The similar questions I read through seemed to be solved by appending rows or columns to a dataframe, which I don't think will work in this case since the 'Name' lists in the two dataframes don't match, unless I'm missing an obvious trick.

pandas slice merge python
2 Answers
user_87083640 
import pandas as pd

df_GB = pd.DataFrame([[ 'Jim','T'],['Susan','F'],['Bob','F'],'Ellen','T']],columns = [ 'Name', 'Attend'])
df_EV = pd.DataFrame([[ 'Jim',1,3,4,'Awesome'],['Ellen',1,4,3,'Splendid'],    ['Fred',0,1,2,'Passable']],columns = ['Name','Q1','Q2','Q3','Comment'])

df_result = pd.merge(df_EV,df_GB,on = 'Name',how = 'outer')

df_result
Out[33]: 
    Name   Q1   Q2   Q3   Comment Attend
0    Jim  1.0  3.0  4.0   Awesome      T
1  Ellen  1.0  4.0  3.0  Splendid      T
2   Fred  0.0  1.0  2.0  Passable    NaN
3  Susan  NaN  NaN  NaN       NaN      F
4    Bob  NaN  NaN  NaN       NaN      F



 df_GB.join(df_EV.set_index('Name'), on='Name',how ='outer')
Out[45]: 
    Name Attend   Q1   Q2   Q3   Comment
0    Jim      T  1.0  3.0  4.0   Awesome
1  Susan      F  NaN  NaN  NaN       NaN
2    Bob      F  NaN  NaN  NaN       NaN
3  Ellen      T  1.0  4.0  3.0  Splendid
3   Fred    NaN  0.0  1.0  2.0  Passable
user_83500010
Related