Index Match for multiple column criteria in Python-CodePudding

I have a dataframe, df, where I would like to create a map for multiple columns in Python

Data

id  date     type
aa  q1 2021  aa
bb  q1 2022  aa
cc  q1 2021  aa
cc  q1 2022  aa
cc  q1 2023  bb

Desired

id  date     type  source
aa  q1 2021  aa    10
bb  q1 2022  aa    20
cc  q1 2021  aa    10
cc  q1 2022  aa    20
cc  q1 2023  bb    50

Logic

when we come across the year 2021 and the type aa, the source value will be 10 when we come across the year 2022 and the type aa, the source value will be 20 when we come across the year 2023 and the type bb, the source value will be 50

Doing

map = pd.DataFrame([
    {'id': 'aa', 'date': '2021', 'source': 10},
    {'id': 'bb', 'date': '2022', 'source': 20}
])


df['source'] = df1.type.replace(map,regex=True)

Any suggestion is appreciated

CodePudding user response：

Try with merge:


mapper = pd.DataFrame([{'id': 'aa', 'date': '2021', 'source': 10},
                       {'id': 'aa', 'date': '2022', 'source': 20},
                       {'id': 'bb', 'date': '2023', 'source': 50},
                      ])

df["year"] = df["date"].str.split().str[1]
output = df.merge(mapper.rename(columns={"date": "year", "id": "type"}), how="left").drop("year", axis=1)

>>> output
   id     date type  source
0  aa  q1 2021   aa      10
1  bb  q1 2022   aa      20
2  cc  q1 2021   aa      10
3  cc  q1 2022   aa      20
4  cc  q1 2023   bb      50

CodePudding user response：

You can create a dictionary that maps the tuple (type, date) to your desired value.

Based on your logic, such a dictionary could be:

map = {('aa','2021'): 10, ('aa','2022'):20, ('bb','2023'): 50}

Then you can apply this dictionary to the 'type' and 'date' columns of your DataFrame:

df['source'] = df[['type','date']].apply(lambda x: map[x[0], x[1]],axis=1)

Output:

>>> df
   id  date type  source
0  aa  2021   aa      10
1  bb  2022   aa      20
2  cc  2021   aa      10
3  cc  2022   aa      20
4  cc  2023   bb      50