Combining Two Series into a Dataframe in Pandas

Combining two Series into a DataFrame in pandas

I think concat is a nice way to do this. If they are present it uses the name attributes of the Series as the columns (otherwise it simply numbers them):

In [1]: s1 = pd.Series([1, 2], index=['A', 'B'], name='s1')

In [2]: s2 = pd.Series([3, 4], index=['A', 'B'], name='s2')

In [3]: pd.concat([s1, s2], axis=1)
Out[3]:
   s1  s2
A   1   3
B   2   4

In [4]: pd.concat([s1, s2], axis=1).reset_index()
Out[4]:
  index  s1  s2
0     A   1   3
1     B   2   4

Note: This extends to more than 2 Series.

Combining two series in pandas along their index

You can use concat:

In [11]: s1
Out[11]:
id
1      3
3     19
4     15
5      5
6      2
Name: count_1, dtype: int64

In [12]: s2
Out[12]:
id
1     3
3     1
4     1
5     2
6     1
Name: count_2, dtype: int64

In [13]: pd.concat([s1, s2], axis=1)
Out[13]:
    count_1  count_2
id
1         3        3
3        19        1
4        15        1
5         5        2
6         2        1

Note: if these were DataFrame (rather than Series) you could use merge:

In [21]: df1 = s1.reset_index()

In [22]: s1.reset_index()
Out[22]:
   id  count_1
0   1        3
1   3       19
2   4       15
3   5        5
4   6        2

In [23]: df2 = s2.reset_index()

In [24]: df1.merge(df2)
Out[24]:
   id  count_1  count_2
0   1        3        3
1   3       19        1
2   4       15        1
3   5        5        2
4   6        2        1

How to combine three pandas series into one dataframe by date?

You can concat them:

pd.concat([adx, adx_neg, adx_pos], axis=1)

---

Full example:

import pandas as pd
df = pd.DataFrame({'Date': {0: '2021-06-07',
  1: '2021-06-08',
  2: '2021-06-09',
  3: '2021-06-10',
  4: '2021-06-11',
  5: '2021-06-14'},
 'price': {0: 20.912307000000002,
  1: 19.82028,
  2: 18.502489,
  3: 17.278827,
  4: 16.142569,
  5: 15.874234}})
df = df.set_index('Date')
df1, df2, df3 = df.copy(), df.copy(), df.copy()

# Code
pd.concat([df1, df2, df3], axis=1)

# Output
                price      price      price
Date                                       
2021-06-07  20.912307  20.912307  20.912307
2021-06-08  19.820280  19.820280  19.820280
2021-06-09  18.502489  18.502489  18.502489
2021-06-10  17.278827  17.278827  17.278827
2021-06-11  16.142569  16.142569  16.142569
2021-06-14  15.874234  15.874234  15.874234

How to merge a Series and DataFrame

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.

Create DataFrame from multiple Series

You can use pd.concat:

pd.concat([r, s], axis=1)
Out: 
   rrr  sss
0    0    0
1    3    5
2    6   10
3    9   15
4   12   20
5   15   25
6   18   30
7   21   35
8   24   40
9   27   45

Or the DataFrame constructor:

pd.DataFrame({'r': r, 's': s})

Out: 
    r   s
0   0   0
1   3   5
2   6  10
3   9  15
4  12  20
5  15  25
6  18  30
7  21  35
8  24  40
9  27  45

Combine two Series into one column of a Dataframe

You could use this if you want: (if you s1 and s2 have same size)

df['mismatch'] = pd.concat([s1,s2],axis = 1).apply(lambda x: ', '.join(x) if not x.all() == '' else x.sum(),axis=1)

I only asked to change None because the if statement would be this simple like that.

The idea is to concat the two series by column and simply join the rows if there are no empty strings. If there are we just sum the rows elements.

That way you can get an output like :

       A     B mismatch
0  stuff  more   aa, aa
1  stuff  more       bb
2  stuff  more       cc
3  stuff  more         
4  stuff  more   ee, ee

without the weird ,.

Hope this was helpful.

Join two Pandas Series with different DateTimeIndex

Use concat with inner join:

df = pd.concat([s1, s2], axis=1, keys=('s1','s2'), join='inner')
print (df)
            s1  s2
2020-03-01   1  20
2020-03-05   3  25
2020-03-07   4  36

Solution with interpolate of s2 Series and then removed rows with missing values:

df = (pd.concat([s1, s2], axis=1, keys=('s1','s2'))
        .assign(s2 = lambda x: x.s2.interpolate('index'))
        .dropna())
print (df)
             s1    s2
2020-03-01  1.0  20.0
2020-03-03  2.0  23.0
2020-03-05  3.0  25.0
2020-03-07  4.0  36.0

Pandas: Merge dataframe and series based on index

You are almost there. Just pass left_index and right_index simultaneously to get the desired effect as below.

# Reproduce your data
import pandas as pd
priceearning_byyear = pd.DataFrame(dict(year=[2016,2017,2018,2019,2020], eps=[2.09,2.32,3.00,2.99,3.31])).set_index('year')
price = pd.Series([28.95,42.31,39.44,73.41,132.69,119.99], index=[2016,2017,2018,2019,2020,2021])
price.name = 'Close'
price.index.name='year'

# Merge priceearning_byyear and price by using their indexes
priceearning_byyear.merge(price,left_index=True,right_index=True)

---

Related Topics