Pandas lesson 2

Question 1

Find out how many sheets are in the excel

Answer 1

len(all_sheets)

Question 2

Create a variable that reads only the sheet name from the 2000s

Answer 2

selected_sheet_only_w_index = pd.read_excel(r’C:\Users\User\Documents\CFG_DATA\Data_files\movies.xls’, sheet_name = 1)

Question 3

Create a variable that reads all sheets from the excel file.

Answer 3

all_sheets = pd.read_excel(r’C:\Users\User\Documents\CFG_DATA\Data_files\movies.xls’, sheet_name = None)

Question 4

How do I find out the number of rows in excel tab 2010

Answer 4

selected_sheet_2010s = pd.read_excel(r’C:\Users\User\Documents\CFG_DATA\Data_files\movies.xls’, sheet_name = ‘2010s’)
len(selected_sheet_2010s)

can also do
len(all_sheets[‘2010s’])

Question 5

Create a new column where you increase the movie budget by 10%. Do this for only the first sheet.

Answer 5

def inc_by_10_pc(num):
return num * 1.10

movies_df[‘incrsd_budget’] = movies_df[‘Budget’].apply(inc_by_10_pc)
movies_df[[‘Budget’, ‘incrsd_budget’]]

Question 5

Create a new column where you decrease the IMDB score by 1. Do this for only the first sheet.

Answer 5

movies_df = first_sheet_only
#adding column - subtract 1 point from IMDB Score

def minus1(num):
return num-1

movies_df[‘Decrease IMDB Score’] = movies_df[‘IMDB Score’].apply(minus1)
movies_df[[‘Decrease IMDB Score’, ‘IMDB Score’]]

Question 6

Group data by year and then calculate mean gross earnings for each year. Use the movies_df

Answer 6

movies_mean = movies_df.groupby(‘Year’)[‘Gross Earnings’].mean()
movies_mean

Question 7

Count number of movies by each country.

Answer 7

count_country = movies_df.groupby(‘Country’)[‘Title’].count()
count_country

Question 8

Create a data series and add an index

emojis = dict(
USA = ‘🇺🇸’,
UK = ‘🇬🇧’,
France = ‘🇫🇷’,
Canada = ‘🇨🇦’,
Australia = ‘🇦🇺’,
Germany = ‘🇩🇪’,
Italy = ‘🇮🇹’,
Japan = ‘🇯🇵’,
Spain = ‘🇪🇸’ )

Answer 8

emojis = dict(
USA = ‘🇺🇸’,
UK = ‘🇬🇧’,
France = ‘🇫🇷’,
Canada = ‘🇨🇦’,
Australia = ‘🇦🇺’,
Germany = ‘🇩🇪’,
Italy = ‘🇮🇹’,
Japan = ‘🇯🇵’,
Spain = ‘🇪🇸’
)

flag_df = pd.Series(emojis, name=’flag’).reset_index()
flag_df

Question 9

Create a new variable, movies_by_country_df where you create a new DataFrame from the series movies_by_country. The column headings should be Title and Title counts.

Answer 9

movies_by_country_df = movies_by_country_count.to_frame().rename(columns={“Title”: “Title counts”})

Question 10

Join the two Dataframes movies_by_country and flag_df (left - Country, right - index)

Answer 10

movies_by_country_df.merge(flag_df, left_on=’Country’, right_on=’index’)

Question 11

Join two Dataframes movies_by_country and flag_df on one column (Country)

Answer 11

movies_by_country_df.merge(flag_df, on=’Country’)