CODEWITHSUNDEEP
pythonpandasdataframe
Curious
Curious

Reputation: 335

How to fill a pandas dataframe column using a value from another dataframe column

Firstly we can import some packages which might be useful

import pandas as pd
import datetime

Say I now have a dataframe which has a date, name and age column.

df1 = pd.DataFrame({'date': ['10-04-2020', '04-07-2019', '12-05-2015' ], 'name': ['john', 'tim', 'sam'], 'age':[20, 22, 27]})

Now say I have another dataframe with some random columns

df2 = pd.DataFrame({'a': [1,2,3], 'b': [4,5,6]})

Question:

How can I take the age value in df1 filtered on the date (can select this value) and populate a whole new column in df2 with this value? Ideally this method should generalise for any number of rows in the dataframe.

Tried

The following is what I have tried (on a similar example) but for some reason it doesn't seem to work (it just shows nan values in the majority of column entries except for a few which randomly seem to populate).

y = datetime.datetime(2015, 5, 12)
df2['new'] = df1[(df1['date'] == y)].age

Expected Output

Since I have filtered above based on sams age (date corresponds to the row with sams name) I would like the new column to be added to df2 with his age as all the entries (in this case 27 repeated 3 times).

df2 = pd.DataFrame({'a': [1,2,3], 'b': [4,5,6], 'new': [27, 27, 27]})

Upvotes: 2

Views: 1169

Answers (3)

Vishnudev Krishnadas
Vishnudev Krishnadas

Reputation: 10960

Convert df1 date column to datetime type

df1['date'] = pd.to_datetime(df1.date, format='%d-%m-%Y')

Filter dataframe and get the age

req_date = '2015-05-12'
age_for_date = df1.query('date == @req_date').age.iloc[0]

NOTE: This assumes that there is only one age per date (As explained by OP in comments)

Create a new column

df2 = df2.assign(new=age_for_date)

Output

   a  b  new
0  1  4   27
1  2  5   27
2  3  6   27

Upvotes: 1

Laurent
Laurent

Reputation: 13458

Try:

y = datetime.datetime(2015, 5, 12).strftime('%d-%m-%Y')
df2.loc[:, 'new'] = df1.loc[df1['date'] == y, "age"].item()

# Output
   a  b  new
0  1  4   27
1  2  5   27
2  3  6   27

Upvotes: 1

Sure13
Sure13

Reputation: 41

You'd like to change format of y to Str and try df.loc method

y = datetime.datetime(2015, 5, 12)

y=y.strftime('%d-%m-%Y')
df2['new']=int(df1.loc[df1['date']==y,'age'].values)
df2

Upvotes: 1

Related Questions