As per below details, dataframe has company-wise numbers. Dict has custom weightages. Company 'A' has 7 rows, so I would like to fetch custom weightages from dict with key as 7 and create 'custom_weights' as a new column. Latest date will have highest weightage.
Similarly for Company 'B' and 'C', I need to fetch and attach weightages with key as 4 and 2 respectively (number of rows per company). These weightages to be aligned into the 'custom_weights' column.
Please suggest how this problem can be solved? Thank you in advance.
df=pd.DataFrame(columns=['CompanyName','Date_Published','Stand_Alone','Consolidated'],data=[('A','31-03-2017',np.random.rand(),np.random.rand()),('A','31-03-2016',np.random.rand(),np.random.rand()),('A','31-03-2015',np.random.rand(),np.random.rand()),('A','31-03-2014',np.random.rand(),np.random.rand()),('A','31-03-2013',np.random.rand(),np.random.rand()),('A','31-03-2012',np.random.rand(),np.random.rand()),('A','31-03-2011',np.random.rand(),np.random.rand()),('B','31-03-2017',np.random.rand(),np.random.rand()),('B','31-03-2016',np.random.rand(),np.random.rand()),('B','31-03-2015',np.random.rand(),np.random.rand()),('B','31-03-2014',np.random.rand(),np.random.rand()),('C','31-03-2017',np.random.rand(),np.random.rand()),('C','31-03-2016',np.random.rand(),np.random.rand())])
dict_wt.update({2:[55.55,44.45]})
dict_wt.update({3:[47.34,31,56,21,11]})
dict_wt.update({7:[21.63, 18.54, 15.89, 13.62, 11.68, 10.01, 8.63]})
Use groupby() to count the number of ['CompanyNames'] and assign the list from the dictionary back to the dataframe using transform()
df['CustomWeights'] = df.groupby('CompanyName')['Date_Published'].transform(lambda x: dict_wt.get(len(x)))
CompanyName Date_Published Stand_Alone Consolidated CustomWeights
0 A 31-03-2017 0.116712 0.044908 21.63
1 A 31-03-2016 0.228525 0.553351 18.54
2 A 31-03-2015 0.476527 0.913417 15.89
3 A 31-03-2014 0.989796 0.716775 13.62
4 A 31-03-2013 0.702358 0.880009 11.68
5 A 31-03-2012 0.531666 0.013267 10.01
6 A 31-03-2011 0.896103 0.351544 8.63
7 B 31-03-2017 0.405370 0.701944 11.00
8 B 31-03-2016 0.858221 0.450118 56.00
9 B 31-03-2015 0.163273 0.613447 21.00
10 B 31-03-2014 0.635888 0.570327 11.00
11 C 31-03-2017 0.680992 0.488191 55.55
12 C 31-03-2016 0.083883 0.682186 44.45