How to add a column to an existing dataframe from another dataframe?

Question

I have a df like below

                                   stock__stock__FINCODE_      month_  price_  quantity_sum_buy  quantity_sum_sell  ...  cumbuyprice  cumsellprice  qty_rem  price_avg_sum  invested_value
stock__stock__FINCODE_                                                                                              ...                                                                   
100112                 2022-06-01                  100112  2022-06-01  2000.0            1500.0                0.0  ...       2000.0           0.0   1500.0         2000.0       3000000.0
                       2022-07-01                       0           0     0.0               0.0                0.0  ...       2000.0           0.0   1500.0         2000.0       3000000.0
                       2022-08-01                  100112  2022-08-01  3000.0               0.0             1500.0  ...       2000.0        3000.0      0.0         1000.0             0.0
                       2022-09-01                       0           0     0.0               0.0                0.0  ...       2000.0        3000.0      0.0         1000.0             0.0
100325                 2022-06-01                  100325  2022-06-01  1500.0             100.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0
                       2022-07-01                       0           0     0.0               0.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0
                       2022-08-01                       0           0     0.0               0.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0
                       2022-09-01                       0           0     0.0               0.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0

and another dataframe

                        Date    Close  Fincode
4   2022-09-08 00:00:00+00:00   544.65   100112
5   2022-09-08 00:00:00+00:00  2585.40   100325
6   2022-09-07 00:00:00+00:00   532.85   100112
7   2022-09-07 00:00:00+00:00  2581.75   100325
8   2022-09-06 00:00:00+00:00   537.80   100112
..                        ...      ...      ...
127 2022-06-03 00:00:00+00:00  2779.50   100325
128 2022-06-02 00:00:00+00:00   469.85   100112
129 2022-06-02 00:00:00+00:00  2724.30   100325
130 2022-06-01 00:00:00+00:00   468.30   100112
131 2022-06-01 00:00:00+00:00  2633.50   100325

I would like to add Close column to the first datafram for each date and stock__stock__FINCODE_

final result would be something like this

                                   stock__stock__FINCODE_      month_  price_  quantity_sum_buy  quantity_sum_sell  ...  cumbuyprice  cumsellprice  qty_rem  price_avg_sum  invested_value  Close
stock__stock__FINCODE_                                                                                              ...                                                                   
100112                 2022-06-01                  100112  2022-06-01  2000.0            1500.0                0.0  ...       2000.0           0.0   1500.0         2000.0       3000000.0  468
                       2022-07-01                       0           0     0.0               0.0                0.0  ...       2000.0           0.0   1500.0         2000.0       3000000.0  468.5
                       2022-08-01                  100112  2022-08-01  3000.0               0.0             1500.0  ...       2000.0        3000.0      0.0         1000.0             0.0  467.2
                       2022-09-01                       0           0     0.0               0.0                0.0  ...       2000.0        3000.0      0.0         1000.0             0.0  466.5
100325                 2022-06-01                  100325  2022-06-01  1500.0             100.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0  2633.50
                       2022-07-01                       0           0     0.0               0.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0  2633.4
                       2022-08-01                       0           0     0.0               0.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0  2630.3
                       2022-09-01                       0           0     0.0               0.0                0.0  ...       1500.0           0.0    100.0         1500.0        150000.0  2630.4

```Close``` column is in second df and that needs to be added in the first df for each date — Deekshith Hari, Sep 13 '22 at 13:55
can you include sample data as code pls, should be a `.merge` — Deusdeorum, Sep 13 '22 at 14:03

score 1 · Answer 1 · answered Sep 13 '22 at 14:13

I created two dfs that include partial data from your dfs:

df1

    Date        FINCODE price_
0   2022-06-01  100112  2000.0
1   2022-07-01  0       0.0

df2

    Date        FINCODE Close
0   2022-06-01  100112  544.65
1   2022-07-01  100325  2585.40

If you make sure the Date formats are the same, you can use merge. In my case I used pd.to_datetime(df1.Date) to make sure they were the same format:

pd.merge(df1, df2, on=['Date', 'FINCODE'], how='left')
    Date        FINCODE price_  Close
0   2022-06-01  100112  2000.0  544.65
1   2022-07-01  0       0.0     NaN

date formats are same but the problem here is the structure. I tried to merge like this ``` pd.merge(df2, df1, left_on=['stock__stock__FINCODE_', 'month_'], right_on=['Fincode','Date'], how='left') ``` but it throws Value error ``` 'stock__stock__FINCODE_' is both an index level and a column label, which is ambiguous. ``` — Deekshith Hari, Sep 14 '22 at 06:21

How to add a column to an existing dataframe from another dataframe?

1 Answers1