Copy values from one column to another column with different rows based on two conditions

Question

my dataframe looks basically like this:

data = [[11200, 33000,dt.datetime(1995,3,1),10,np.nan], [11200, 33000, dt.datetime(1995,3,2),11, np.nan],[11200, 33000, dt.datetime(1995,3,3),9, np.nan],\
[23400, 45000, dt.datetime(1995,3,1),50, np.nan],  [23400, 45000, dt.datetime(1995,3,3),49, np.nan], [33000, 55000, dt.datetime(1995,3,1),60, np.nan], [33000, 55000, dt.datetime(1995,3,2),61, np.nan]]


df = pd.DataFrame(data, columns = ["Identifier", "Identifier2" ,"date", "price","price2"])

Output looks like:

index Identifier1 Identifier2     date    price1 price2 
  0      11200      33000      1995-03-01   10     nan
  1      11200      33000      1995-03-02   11     nan
  2      11200      33000      1995-03-03    9     nan
  3      23400      45000      1995-03-01   50     nan
  4      23400      45000      1995-03-03   49     nan
  5      33000      55000      1995-03-01   60     nan
  6      33000      55000      1995-03-02   61     nan

Please note that my index is not sorted by ascending numbers like to one of my example df. I would like to: look for the number that is in column Identifier2 (I know the exact number I want to look up) in column Identifier 1. Then copy the value of price1 into price2 with respect to correct dates, because some dates are missing.

My goal would look like this:

   index Identifier1 Identifier2     date    price1 price2 
      0      11200      33000      1995-03-01   10     60
      1      11200      33000      1995-03-02   11     61
      2      11200      33000      1995-03-03    9     nan
      3      23400      45000      1995-03-01   50     nan
      4      23400      45000      1995-03-03   49     nan
      5      33000      55000      1995-03-01   60     nan
      6      33000      55000      1995-03-02   61     nan

I'm sure this is not too difficult, but somehow I don't get it. Thank you very much in advance for any help.

Hi, this can be done with a merge, does the column price2 already exist in your real data? — Ben.T
– Ben.T, Commented Jul 12, 2021 at 19:10
Hi, I already tried using merge, but I got stuck when trying to merge two dataframes that did not have the same amounts of rows. And yes, the column price2 already exists, but there is no data in it. — GC2023
– GC2023, Commented Jul 12, 2021 at 19:55

Nk03 · Accepted Answer · 2021-07-12 19:13:09Z

2

One way:

df['price2'] = df[['Identifier2', 'date']].apply(tuple, 1).map(df.set_index(['Identifier','date'])['price'].to_dict())

OUTPUT:

   Identifier  Identifier2       date  price  price2
0       11200        33000 1995-03-01     10    60.0
1       11200        33000 1995-03-02     11    61.0
2       11200        33000 1995-03-03      9     NaN
3       23400        45000 1995-03-01     50     NaN
4       23400        45000 1995-03-03     49     NaN
5       33000        55000 1995-03-01     60     NaN
6       33000        55000 1995-03-02     61     NaN

answered Jul 12, 2021 at 19:13

Nk03

15k2 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nk03 Over a year ago

If you see the above code to generate the df. There’s no suffix -> 1. So, that’s why I ignored it.

L.Stefan · Accepted Answer · 2021-07-12 19:49:36Z

1

I don't know if is the best way, but this works:

Using merge:

#Get a copy like 2 separated dataframe's
df1 = df [['index', 'Identifier',  'Identifier2','date', 'price']]
df2 = df [['Identifier','date', 'price']]

#Mergin on left
df3 = df1.merge(df2, how = 'left' ,left_on = ['Identifier2','date'] , right_on =['Identifier','date'], suffixes=('','R'))

#Drop created IdentifierR column an rename priceR to price2
df4 = df3.drop('IdentifierR', axis=1).rename(columns={'priceR':'price2'})

answered Jul 12, 2021 at 19:49

L.Stefan

3562 silver badges15 bronze badges

2 Comments

Ben.T Over a year ago

if you rename df2 before the merge, then it can be a bit less verbose. With your notation, then df1.merge(df2.rename(columns={'Identifier':'Identifier2', 'price':'price2'}), how = 'left') is directly the final result ;)

L.Stefan Over a year ago

@Ben.T Yeah, think this, but I tried to be more didactic

Collectives™ on Stack Overflow

Copy values from one column to another column with different rows based on two conditions

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related