How to merge a Series and DataFrame

Question

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

User · Answer

You can easily set a pandas DataFrame column to a constant  This constant can be an int such as in your example  If the column you specify isn t in the df  then pandas will create a new column with the name you specify  So after your dataframe is constructed   from your question    df   pd DataFrame   a   np nan  2  3    b   4  5  6    index  3  5  6     You can just run   df  s1    df  s2     5  6   You could write a loop or comprehension to make it do this for all the elements in a list of tuples  or keys and values in a dictionary depending on how you have your real data stored

User · Answer

If df is a pandas DataFrame then df  new col    Series list object of length len df  will add the or Series list object as a column named  new col   df  new col    scalar  such as 5 or 6 in your case  also works and is equivalent to df  new col     scalar  len df   So a two-line code serves the purpose   df   pd DataFrame   a   1  2    b   3  4    s   pd Series   s1  5   s2  6   for x in s index          df x    s x   Output      a  b  s1  s2 0  1  3   5   6 1  2  4   5   6

User · Answer

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

User · Answer

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

User · Answer

If I could suggest setting up your dataframes like this  auto-indexing    df   pd DataFrame   a   np nan  1  2    b   4  5  6      then you can set up your s1 and s2 values thus  using shape   to return the number of rows from df    s   pd DataFrame   s1   5  df shape 0    s2   6  df shape 0      then the result you want is easy   display  df merge s  left index True  right index True     Alternatively  just add the new values to your dataframe df    df   pd DataFrame   a   nan  1  2    b   4  5  6    df  s1   5 df  s2   6 display df    Both return        a  b  s1  s2 0  NaN  4   5   6 1  1 0  5   5   6 2  2 0  6   5   6   If you have another list of data  instead of just a single value to apply   and you know it is in the same sequence as df  eg   s1   a   b   c     then you can attach this in the same way   df  s1   s1   returns        a  b s1 0  NaN  4  a 1  1 0  5  b 2  2 0  6  c

User · Answer

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

[python] How to merge a Series and DataFrame

Examples related to python

Examples related to pandas

Examples related to dataframe