6. Pandas合并数据集

第1关：Concat与Append操作

import pandas as pd

def task1():
    #********** Begin **********#
    data=pd.read_csv('step1/data.csv')   
    data1=pd.read_csv('step1/data1.csv')  
    result=pd.concat([data,data1],axis=1) 
    result=result.T.drop_duplicates().T   
    result.index.name = 'Ladder'          
    result=result.fillna(0)
    #********** End **********#
    return result

第2关：合并与连接

import pandas as pd


def task2(dataset1,dataset2,dataset3):

    # ********** Begin **********#
    d1,d2,d3=pd.DataFrame(dataset1),pd.DataFrame(dataset2),pd.DataFrame(dataset3)
    result=pd.merge(d3,pd.merge(d1,d2,on='user_id',how='outer'),left_on='id',right_on='user_id',how='outer')
    result['user_id']=result['user_id'].fillna(result['id'])
    result['page_click_count_y']=result['page_click_count_y'].fillna(result['page_click_count_x'])
    result['city_x']=result['city_x'].fillna(result['city_y'])
    result=result.drop(['id','page_click_count_x','city_y'],axis=1)
    result.rename(columns={'city_x':'city','page_click_count_y':'page_click_count'},inplace=True)
    result=result.sort_values(by="user_id")
    result['user_id']= result['user_id'].values.astype(int)
    # ********** End **********#
    return result

第3关：案例：美国各州的统计数据

import pandas as pd
import numpy as np

def task3():
    #********** Begin **********#
    #读取三个csv文件
    population=pd.read_csv('./step3/state-population.csv')
    areas=pd.read_csv('./step3/state-areas.csv')
    abbrevs=pd.read_csv('./step3/state-abbrevs.csv')
    # 合并pop和abbrevs并删除重复列
    merged = pd.merge(population, abbrevs, how='outer',
    left_on='state/region', right_on='abbreviation')
    merged = merged.drop('abbreviation', 1)
    # 填充对应的全称
    merged.loc[merged['state/region'] == 'PR','state']='Puerto Rico'
    merged.loc[merged['state/region'] == 'USA','state']='United States'
    # 合并面积数据
    final = pd.merge(merged, areas, on='state', how='left')
    # 删掉这些缺失值
    final.dropna(inplace=True)
    # 取year为2010年的数据，并将索引设为state列
    data2010_1= final.query("year == 2010 & ages == 'total'")
    data2010_2=final.query("year == 2010 & ages == 'under18'")
    
    p=np.array(data2010_1.loc[:,'population'])+np.array(data2010_2.loc[:,'population'])
    data2010=data2010_1.copy()
    data2010.loc[:,'population']=p
    
    data2010.set_index('state', inplace=True)
    # 计算人口密度
    density = data2010['population'] / data2010['area (sq. mi)']
    # 对密度求和
    
    # 对值进行排序
    density.sort_values(ascending=False, inplace=True)
    # 输出人口密度前5名和倒数5名
    print('前5名：')
    print(density[:5])
    print('后5名：')
    print(density[-5:])
    #********** End **********#