Setting DataFrame value using a datetime as index

Question

I have two data frames, one with 3 rows and 4 columns + date as index dataframeA

             TYPE      UNIT  PRICE   PERCENT
2010-01-05   REDUCE    CAR   2300.00   3.0
2010-06-03   INCREASE  BOAT  1000.00   2.0
2010-07-01   INCREASE  CAR   3500.00   3.0

and another empty one with 100's of dates as index and two columns dataframeB

            CAR      BOAT
2010-01-01  Nan        0.0
2010-01-02  Nan        0.0
2010-01-03  Nan        0.0
2010-01-04  Nan        0.0
2010-01-05  -69.00     0.0
.....
2010-06-03  Nan       20.00
...
2010-07-01  105.00     0.0

I need to read each row from the first data frame , find the corresponding date and based on the unit type assign it the corresponding percentage or reduction on the second data frame.

I was reading about not iterating when dealing with dataframes? not sure how else?. how can i evaluate each row and then set the value on dataframeB ?

I tried doing the following :

for index, row in dataframeA.iterrows():
   type = row['TYPE']
   unit = row['UNIT']
   price = row['PRICE']
   percent = row['PERCENT']
   
   then here with basic math come up with the reduction or 
  increase and assign to dataframeB do the same for the others

My question is, is this the right approach and also how do i assign the value i come up to the other dataframeB ?

Jonathan Leon · Accepted Answer · 2021-02-12 04:25:21Z

If your first dataframe is limited to just the variables stated, you can do this. Not terribly elegant, but works. If you have many more combinations in the dataframe, it'd have to be rethought. See comments inline.

df =  pd.read_csv(io.StringIO('''      date       TYPE      UNIT  PRICE   PERCENT
2010-01-05   REDUCE    CAR   2300.00   3.0
2010-06-03   INCREASE  BOAT  1000.00   2.0
2010-07-01   INCREASE  CAR   3500.00   3.0'''), sep='\s+', engine='python').set_index('date')

 df1 =  pd.read_csv(io.StringIO('''date
2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-06-03
2010-07-01'''), engine='python').set_index('date')

# calculate your changes in first dataframe
df.loc[df.TYPE == 'REDUCE', 'Change'] = - df['PRICE'] * df['PERCENT'] / 100
df.loc[df.TYPE == 'INCREASE', 'Change'] = df['PRICE'] * df['PERCENT'] / 100
  

#merge the Changes into car and boat dataframes; rename columns
df_car = df[['Change']].loc[df.UNIT == 'CAR'].merge(df1, right_index=True, left_index=True, how='right')
df_car.rename(columns={'Change':'Car'}, inplace=True)
df_boat = df[['Change']].loc[df.UNIT == 'BOAT'].merge(df1, right_index=True, left_index=True, how='right')
df_boat.rename(columns={'Change':'Boat'}, inplace=True)
  
# merge car and boat  
dfnew =  df_car.merge(df_boat, right_index=True, left_index=True, how='right')
dfnew

            Car Boat
date        
2010-01-01  NaN NaN
2010-01-02  NaN NaN
2010-01-03  NaN NaN
2010-01-04  NaN NaN
2010-01-05  -69.000 NaN
2010-06-03  NaN 20.000
2010-07-01  105.000 NaN

Collectives™ on Stack Overflow

Setting DataFrame value using a datetime as index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related