how to apply function over multiple rows

Viewed 54

I split a series of period ranges in one column into two columns using pandas i.e. Date = 100-120 --> EarliestDate = 100, LatestDate = 120

The only problem is that some ranges also include abbreviations (i.e. 150-56 or 225-26, leading to bad outcomes like {150, 56} and {225-26})

I'm trying to write a function that will expand the abbreviation. Something like

def expansion(x, y):
    for x in data_frame['EarliestDate']:
       for y in data_frame['LatestDate']:
         if x>100 and y>100 and y<x:
           return (math.floor(x/100))*100 + y)
         else:
           pass

Basically, I take the number in the earliest range, divide by a hundred, round it down with math.floor and then add it to the later range (so 56 will be turned back into 156). I'm not sure if this is the most efficient way (maybe it would be better to convert to string and use regular expression?) I also have to write similar functions for when the date range involves dates 1-99 or negative dates (i.e. -110)

But my major problem is when I applied this as a lambda function:

data_frame['LatestDate'] = data_frame[['EarliestDate', 'LatestDate']].apply(lambda x: expansion(x.EarliestDate, x.LatestDate), axis=1)

The result of the lambda function just overwrites all the original correct values in 'LatestDate' (even when the condition does not obtain) and with the same single value (79). I'm not sure why this is--many thanks.

1 Answers

try .loc instead of multiple for loops:

data_frame.loc[(data_frame.EarliestDate >100) & (data_frame.LatestDate >100) &(data_frame.EarliestDate > data_frame.LatestDate), 'newCol'] = math.floor(data_frame.loc[(data_frame.EarliestDate >100) & (data_frame.LatestDate >100) &(data_frame.EarliestDate > data_frame.LatestDate), 'EarliestDate'] /100 )*100+  data_frame.loc[(data_frame.EarliestDate >100) & (data_frame.LatestDate >100) &(data_frame.EarliestDate > data_frame.LatestDate), 'LatestDate']

You can select all rows where your conditions are true using the mask:

mask = (data_frame.EarliestDate >100) & (data_frame.LatestDate >100) &(data_frame.EarliestDate > data_frame.LatestDate)

You can select those rows using .loc using the syntax:

data_frame.loc[mask, 'newCol']

The second argument is a column index where newCol will be a new column in data_frame because it doesn't exist already. Then on the other side of the equals you use the same mask and make your calculation with the specified columns. Anywhere the mask isn't valid, it will leave a NaN.

Related