JBr JBr - 5 months ago 41
Python Question

adding a column to pandas dataframe based on contents of another dataframe

I have two dataframes

df
and
times
, representing maintenance records and monthly times, respectively. I'd like to append a column to
times
based on the data in
df
:

#df represents car maintenance records
data = {"07-18-2012": ["replaced wheels", 45, 200], "09-12-2014": ["changed oil", 30, 40], "09-18-2015": ["fixed dent", 92, 0]}
df = pd.DataFrame.from_dict(data, orient = "index")
df.index = pd.to_datetime(df.index)
df.sort_index(inplace = True)
df.columns = ["description", "mins_spent", "cost"]

#times represents monthly periods
rng = pd.date_range(start = '12/31/2013', end = '1/1/2015', freq='M')
ts = pd.Series(rng)
times = ts.to_frame(name = "months")


I'm trying to add a new column called
days_since_maintenance
to
times
, that represents the number of days since the most recent maintenance occurring from
df


I've tried using
df.ix[]
, iterating over
for loop
, and
searchsorted()
.

df
:


description mins_spent cost
2012-07-18 replaced wheels 45 200
2014-09-12 changed oil 30 40
2015-09-18 fixed dent 92 0


times
:


months
0 2013-12-31
1 2014-01-31
2 2014-02-28
3 2014-03-31
4 2014-04-30
5 2014-05-31
6 2014-06-30
7 2014-07-31
8 2014-08-31
9 2014-09-30
10 2014-10-31
11 2014-11-30
12 2014-12-31


Desired DataFrame:

months days_since_maintenance
0 2013-12-31 531 days
1 2014-01-31 562 days
2 2014-02-28 ...
3 2014-03-31 ...
4 2014-04-30 ...
5 2014-05-31 ...
6 2014-06-30 ...
7 2014-07-31 ...
8 2014-08-31 774 days
9 2014-09-30 18 days
10 2014-10-31 ...
11 2014-11-30 ...
12 2014-12-31 ...

Answer
df['dates'] = df.index

def days_from_closest(x, df):
    closest = df[df['dates'] < x].ix[-1]
    return x - closest.dates

times['days_since_maintenance'] = times['months'].apply(lambda x: days_from_closest(x, df))

       months  days_since_maintenance
0  2013-12-31                531 days
1  2014-01-31                562 days
2  2014-02-28                590 days
3  2014-03-31                621 days
4  2014-04-30                651 days
5  2014-05-31                682 days
6  2014-06-30                712 days
7  2014-07-31                743 days
8  2014-08-31                774 days
9  2014-09-30                 18 days
10 2014-10-31                 49 days
11 2014-11-30                 79 days
12 2014-12-31                110 days

[13 rows x 2 columns]

Comments