Skip to content
Advertisement

How can I handle “Reindexing only valid with uniquely valued Index objects”

I have a dataframe of names (df):

df:

  Group  Name
0     A   jim
1     B   bob
2     C   joe
3     A  jane
4     C   doe

And a schedule dataframe (df1):

df1:

      Group     Due_Date      Assignment
0     A         1-19 17:00    Quiz #1 A
1     B         1-21 16:30    Quiz #1 A
2     C         1-21 16:30    Quiz #1 A
5     A         1-24 11:30    Exam A
6     B         1-24 11:30    Exam A
7     C         1-24 12:30    Exam A

I pivoted df1 (df1_pivoted) to put assignments in the df1 columns:

df1 = df1.pivot(index='Group',
         columns='Assignment',
         values='Due_Date')
df1_pivoted:

Assignment      Exam A   Quiz #1 A
Group
A           1-24 11:30  1-19 17:00
B           1-24 11:30  1-21 16:30
C           1-24 12:30  1-21 16:30

I then try to add the names back in, but I can not figure out how to deal with the “Reindexing only valid with uniquely valued Index objects” error. I presume it is because some names are associated with more than one group:

d=df.set_index('Group')['Name']
p='({})'.format('\b|\b'.join(df['Group']))
df3['Name']=df3['Group'].str.extract(p,expand=False).map(d)

results in error: “Reindexing only valid with uniquely valued Index objects”

I want the final dataframe (df1_final) returned as below:

df1_final:
Group   Name    Exam A      Quiz #1 A
A       jim     1-24 11:30  1-19 17:00
B       bob     1-24 11:30  1-21 16:30
C       joe     1-24 12:30  1-21 16:30
A       jane    1-24 11:30  1-19 17:00
C       doe     1-24 12:30  1-21 16:30

If there is a more pythonic approach to going about this, I am definitely up for learning cleaner programming. Thank you for your help. This community is truly awesome. I am new to programing this past year as a hobby, to make work easier/more fun, and I don’t know if I would stick with it without all of you and your willingness to help. Thanks again

Advertisement

Answer

The problem is that pandas doesn’t know how to handle the direct assignment because A and C have more then one value. Use a left-join in stead to get this to work. Your example is broken (it’s missing parts to reproduce), so imagine that df1_pivoted is your pivoted df1 and df3 is created to look like

df3:
Group   Name  
A       jim 
B       bob
C       joe
A       jane 
C       doe

Then the following should do the job

df3.set_index('Group', inplace=True)
df1_pivoted = df1_pivoted.join(df3, how='left')

Alternatively, you can reset the index, and do a merge on the Group columns for the same effect.

6 People found this is helpful
Advertisement