CODEWITHSUNDEEP
pythonpandas
EyJay
EyJay

Reputation: 180

Pandas - Fill column depending on date in dictionary

I am trying to fill a dataframe column with the value of two other columns depending on a date i have in a dictionary. It looks like this:

# the input date 
input_date = pd.to_datetime('04.12.2017 12:00:00', format='%d.%m.%Y %H:%M:%S')

# the dict
dict = {'A': pd.to_datetime('06.12.2017 12:00:00', format='%d.%m.%Y %H:%M:%S'),
        'B': pd.to_datetime('08.11.2017 12:00:00', format='%d.%m.%Y %H:%M:%S'),
        'C': pd.to_datetime('15.10.2017 12:00:00', format='%d.%m.%Y %H:%M:%S'),

# the df
d = {'result':[None,None,None], 
     'id_1':[1,2,3], 'id_2':[10,20,30], 
     'dict_key':['A', 'B', 'A']}
df = pd.DataFrame(d)

My criteria is: If the input date is after the date in the dictonary, take id_1, else take id_2

The result would like this:

    dict_key    id_1    id_2    result
0   A           1       10      10
1   B           2       20      2
2   A           3       30      30

Upvotes: 0

Views: 242

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210862

In [20]: df['result'] = np.where(df.dict_key.map(dct) >= input_date, df['id_2'], df['id_1'])

In [21]: df
Out[21]:
  dict_key  id_1  id_2  result
0        A     1    10      10
1        B     2    20       2
2        A     3    30      30

where dct is a dictionary that is called dict in your question. We should try to avoid overwriting standard keywords (like list,dict, etc.).

Upvotes: 2

jezrael
jezrael

Reputation: 862841

Use:

d1 = {'A': pd.to_datetime('06.12.2017 12:00:00', format='%d.%m.%Y %H:%M:%S'),
        'B': pd.to_datetime('08.11.2017 12:00:00', format='%d.%m.%Y %H:%M:%S'),
        'C': pd.to_datetime('15.10.2017 12:00:00', format='%d.%m.%Y %H:%M:%S')}

d2 = {k:v for k,v in d1.items() if v > input_date}
print (d2)
{'A': Timestamp('2017-12-06 12:00:00')}

df['result'] = np.where(df.dict_key.isin(d2.keys()), df.id_2, df.id_1)
print (df)
  dict_key  id_1  id_2  result
0        A     1    10      10
1        B     2    20       2
2        A     3    30      30

Upvotes: 2

Related Questions