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.