def q02_append_row(path): df = q01_load_data(path) df.loc[len(df)] = df.iloc[:, 6:10].sum(axis=0) df.fillna(value=0, inplace=True, axis=0) return df
def q02_append_row(path): df = q01_load_data(path) sum_row = df[['Jan', 'Feb', 'Mar', 'total']].sum() df_sum = pd.DataFrame(data=sum_row).T df_final = df.append(df_sum, ignore_index=True) return df_final
def q02_append_row(path): #'write your solution here' df_final = q01_load_data(path) sum_row = df_final[['Jan', 'Feb', 'Mar', 'total']].sum() df_sum = pd.DataFrame(data=sum_row).T df_sum = df_sum.reindex(columns=df_final.columns) df = df_final.append(df_sum, ignore_index=True) #df_final.sum(numeric_only=True) #df_final.append(df_final.sum(numeric_only=True).T, ignore_index=True) return df
def q02_append_row(path): df = q01_load_data(path) df1 = df[['Jan', 'Feb', 'Mar', 'total']].sum(axis=0) df1 = pd.DataFrame(df1) df2 = pd.DataFrame(columns=['Jan', 'Feb', 'Mar', 'total']) df2['Jan'] = df1.loc['Jan'] df2['Feb'] = df1.loc['Feb'] df2['Mar'] = df1.loc['Mar'] df2['total'] = df1.loc['total'] df3 = df.append(df2, ignore_index=True)
def q02_append_row(path): 'write your solution here' df = q01_load_data(path) a = df['Jan'].sum() b = df['Feb'].sum() c = df['Mar'].sum() d = df['total'].sum() list_new = [[a, b, c, d]] df_final = df.append( pd.DataFrame(list_new, columns=['Jan', 'Feb', 'Mar', 'total'])) return df_final
def q02_append_row(path): 'write your solution here' df = q01_load_data(path) sum_row = df[['Jan', 'Feb', 'Mar', 'total']].sum() #sum for the month and total columns. df_sum = pd.DataFrame( data=sum_row ).T #Converting the series into a dataframe and transposing it df_sum = df_sum.reindex( columns=df.columns) #using reindex to add the missing values df_final = df.append(df_sum, ignore_index=True) return df_final
def q02_append_row(path): df = q01_load_data(path) total_jan = df['Jan'].sum() total_feb = df['Feb'].sum() total_mar = df['Mar'].sum() total_grand = df['total'].sum() df2 = pd.DataFrame(columns=df.columns) df2['Jan'] = [total_jan] df2['Feb'] = [total_feb] df2['Mar'] = [total_mar] df2['total'] = [total_grand] df = df.append(df2, ignore_index=True) return df
def q02_append_row(path): df=q01_load_data(path) t1=pd.DataFrame(df['Jan']).sum() t2=pd.DataFrame(df['Feb']).sum() t3=pd.DataFrame(df['Mar']).sum() t4=pd.DataFrame(df['total']).sum() Total=[t1,t2,t3,t4] df2=pd.DataFrame(columns=df.columns) df2.loc[0]=df2['Jan'].append(t1) df2.loc[0]=df2['Feb'].append(t2) df2.loc[0]=df2['Mar'].append(t3) df2.loc[0]=df2['total'].append(Total) frames=[df,df2] frame=pd.concat(frames,ignore_index=True) return frame
def q09_pie_chart_jan(path1, path2): df_02 = q01_load_data(path1) df_02.loc[len(df_02)] = df_02.iloc[:, 6:10].sum(axis=0) df_02.fillna(value=0, inplace=True, axis=0) df_03 = pd.read_csv(path2) mapping = dict( zip(df_03['United States of America'].str.lower(), df_03['Unnamed: 6'])) df_02.insert(loc=6, column='abbr', value='') df_02.iloc[:, 6] = df_02['state'].map(mapping) df_02.iloc[6, 6] = 'MS' df_02.iloc[10, 6] = 'TN' return df_02[['Jan', 'Feb', 'Mar', 'total']].Jan.value_counts().plot(kind='pie', y='Jan')
def q04_mapping(path1, path2): df_02 = q01_load_data(path1) df_02.loc[len(df_02)] = df_02.iloc[:, 6:10].sum(axis=0) df_02.fillna(value=0, inplace=True, axis=0) #df_02 = q02_append_row(path1) df_03 = pd.read_csv(path2) mapping = dict( zip(df_03['United States of America'].str.lower(), df_03['Unnamed: 6'])) df_02.insert(loc=6, column='abbr', value='') df_02.iloc[:, 6] = df_02['state'].map(mapping) return df_02
def q02_append_row(path): data_set = q01_load_data(path) ''' Approach 1 ''' data_set.at['Grand Total', 'Jan'] = data_set['Jan'].sum() data_set.at['Grand Total', 'Feb'] = data_set['Feb'].sum() data_set.at['Grand Total', 'Mar'] = data_set['Mar'].sum() data_set.at['Grand Total', 'total'] = data_set['total'].sum() ''' Approach 2 ''' #data_set.loc['Grand Total', 'Jan'] = data_set['Jan'].sum() #data_set.loc['Grand Total', 'Feb'] = data_set['Feb'].sum() #data_set.loc['Grand Total', 'Mar'] = data_set['Mar'].sum() #data_set.loc['Grand Total', 'total'] = data_set['total'].sum() return data_set
def q07_symbols(path1, path2): 'write your solution here' df_02 = q01_load_data(path1) df_02.loc[len(df_02)] = df_02.iloc[:, 6:10].sum(axis=0) df_02.fillna(value=0, inplace=True, axis=0) df_03 = pd.read_csv(path2) mapping = dict( zip(df_03['United States of America'].str.lower(), df_03['Unnamed: 6'])) df_02.insert(loc=6, column='abbr', value='') df_02.iloc[:, 6] = df_02['state'].map(mapping) df_02.iloc[6, 6] = 'MS' df_02.iloc[10, 6] = 'TN' df_sub = df_02.groupby(['abbr'])[[ 'account', 'Jan', 'Feb', 'Mar', 'total' ]].sum().applymap(lambda x: '$%s' % '{:,}'.format(int(x))) return df_sub
def q06_sub_total(path1, path2): 'write your solution here' df_02 = q01_load_data(path1) df_02.loc[len(df_02)] = df_02.iloc[:, 6:10].sum(axis=0) df_02.fillna(value=0, inplace=True, axis=0) df_03 = pd.read_csv(path2) mapping = dict( zip(df_03['United States of America'].str.lower(), df_03['Unnamed: 6'])) df_02.insert(loc=5, column='abbr', value='') df_02.iloc[:, 6] = df_02['state'].map(mapping) df_02.iloc[6, 6] = 'MS' df_02.iloc[10, 6] = 'TN' #df_02 = q05_replace_missing_values(path1,path2) df_sub = df_02.groupby(['postal-code'])[['account', 'Jan', 'Feb', 'Mar']].sum() return df_sub
# %load q02_append_row/build.py import pandas as pd import sys, os #sys.path.append(os.path.join(os.path.dirname(os.curdir))) from greyatomlib.pandas_guided_project.q01_load_data.build import q01_load_data df = q01_load_data(path='data/excel-comp-data.xlsx') def q02_append_row(path='data/excel-comp-data.xlsx'): a = df.append(df[['Jan', 'Feb', 'Mar', 'total']].sum(), ignore_index=True) return a # a=df.append([df[['Jan','Feb','Mar']].sum()],ignore_index=True)<--This is how you create a new row in a dataframe c = q02_append_row(path='data/excel-comp-data.xlsx') c
def q02_append_row(path): df = q01_load_data(path) col_list = ['Jan', 'Feb', 'Mar', 'total'] df_inter = df[col_list].sum(axis=0).to_frame().T df_final = pd.concat([df, df_inter]) return df_final
def q02_append_row(path): 'write your solution here' df = q01_load_data(path) return df.append(df.sum(numeric_only=True), ignore_index=True)