Q:

Joining 2 datasets in pandas

Hi everyone,
I'm having trouble joining 2 datatsets in Pandas. The 1st one contains how much each type of house was sold for in set month of the year, and the 2nd contains how many houses of each type were built in each year. I'm trying to combine the 2 in showing on each row: the year, the house type, an average price over the 12 months and how many houses of that type were built in that year. An issue is that years are shown in rows in the 1st dataset and columns in the 2nd, and i'm not sure how to deal with that. Another issue is trying to get the average price over the 12 months. I added a column at the end with average prices, but that included the year as well which is not good. Also, not sure how to get only the average price, without adding every single month to the dataset.
Any suggestions appreciated, thanks.
Dataset 1 contains sold houses:
Year type          Jan     Feb    Mar ... Dec
2016  Oakham       160000  165000
2016  Alvingham    155000  150000
2017  Oakham
2017  Alvingham

Dataset 2 contains houses built:
               2015    2016    2017
Oakham          150     200     250
Alvingham        50      85
Chesam

Expected output would be something like this:
YEAR    TYPE       AVERAGE PRICE       AMOUNT BUILT
2016    Oakham            175500                200
2017    Chesam            325000                 20

A:

Here's one solution:
df1:
      Feb     Jan  Year       type
0  165000  160000  2016     Oakham
1  155000  150000  2016  Alvingham
2  165000  165000  2017     Oakham
3  165000  160000  2017  Alvingham

df2:
           2015  2016  2017
Oakham      150   200   250
Alvingham    50    85    80

First of all you have to calculate de average of price columns only:
df1['avg'] = df1.drop(['type', 'Year'], axis=1).mean(axis=1)
print(df1)
      Feb     Jan  Year       type       avg
0  165000  160000  2016     Oakham  162500.0
1  155000  150000  2016  Alvingham  152500.0
2  165000  165000  2017     Oakham  165000.0
3  165000  160000  2017  Alvingham  162500.0

Now you just have to merge df1 with the unstacked version of df2:
df3 = pd.merge(left=df1[['type', 'Year', 'avg']],
               right=df2.unstack().reset_index(),
               left_on=['type', 'Year'],
               right_on=['level_1','level_0'])

The output of print(df3) is:
        type  Year       avg  level_0    level_1    0
0     Oakham  2016  162500.0     2016     Oakham  200
1  Alvingham  2016  152500.0     2016  Alvingham   85
2     Oakham  2017  165000.0     2017     Oakham  250
3  Alvingham  2017  162500.0     2017  Alvingham   80

Then you can drop unwanted columns and rename the others:
print(df3.drop(['level_0', 'level_1'],
                axis=1).rename(columns={0: 'AMOUNT BUILT',
                                        'avg': 'AVERAGE PRICE'}))

The result is:
        type  Year  AVERAGE PRICE  AMOUNT BUILT
0     Oakham  2016       162500.0           200
1  Alvingham  2016       152500.0            85
2     Oakham  2017       165000.0           250
3  Alvingham  2017       162500.0            80

