Search code examples
pythonpandasdataframepivot-table

Join with a double entry dataframe

I am trying to create a variable based on the amount of years and the scoring of a contract. This double-entry table contains the two main variables:

scoring 1 2 3 4 5 6 7 8 9 10 11
9.3 0.00805% 0.0283431% 0.0283431% 0.0464484% 0.0464484% 0.0464484% 0.1196343% 0.1196343% 0.1562273% 0.1562273% 0.2595363%
9.2 0.0102% 0.0102% 0.0476431% 0.0875944% 0.0875944% 0.0875944% 0.0875944% 0.1434739% 0.1434739% 0.2072495% 0.2072495%
9.1 0.0383297% 0.0835863% 0.0835863% 0.0835863% 0.0835863% 0.0835863% 0.1810753% 0.1810753% 0.2259184% 0.2259184% 0.2259184%

Expected result:

enter image description here

I need to do the same with this database:

import pandas as pd


df = pd.DataFrame({'id':[80887618
,80887619
,80887620
,80887621
],
'years': [9,
1,
5,
12

],
'scoring': [9.3
,9.2
,9.1
,9.3
]
})
Solution

  • IIUC, you unstack the dataframe with the percentages, then merge it into df:

    percentages = (
    percentages.set_index("scoring")
    .unstack()
    .reset_index(name="data")
    .rename(columns={"level_0": "years"})
)

df = df.merge(percentages, how="left")

             id  years  scoring        data
0  80887618      9      9.3  0.1562273%
1  80887619      1      9.2     0.0102%
2  80887620      5      9.1  0.0835863%
3  80887621     12      9.3         NaN