one dataframe as
first = pd.DataFrame({'Code': [1,2,3,4],'Value': ['Apple','Ornage','Bannana', 'Graps']})
and another data frame is
second= pd.DataFrame({'Code': ['1','2','1','2,4','3'],'Product id': ['A','B','C', 'D','E']})
I required the code replace with value as in the third table column 'Required field'
in python coding. as dataframe
third= pd.DataFrame({'Code': ['1','2','1','2,4','3'],'Product id': ['A','B','C', 'D','E'],'Required Field':['Apple(1)','Orange(2)','Apple(1)','Orange(2),Graps(4)','Bannana(3)']})
CodePudding user response:
Try:
second["Code"] = second["Code"].str.split(",")
second = second.explode("Code")
first["Code"] = first["Code"].astype(str)
second["Code"] = second["Code"].astype(str)
third = pd.merge(first, second, on="Code")
third["Value"] = third.apply(lambda x: f"{x['Value']}({x['Code']})", axis=1)
print(
third.groupby("Product id", as_index=False)
.agg({"Code": ",".join, "Value": ",".join})
.rename(columns={"Value": "Required Field"})
)
Prints:
Product id Code Required Field
0 A 1 Apple(1)
1 B 2 Orange(2)
2 C 1 Apple(1)
3 D 2,4 Orange(2),Graps(4)
4 E 3 Bannana(3)
CodePudding user response:
>>> df1=pd.DataFrame({"code": [1,2,3,4], "value": ["apple", "orange", "banana", "grapes"]})
>>> df1
code value
0 1 apple
1 2 orange
2 3 banana
3 4 grapes
>>> df2=pd.DataFrame({"id":['A','B','C','D','E'], "code": ["1","2","1","2,4","3"]})
>>> df2
id code
0 A 1
1 B 2
2 C 1
3 D 2,4
4 E 3
transform the string to list and map the values from the other dataframe:
>>> df2['code']=df2['code'].str.split(',').apply(lambda x: df1.set_index('code').loc[[int(i) for i in x]].value.tolist())
>>> df2
id code
0 A [apple]
1 B [orange]
2 C [apple]
3 D [orange, grapes]
4 E [banana]