<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Join two sheets, groupby and sum on the joined data
Step1:  Change the index to be the showname
Step2:  Do the same for views
DANGER note that battle-star has a hyphen!
Step3:  Join on shows watched against category
PROBLEM - we have NaN values for the last two Firefly entries. Can you do something, earlier on, to fix this, from here inside the Notebook?
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
# load both sheets as new dataframes
shows_df = pd.read_csv("show_category.csv")
views_df = pd.read_excel("views.xls")
Explanation: Join two sheets, groupby and sum on the joined data
End of explanation
shows_df.head()
shows_df = shows_df.set_index('showname')
shows_df.head()
Explanation: Change the index to be the showname
End of explanation
views_df.head()
views_df = views_df.set_index('viewer_id')
views_df.head() # note that we can have repeating viewer_id values (they're non-unique)
# we can select out the column to work on, then use the built-in str (string) functions
# to replace hyphens (we do this and just print the result to screen)
views_df['show_watched'].str.replace("-", "")
# now we do the fix in-place
views_df['show_watched'] = views_df['show_watched'].str.replace("-", "")
# NOTE if you comment out the line above, you'll get a NaN in the final table
# as `battle-star` won't be joined
views_df
print("Index info:", views_df.index)
views_df.ix[22] # select the items with index 22 (note this is an integer, not string value)
Explanation: Do the same for views
DANGER note that battle-star has a hyphen!
End of explanation
shows_views_df = views_df.join(shows_df, on='show_watched')
shows_views_df
# take out two relevant columns, group by category, sum the views
shows_views_df[['views', 'category']].groupby('category').sum()
Explanation: Join on shows watched against category
PROBLEM - we have NaN values for the last two Firefly entries. Can you do something, earlier on, to fix this, from here inside the Notebook?
End of explanation
<END_TASK>