python - A complex transformation of a data set in pandas

Question

I have the following data frame:

dictionary = {'Year': [1985, 1985, 1986, 1986, 1987, 1987], 'Wteam' :[1, 2, 3, 4, 5, 6], 'lteam': [ 9, 10, 11, 12, 13, 14] }
pdf = pd.DataFrame(dictionary)

    Wteam   Year    lteam
0    1      1985    9
1    2      1985    10
2    3      1986    11
3    4      1986    12
4    5      1987    13
5    6      1987    14

I need to create a new data frame in the following format :

team values   predicted_value 
1985_1_9            1
1985_2_10           1
1986_3_11           1 
1986_4_12           1
1987_5_13           1
1987_6_13           1

The values of my new data frame should be in this format "year_Wteam_lteam". How do I do that in pandas. The predicted values column is always 1.

Answer 1

You can do the following :

final = pd.DataFrame()
final['team values'] = pdf['Year'].astype('str') + '_' + pdf['Wteam'].astype('str') + '_' + pdf['lteam'].astype('str')
final['predicted_value'] = 1