1

I have a pandas Series S:

Date  
2/27/2017    149 
2/28/2017    150
3/01/2017    154
3/04/2017    152
3/12/2017    156
3/17/2017    148

I also have a Dataframe df

Date                  A   B    C   PS
2/28/2017 12:42:05    1    2   4  2/27/2017
2/28/2017 12:42:07    1    2   4  2/27/2017
2/28/2017 12:42:08    1    2   4  2/27/2017
2/28/2017 12:42:55    1    2   4  2/27/2017
3/01/2017 12:42:05    1    2   4  2/28/2017
3/01/2017 12:42:07    1    2   4  2/28/2017
3/01/2017 12:42:08    1    2   4  2/28/2017
3/01/2017 12:42:09    1    2   4  2/28/2017
3/05/2017 12:42:05    1    2   4  3/04/2017
3/05/2017 12:42:07    1    2   4  3/04/2017
3/05/2017 12:42:08    1    2   4  3/04/2017
3/05/2017 12:42:09    1    2   4  3/04/2017

A, B, C do change, but they are not relevant for this question.

I would like to have an output dataframe as follows:

Date                  A   B    C   PS         Value
2/28/2017 12:42:05    1    2   4  2/27/2017   149
2/28/2017 12:42:07    1    2   4  2/27/2017   149
2/28/2017 12:42:08    1    2   4  2/27/2017   149
2/28/2017 12:42:55    1    2   4  2/27/2017   149
3/01/2017 12:42:05    1    2   4  2/28/2017   150
3/01/2017 12:42:07    1    2   4  2/28/2017   150
3/01/2017 12:42:08    1    2   4  2/28/2017   150
3/01/2017 12:42:09    1    2   4  2/28/2017   150
3/05/2017 12:42:05    1    2   4  3/04/2017   152
3/05/2017 12:42:07    1    2   4  3/04/2017   152
3/05/2017 12:42:08    1    2   4  3/04/2017   152
3/05/2017 12:42:09    1    2   4  3/04/2017   152

Basically I want to add a column to df, called Value, where Value is whatever value corresponds to the Date in series S, that is in column PS of df.

The pseudocode would be df["Value"] = S[df[PS]]

I don't want to bring the Date column/index from the series over.

cs95
  • 379,657
  • 97
  • 704
  • 746
john
  • 169
  • 12
  • 1
    This is a simple `merge()` operation. Read any tutorial on `merge()`. – DYZ Aug 22 '17 at 01:50
  • What is the output of `df.info()` and `s.info()`? (where `df` and `s` are the names of your dataframe and series, respectively). – Alexander Aug 22 '17 at 02:05
  • `df.merge(s.to_frame(), left_on='PS', right_index=True)` assuming the dates in you series index are consistent with those in column `PS`. – Alexander Aug 22 '17 at 02:16
  • Do note that merging is much slower compared to map or replace. – cs95 Aug 24 '17 at 11:04

1 Answers1

2

Merge dataframe with series using .merge():

new_df = df.merge(pd.DataFrame(s, columns=['Value']), right_index=True, left_on='PS', how='outer')

You can also iterate over PS and get the value from the series:

df.loc[:, 'Value'] = [s[ps] for ps in df['PS']]
cs95
  • 379,657
  • 97
  • 704
  • 746
Vico
  • 579
  • 3
  • 13