I have two dataframes (taken from pd.to_clipboard(), suggest using pd.read_clipboard())
df_a:
DateField Team FirstVal SecondVal0 1/1/2001 Team1 578 326311 1/1/2001 Team2 570 547182 1/1/2001 Team3 992 160923 1/1/2001 Team4 323 967444 1/1/2001 Team5 591 511035 2/1/2001 Team1 407 851076 2/1/2001 Team2 980 626927 2/1/2001 Team3 668 555238 2/1/2001 Team4 622 818079 2/1/2001 Team5 966 1850510 3/1/2001 Team1 606 4181711 3/1/2001 Team2 547 3921712 3/1/2001 Team3 794 7281913 3/1/2001 Team4 509 4991814 3/1/2001 Team5 318 4999815 4/1/2001 Team1 340 4828316 4/1/2001 Team2 380 2622917 4/1/2001 Team3 307 7924418 4/1/2001 Team4 374 2262619 4/1/2001 Team5 918 99260and df_b:
DateField Team1 Team2 Team3 Team4 Team50 1/1/2001 49 44 95 28 91 1/2/2001 72 37 3 30 852 1/3/2001 47 6 53 93 33 1/4/2001 15 96 11 96 814 1/5/2001 14 95 11 66 125 1/6/2001 29 62 19 96 746 1/7/2001 36 10 25 38 967 1/8/2001 24 39 1 76 448 1/9/2001 9 3 32 2 259 1/10/2001 25 75 52 19 910 1/11/2001 17 67 61 90 3611 1/12/2001 15 17 36 54 13What I am looking to do is add a third column to df_a, say ThirdVal, which contains the value in df_b where the DateField and Team align. My issue is that df_b is transposed and formatted awry compared to df_a.
I have looked into pd.pivot() but have been unsuccessful in pivoting the dataframe to make it joinable, but I am not sure what to fill in for the columns argument. Should I be looking into a different function?
Advertisement
Answer
What you’re looking for is not pivot, but melt.
df_c = df_b.melt(id_vars = 'DateField', value_vars = ['Team1', 'Team2','Team3','Team4','Team5'])Or, for a more general approach, where all columns not specified in id_vars will be used as value_vars:
df_c = df_b.melt(id_vars = 'DateField')Gives you a DataFrame looking like this:
DateField variable value0 1/1/2001 Team1 491 1/2/2001 Team1 722 1/3/2001 Team1 473 1/4/2001 Team1 154 1/5/2001 Team1 145 1/6/2001 Team1 296 1/7/2001 Team1 367 1/8/2001 Team1 248 1/9/2001 Team1 99 1/10/2001 Team1 2510 1/11/2001 Team1 1711 1/12/2001 Team1 1512 1/1/2001 Team2 4413 1/2/2001 Team2 3714 1/3/2001 Team2 615 1/4/2001 Team2 9616 1/5/2001 Team2 9517 1/6/2001 Team2 6218 1/7/2001 Team2 1019 1/8/2001 Team2 3920 1/9/2001 Team2 321 1/10/2001 Team2 7522 1/11/2001 Team2 6723 1/12/2001 Team2 1724 1/1/2001 Team3 9525 1/2/2001 Team3 326 1/3/2001 Team3 5327 1/4/2001 Team3 1128 1/5/2001 Team3 1129 1/6/2001 Team3 1930 1/7/2001 Team3 2531 1/8/2001 Team3 132 1/9/2001 Team3 3233 1/10/2001 Team3 5234 1/11/2001 Team3 6135 1/12/2001 Team3 3636 1/1/2001 Team4 2837 1/2/2001 Team4 3038 1/3/2001 Team4 9339 1/4/2001 Team4 9640 1/5/2001 Team4 6641 1/6/2001 Team4 9642 1/7/2001 Team4 3843 1/8/2001 Team4 7644 1/9/2001 Team4 245 1/10/2001 Team4 1946 1/11/2001 Team4 9047 1/12/2001 Team4 5448 1/1/2001 Team5 949 1/2/2001 Team5 8550 1/3/2001 Team5 351 1/4/2001 Team5 8152 1/5/2001 Team5 1253 1/6/2001 Team5 7454 1/7/2001 Team5 9655 1/8/2001 Team5 4456 1/9/2001 Team5 2557 1/10/2001 Team5 958 1/11/2001 Team5 3659 1/12/2001 Team5 13Which you can then merge with your df_a:
df_a.merge(df_c, how = 'left', left_on = ['DateField','Team'], right_on = ['DateField','variable'])To get output (not missing values are due to left join as I wanted to preserve df_a data, since you mentioned adding a column to it):
DateField Team FirstVal SecondVal variable value0 1/1/2001 Team1 578 32631 Team1 49.01 1/1/2001 Team2 570 54718 Team2 44.02 1/1/2001 Team3 992 16092 Team3 95.03 1/1/2001 Team4 323 96744 Team4 28.04 1/1/2001 Team5 591 51103 Team5 9.05 2/1/2001 Team1 407 85107 6 2/1/2001 Team2 980 62692 7 2/1/2001 Team3 668 55523 8 2/1/2001 Team4 622 81807 9 2/1/2001 Team5 966 18505 10 3/1/2001 Team1 606 41817 11 3/1/2001 Team2 547 39217 12 3/1/2001 Team3 794 72819 13 3/1/2001 Team4 509 49918 14 3/1/2001 Team5 318 49998 15 4/1/2001 Team1 340 48283 16 4/1/2001 Team2 380 26229 17 4/1/2001 Team3 307 79244 18 4/1/2001 Team4 374 22626 19 4/1/2001 Team5 918 99260