Datawhale Pandas Task06

一、2002 年-2018 年上海机动车拍照拍卖

import numpy as np
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
path = 'C:\Users\26591\Desktop\datawhale\pandas\joyful-pandas-master\joyful-pandas\data'
df1 = pd.read_csv(path+'2002年-2018年上海机动车拍照拍卖.csv')
df1.head()

（1）

df1['Auction winning rate'] = df1['Total number of license issued'] / df1['Total number of applicants']
df1.head()
df1[df1['Auction winning rate']<0.05]['Date'].values[0]

（3）

year = []
month = []
for f in list(df1['Date'].values):
    year.append(f.split('-')[0])
    month.append(f.split('-')[1])
df1['Year'] = year
df1['Year'] = df1['Year'].apply(lambda x: '200'+str(x) if len(str(x))==1 else '20'+str(x))
df1['Month'] = month
df1_year_month = df1.reindex(columns=['Year', 'Month', 'Date', 'Total number of license issued', 'lowest price ', 'avg price', 
                     'Total number of applicants', 'Auction winning rate'])
df1_year_month = df1_year_month.drop(columns='Date')
del df1_year_month['Auction winning rate']
df1_year_month.head()

（2）

df1_summary = df1_year_month.groupby('Year')
df1_summary_lowest_price = df1_summary.describe(percentiles=[.75])
df1_summary_lowest_price

（4）

L1 = ['Year']
L2 = ['Total number of license issued', 'lowest price ', 'avg price', 'Total number of applicants']
mul_index = pd.MultiIndex.from_product([L1,L2],names=('Upper', 'Lower'))
pd.pivot_table(df1_year_month,index=['Year'],
               columns=['Month'])