Add new Datetime to Datetime Index Pandas dataframe with value

Question

Here is my first dataframe df1,

                   Open       High         Low      Close
Date                
2018-07-16  2797.360107 2801.189941 2793.389893 2798.429932
2018-07-17  2789.340088 2814.189941 2789.239990 2809.550049
2018-07-18  2811.350098 2816.760010 2805.889893 2815.620117
2018-07-19  2809.370117 2812.050049 2799.770020 2804.489990
2018-07-20  2804.550049 2809.699951 2800.010010 2801.830078

Now i want to add 21 July which is in another dataframe df2 for eg.,

          Date   Prediction 
0   2018-07-25   2111.111

Now how should I add 2018-07-21 as df1 index and data corresponding to it should be df2['Prediction'] for Close column else other column can be 0.

So final df should be like this,

                Open          High         Low      Close
Date                
2018-07-16  2797.360107 2801.189941 2793.389893 2798.429932
2018-07-17  2789.340088 2814.189941 2789.239990 2809.550049
2018-07-18  2811.350098 2816.760010 2805.889893 2815.620117
2018-07-19  2809.370117 2812.050049 2799.770020 2804.489990
2018-07-20  2804.550049 2809.699951 2800.010010 2801.830078
2018-07-21  0           0            0          2111.111

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Method 1: `pd.concat`

You can use pd.concat, just rename the df2 columns to match the desired columns in df1:

new_df = (pd.concat([df1,
                     df2.set_index('Date')
                     .rename(columns={'Prediction':'Close'})])
          .fillna(0)
          [df.columns])

>>> new_df
                   Open         High          Low        Close
Date                                                          
2018-07-16  2797.360107  2801.189941  2793.389893  2798.429932
2018-07-17  2789.340088  2814.189941  2789.239990  2809.550049
2018-07-18  2811.350098  2816.760010  2805.889893  2815.620117
2018-07-19  2809.370117  2812.050049  2799.770020  2804.489990
2018-07-20  2804.550049  2809.699951  2800.010010  2801.830078
2018-07-25     0.000000     0.000000     0.000000  2111.111000

Method 2: `merge`

Alternatively use merge to do an outer merge. You still need to change the column names in df2 to match where you want the columns to end up

new_df = (df1.reset_index()
          .merge(df2.rename(columns={'Prediction':'Close'}),
                 how='outer')
          .fillna(0)
          .set_index('Date'))

>>> new_df
                   Open         High          Low        Close
Date                                                          
2018-07-16  2797.360107  2801.189941  2793.389893  2798.429932
2018-07-17  2789.340088  2814.189941  2789.239990  2809.550049
2018-07-18  2811.350098  2816.760010  2805.889893  2815.620117
2018-07-19  2809.370117  2812.050049  2799.770020  2804.489990
2018-07-20  2804.550049  2809.699951  2800.010010  2801.830078
2018-07-25     0.000000     0.000000     0.000000  2111.111000

score 1 · Answer 2 · answered Jul 22 '18 at 20:36

1

Just add another row:

df.loc['2018-07-25', 'Close'] = 2111.111

You will have a bunch on NaNs in the other columns. You can fill them with 0s if you want:

df.fillna(0, inplace=True)

answered Jul 22 '18 at 20:36

DYZ

55,249
10
64
93

Add new Datetime to Datetime Index Pandas dataframe with value

2 Answers2

Method 1: pd.concat

Method 2: merge

Method 1: `pd.concat`

Method 2: `merge`