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)
