Pandas dataframe merge

Question

I have a concatenated pandas dataframe from 4 dataframes like this:

In [121]: all
Out[121]:
       E  H  N  S
   102P    Y  NaN  NaN  NaN
   103R    Y  NaN  NaN  NaN
   102P  NaN  NaN    Y  NaN
   103R  NaN  NaN    Y  NaN
   109F  NaN  NaN    Y  NaN
   103R  NaN    Y  NaN  NaN
   109F  NaN    Y  NaN  NaN
   102P  NaN  NaN  NaN    Y
   103R  NaN  NaN  NaN    Y
   109F  NaN  NaN  NaN    Y

I want to consolidate this into a dataframe like this:

        E   H   N   S
  102P  Y  NAN  Y   Y
  103R  Y   Y   Y   Y
  109F NAN  Y   Y   Y

How can I merge them based on all.index?

Viktor Kerkez · Accepted Answer · 2013-09-21 08:20:00Z

1

Do a groupby on the index (I presume from the data you posted that the values 102P... are in the index). And count the values. That will return a DataFrame with zeros and ones. Just replace them with appropriate values.

>>> ndf = df.groupby(level=0).count()
>>> ndf[ndf == 1] = 'Y'
>>> ndf[ndf == 0] = np.nan
>>> ndf
         E    H  N  S
label                
102P     Y  NaN  Y  Y
103R     Y    Y  Y  Y
109F   NaN    Y  Y  Y

If you have repetitions then just change the condition from ndf[ndf == 1] to ndf[ndf > 0].

But why are you concatenating the data frames instead of combining them? Example:

>>> df1
      E   H   N   S
0                  
102P  Y NaN NaN NaN
103R  Y NaN NaN NaN
>>> df2
       E   H  N   S
0                  
102P NaN NaN  Y NaN
103R NaN NaN  Y NaN
109F NaN NaN  Y NaN

...

>>> reduce(lambda first, second: first.combine_first(second),
           [df1, df2, df3, df4], pd.DataFrame())
        E    H  N  S
0                   
102P    Y  NaN  Y  Y
103R    Y    Y  Y  Y
109F  NaN    Y  Y  Y

edited Sep 21, 2013 at 8:20

answered Sep 20, 2013 at 18:03

Viktor Kerkez

46.8k13 gold badges109 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

UNagaswamy Over a year ago

pd.merge takes only two dataframes at a time, I ended up with two sets of merged df's which I merged to get the final df. Is this the right way or am I missing something?

Viktor Kerkez Over a year ago

@UNagaswamy Updated the answer, added example. I'm sorry for misleading you, I was not clear enough when I said merge :( I was not thinking about the merge function but a merge as a general term...

user2685079 Over a year ago

You could use nested merge.

Collectives™ on Stack Overflow

Pandas dataframe merge

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related