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

user_16400244 0
Viewed 125

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.

pandas python
2 Answers

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
user_154380330

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'})
user_98980330
Related