combining rows in pandas dataframe

Question

I have the data frame

Date         CUSIP   Asset   Liability
01-01-1990     A       1        NaN
01-01-1990     A      Nan        2
02-01-1990     A       3         2
01-01-1990     B      Nan        2
01-01-1990     B       1         2

Is there anyway of combining this such that it becomes:

Date         CUSIP   Asset   Liability
01-01-1990     A       1         2
02-01-1990     A       3         2
01-01-1990     B       1         2

The way I came up with is to use groupby(["CUSIP", Date]).agg(function)

where I apply a function where the max(nan, 3) = 3.

Is there a simpler way?

Alexander · Accepted Answer · 2017-08-23 21:26:14Z

1

>>> df.groupby(['Date', 'CUSIP']).apply(lambda group: group.ffill().bfill()).drop_duplicates()
         Date CUSIP Asset  Liability
0  01-01-1990     A     1          2
2  02-01-1990     A     3          2
3  01-01-1990     B     1          2

answered Aug 23, 2017 at 21:26

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

5 Comments

Lost1 Over a year ago

yes! the problem with implementing a max with nan method is that this is stupidly slow. One needs to loop through a list and discard nan values. I sometimes hates the way python treats nan...

Alexander Over a year ago

To ensure there are no errors in your data, you can also ensure that there is only one CUSIP on any given date. Assuming the result above is called result, then result.groupby(['Date', 'CUSIP'])['CUSIP'].count().max() should return 1.

Lost1 Over a year ago

thanks for the tip. I am running this. this is very slow. are forward fill and backward fill always this slow?

Alexander Over a year ago

What is the output of df.info()? Are the columns Asset and Liability floats? And what is the type of the Date column? Also, drop_duplicates may be slowing it down. Try without it and see if there is an improvement. If so, and you've checked your data to ensure there are no dups per my comment above, you can group again. df.groupby(['Date', 'CUSIP']).apply(lambda group: group.ffill().bfill()).groupby(['Date', 'CUSIP']).first()

Lost1 Over a year ago

RangeIndex: 501896 entries, 0 to 501895 Data columns (total 4 columns): Date 501896 non-null datetime64[ns] CUSIP 501372 non-null object Asset 386228 non-null float64 Liability 385416 non-null float64 dtypes: datetime64[ns](1), float64(2), object(1)

Collectives™ on Stack Overflow

combining rows in pandas dataframe

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related