3

I want to combine two dataframes where individual indices exist in a sorted manner, but show up a different number of times in the dataframes that I want to combine.

frame1 = pd.DataFrame([1, 2, 3, 4, 5, 6, 7, 8, 9, 10], index=['A','B','B','C','C','C','D','E','E','F']) frame2 = pd.DataFrame([1, 2, 3, 4, 5, 6, 7, 8, 9, 10], index=['A', 'A', 'B', 'C', 'C', 'D', 'D', 'E', 'F', 'F']) frame1.columns =['Hi'] frame2.columns =['Bye'] frame1 Out[160]: Hi A 1 B 2 B 3 C 4 C 5 C 6 D 7 E 8 E 9 F 10 frame2 Out[161]: Bye A 1 A 2 B 3 C 4 C 5 D 6 D 7 E 8 F 9 F 10

Desired output:

 Bye Hi A 1.0 1.0 A 2.0 NaN B 3.0 2.0 B NaN 3.0 C 4.0 4.0 C 5.0 5.0 C NaN 6.0 D 6.0 7.0 D 7.0 NaN E 8.0 8.0 E NaN 9.0 F 9.0 10.0 F 10.0 NaN

Can't seem to find any right combinations of concat or join to do this. Is there any way?

Improve this question

1 Answer 1

Reset to default
3

Ok, let us build a new key here by using comcount

s1=frame1.set_index(frame1.groupby(level=0).cumcount(),append=True) s2=frame2.set_index(frame2.groupby(level=0).cumcount(),append=True) pd.concat([s2,s1],1).reset_index(level=1,drop=True) Out[364]: Bye Hi A 1.0 1.0 A 2.0 NaN B 3.0 2.0 B NaN 3.0 C 4.0 4.0 C 5.0 5.0 C NaN 6.0 D 6.0 7.0 D 7.0 NaN E 8.0 8.0 E NaN 9.0 F 9.0 10.0 F 10.0 NaN

From piR (great solution with self-define func)

def add_cumcount_level(df): return df.set_index(df.groupby(level=0).cumcount(), append=True) pd.concat(map(add_cumcount_level, [frame1, frame2]), axis=1)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@piRSquared your solution is awesome sir
@NameyMcNamo yw happy coding

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.