Merge a Dataframe and a Series with the same index

Question

I got a Dataframe as below:

            Name     Sales
Datetime
2021-06-01  Amy      1000
2021-06-02  Amy      1500
2021-06-03  Amy      2300
2021-06-01  Joyce    3200
2021-06-02  Joyce    1422
2021-06-03  Joyce    1002

And I got another Series as shown below:

             Weather
Datetime
2021-06-01   Rain
2021-06-02   Clear
2021-06-03   Rain, Cloudy

What I expect is merging the dataframe and the series based on the index:

            Name     Sales   Weather
Datetime
2021-06-01  Amy      1000    Rain
2021-06-02  Amy      1500    Clear
2021-06-03  Amy      2300    Rain, Cloudy
2021-06-01  Joyce    3200    Rain
2021-06-02  Joyce    1422    Clear
2021-06-03  Joyce    1002    Rain, Cloudy

Thanks!

The second data looks like a dataframe with one column, not a Series. This is kind of important, which is it? — timgeb, Jan 18 '22 at 09:11
It is a series but I can change it to a dataframe by `second_df.to_frame()` — Hang, Jan 18 '22 at 09:15

score 0 · Accepted Answer · answered Jan 18 '22 at 09:14

As you posted it, the "Series" is a one column DataFrame. Anyway, all you have to do is make an assignment. pandas will match the index for you.

data

>>> df 
             Name  Sales
Datetime                
2021-06-01    Amy   1000
2021-06-02    Amy   1500
2021-06-03    Amy   2300
2021-06-01  Joyce   3200
2021-06-02  Joyce   1422
2021-06-03  Joyce   1002
>>> df2 
                 Weather
Datetime                
2021-06-01          Rain
2021-06-02         Clear
2021-06-03  Rain, Cloudy

solution

>>> df['Weather'] = df2
>>> df 
             Name  Sales       Weather
Datetime                              
2021-06-01    Amy   1000          Rain
2021-06-02    Amy   1500         Clear
2021-06-03    Amy   2300  Rain, Cloudy
2021-06-01  Joyce   3200          Rain
2021-06-02  Joyce   1422         Clear
2021-06-03  Joyce   1002  Rain, Cloudy

score 0 · Answer 2 · answered Jan 18 '22 at 09:17

0

You can use for add DataFrame by DataFrame.join:

df = df.join(df1['Weather'])

If need add Series:

df = df.join(s.rename('Weather'))

answered Jan 18 '22 at 09:17

jezrael

822,522
95
1,334
1,252

Merge a Dataframe and a Series with the same index

2 Answers2