How to merge a Series and DataFrame

If you came here looking for information on how to merge a DataFrame and Series on the index, please look at this answer.

The OP's original intention was to ask how to assign series elements as columns to another DataFrame. If you are interested in knowing the answer to this, look at the accepted answer by EdChum.

Best I can come up with is

df = pd.DataFrame({'a':[1, 2], 'b':[3, 4]})  # see EDIT below
s = pd.Series({'s1':5, 's2':6})

for name in s.index:
    df[name] = s[name]

   a  b  s1  s2
0  1  3   5   6
1  2  4   5   6

Can anybody suggest better syntax / faster method?

My attempts:

df.merge(s)
AttributeError: 'Series' object has no attribute 'columns'

and

df.join(s)
ValueError: Other Series must have a name

EDIT The first two answers posted highlighted a problem with my question, so please use the following to construct df:

df = pd.DataFrame({'a':[np.nan, 2, 3], 'b':[4, 5, 6]}, index=[3, 5, 6])

with the final result

    a  b  s1  s2
3 NaN  4   5   6
5   2  5   5   6
6   3  6   5   6

Solution 1:

Update
From v0.24.0 onwards, you can merge on DataFrame and Series as long as the Series is named.

df.merge(s.rename('new'), left_index=True, right_index=True)
# If series is already named,
# df.merge(s, left_index=True, right_index=True)

Nowadays, you can simply convert the Series to a DataFrame with to_frame(). So (if joining on index):

df.merge(s.to_frame(), left_index=True, right_index=True)

Solution 2:

You could construct a dataframe from the series and then merge with the dataframe. So you specify the data as the values but multiply them by the length, set the columns to the index and set params for left_index and right_index to True:

In [27]:

df.merge(pd.DataFrame(data = [s.values] * len(s), columns = s.index), left_index=True, right_index=True)
Out[27]:
   a  b  s1  s2
0  1  3   5   6
1  2  4   5   6

EDIT for the situation where you want the index of your constructed df from the series to use the index of the df then you can do the following:

df.merge(pd.DataFrame(data = [s.values] * len(df), columns = s.index, index=df.index), left_index=True, right_index=True)

This assumes that the indices match the length.

Solution 3:

Here's one way:

df.join(pd.DataFrame(s).T).fillna(method='ffill')

To break down what happens here...

pd.DataFrame(s).T creates a one-row DataFrame from s which looks like this:

   s1  s2
0   5   6

Next, join concatenates this new frame with df:

   a  b  s1  s2
0  1  3   5   6
1  2  4 NaN NaN

Lastly, the NaN values at index 1 are filled with the previous values in the column using fillna with the forward-fill (ffill) argument:

   a  b  s1  s2
0  1  3   5   6
1  2  4   5   6

To avoid using fillna, it's possible to use pd.concat to repeat the rows of the DataFrame constructed from s. In this case, the general solution is:

df.join(pd.concat([pd.DataFrame(s).T] * len(df), ignore_index=True))

Here's another solution to address the indexing challenge posed in the edited question:

df.join(pd.DataFrame(s.repeat(len(df)).values.reshape((len(df), -1), order='F'), 
        columns=s.index, 
        index=df.index))

s is transformed into a DataFrame by repeating the values and reshaping (specifying 'Fortran' order), and also passing in the appropriate column names and index. This new DataFrame is then joined to df.

Solution 4:

Nowadays, much simpler and concise solution can achieve the same task. Leveraging the capability of DataFrame.apply() to turn a Series into columns of its belonging DataFrame, we can use:

df.join(df.apply(lambda x: s, axis=1))

Result:

     a  b  s1  s2
3  NaN  4   5   6
5  2.0  5   5   6
6  3.0  6   5   6

Here, we used DataFrame.apply() with a simple lambda function as the applied function on axis=1. The applied lambda function simply just returns the Series s:

df.apply(lambda x: s, axis=1)

Result:

   s1  s2
3   5   6
5   5   6
6   5   6

The result has already inherited the row index of the original DataFrame df. Consequently, we can simply join df with this interim result by DataFrame.join() to get the desired final result (since they have the same row index).

This capability of DataFrame.apply() to turn a Series into columns of its belonging DataFrame is well documented in the official document as follows:

By default (result_type=None), the final return type is inferred from the return type of the applied function.

The default behaviour (result_type=None) depends on the return value of the applied function: list-like results will be returned as a Series of those. However if the apply function returns a Series these are expanded to columns.

The official document also includes example of such usage:

Returning a Series inside the function is similar to passing result_type='expand'. The resulting column names will be the Series index.

df.apply(lambda x: pd.Series([1, 2], index=['foo', 'bar']), axis=1)   

   foo  bar
0    1    2
1    1    2
2    1    2