CODEWITHSUNDEEP
pythonpandas
Luan Vieira
Luan Vieira

Reputation: 117

How to use map function to get values from multiple columns depending on a column value?

I'm trying to get the seniority from 'Seniority' table based on the name and the date. The Seniority table has columns of the format "Seniority month/year", because it can change from one month to another.

I'm trying:

import pandas as pd
df = pd.DataFrame({'Name': ['Tom', 'Sara', 'Eva', 'Jack', 'Laura'], 
                    'Date': ['1/1/2019', '2/1/2019', '2/20/2019', '2/20/2019', '2/10/2019']})
Seniority = pd.DataFrame({'Name': ['Tom', 'Sara', 'Eva'], 
                          'Col2': ['a','b','c'],
                          'Seniority 1/2019': ['1','2','3'],
                          'Seniority 2/2019': ['2','2','3']})

df.loc[df, 'Seniority'] = \
df['Name'].map(Seniority.set_index('Name')['Seniority ' + pd.DatetimeIndex(df['Date']).month.astype(str) + "/" + pd.DatetimeIndex(df['Date']).year.astype(str) ]).fillna("-")

I get the following error:

TypeError: 'DataFrame' object is not callable.

How can I solve this problem without creating a new column for Date in the Seniority table?

Upvotes: 0

Views: 62

Answers (1)

jezrael
jezrael

Reputation: 863226

Use DataFrame.melt, convert datetimes to month periods by Series.dt.to_period with merge and left join:

sen = Seniority.melt('Name', var_name='Date', value_name='new')
sen['Date'] = (pd.to_datetime(sen['Date'].str.replace('Seniority ', ''), format='%m/%Y')
                 .dt.to_period('m'))
print (sen)
   Name     Date new
0   Tom  2019-01   1
1  Sara  2019-01   2
2   Eva  2019-01   3
3   Tom  2019-02   2
4  Sara  2019-02   2
5   Eva  2019-02   3

df = df.assign(Date= pd.to_datetime(df['Date']).dt.to_period('m')).merge(sen, how='left')
print (df)
    Name     Date  new
0    Tom  2019-01    1
1   Sara  2019-02    2
2    Eva  2019-02    3
3   Jack  2019-02  NaN
4  Laura  2019-02  NaN

EDIT:

You can specify value_vars columns - all columns without Date and Col2 column:

cols = Seniority.columns.difference(['Name','Col2'])
sen = Seniority.melt('Name', var_name='Date', value_name='new', value_vars=cols)
sen['Date'] = (pd.to_datetime(sen['Date'].str.replace('Seniority ', ''), format='%m/%Y')
                 .dt.to_period('m'))
print (sen)
   Name     Date new
0   Tom  2019-01   1
1  Sara  2019-01   2
2   Eva  2019-01   3
3   Tom  2019-02   2
4  Sara  2019-02   2
5   Eva  2019-02   3

Upvotes: 1

Related Questions