pandasでよく使う操作

# Pre-defined lists
names = ['United States', 'Australia', 'Japan', 'India', 'Russia', 'Morocco', 'Egypt']
dr =  [True, False, False, False, True, True, True]
cpc = [809, 731, 588, 18, 200, 70, 45]

# Import pandas as pd
import pandas as pd

# Create dictionary my_dict with three key:value pairs: my_dict
my_dict = {
    'country': names,
    'drive_right': dr,
    'car_per_cap': cpc
}

# Build a DataFrame cars from my_dict: cars
cars = pd.DataFrame(my_dict)

# Definition of row_labels
row_labels = ['US', 'AUS', 'JPN', 'IN', 'RU', 'MOR', 'EG']
# Specify row labels of cars
cars.index = row_labels

scores_df = pd.DataFrame({'点数': scores},
                        index=pd.Index(['A', 'B', 'C', 'D', 'E',
                                        'F', 'G', 'H', 'I', 'J'],
                                        name='生徒'))

frame3.index.name = 'year'; frame3.columns.name = 'state'
frame3
>>>
state	Nevada	Ohio
year		
2000	NaN	1.5
2001	2.4	1.7
2002	2.9	3.6

df_list = []
for filename in tqdm(["[JST_OrderManagement_His1].rpt.pkl.gz",
                      "[JST_OrderManagement_His2].rpt.pkl.gz",
                      "[JST_OrderManagement_His3].rpt.pkl.gz"]):
    df = pd.read_pickle("../data/raw/pkl/" + filename)
    df_list.append(df[:-1])

JST_OrderManagement_His_df = pd.concat(df_list,
                                       axis=0,
                                       ignore_index=True)

# Fix import by including index_col
cars = pd.read_csv('cars.csv')

# Fix import by including index_col
cars = pd.read_csv('cars.csv', index_col=0)

# index_colを指定
medal_df = pd.read_csv(file_name, header=0, index_col='Country', names=columns)

df_list = []
for filename in tqdm(["[JST_OrderManagement_His1].rpt.pkl.gz",
                      "[JST_OrderManagement_His2].rpt.pkl.gz",
                      "[JST_OrderManagement_His3].rpt.pkl.gz"]):
    df = pd.read_pickle("../data/raw/pkl/" + filename)
    df_list.append(df[:-1])

JST_OrderManagement_His_df = pd.concat(df_list,
                                       axis=0,
                                       ignore_index=True)

# Excelファイルを読み込む
xls = pd.ExcelFile('data/' + FILE_NAME)

# 取り込み対象から除外できるデータがないかデータを見て確認する
# skiprows:先頭の何行を読み込まないかを指定する引数
# skip_footer:最後の何行を読み込まないか指定する引数
# usecols:読み込む列を指定
df = xls.parse('二人',
                skiprows = 10,
                skip_footer = 2,
                usecols = "H:CG")

obj = pd.Series([4.5, 7.2, -5.3, 3.6], index=['d', 'b', 'a', 'c'])
obj2 = obj.reindex(['a', 'b', 'c', 'd', 'e'])
obj2
>>>
a   -5.3
b    7.2
c    3.6
d    4.5
e    NaN
dtype: float64

order.rename(index=order['TARGET_DATE'])

df.rename(columns={ 0: 'monthly'})

> print(cars.reset_index())
   index        country  drive_right  car_per_cap
0      0  United States         True          809
1      1      Australia        False          731
2      2          Japan        False          588
3      3          India        False           18
4      4         Russia         True          200
5      5        Morocco         True           70
6      6          Egypt         True           45

> print(cars.reset_index(drop=True))
         country  drive_right  car_per_cap
0  United States         True          809
1      Australia        False          731
2          Japan        False          588
3          India        False           18
4         Russia         True          200
5        Morocco         True           70
6          Egypt         True           45

cars.sort_values(['car_per_cap']).reset_index(drop=True)
         country  drive_right  car_per_cap
0          India        False           18
1          Egypt         True           45
2        Morocco         True           70
3         Russia         True          200
4          Japan        False          588
5      Australia        False          731
6  United States         True          809

# 項目を指定してソート
df.sort_values(by=["項目1"], ascending=True)
# 項目1を昇順、項目2を降順
df.sort_values(by=["項目1", "項目2"], ascending=[True,False])

result.loc[ ['[18, 31)', '[31, 42)', '[42, 53)', '[53, 64)', '[64, 75)', '75+']]

daily	weekly	monthly
age_group			
[18, 31)	2.59	2.89	3.21
[31, 42)	12.11	12.92	13.72
[42, 53)	24.42	24.38	24.81
[53, 64)	25.29	25.01	24.84
[64, 75)	27.48	26.94	26.05
75+	8.11	7.86	7.37

pd.DataFrame(data, columns=['year', 'state', 'pop'])

# left_on, right_onでそれぞれのデータフレームの列名を指定する
pd.merge(df_ab, df_ac_, left_on='a', right_on='a_')

# 項目名を指定して対象項目のみを抽出(以下2つは同じ結果になる)
 >>>df['name']
 >>>df.name
 # 複数の列にアクセスするには列の名前のリストを渡す
 >>>df[['name', 'age']]
 # locを使っても大丈夫
 >>>df.loc[:, ['name', 'age']]
 # 条件を複数指定したいときはisinを使う
 df[df['city'].isin(['Tokyo', 'Osaka']]
 # 表全体ではなく、特定の列からサブセットを取り出すこともできる(生存者のうち年齢が30以上)
 df['survived'][df['age'] >= 30]

# Print out country column as Pandas Series
print(type(cars['country']))

# Print out country column as Pandas DataFrame
print(type(cars[['country']]))

# Print out first 3 observations
print(cars[0:3])

# Print out fourth, fifth and sixth observation
print(cars[3:6])

# Seriesを抽出
cars.loc['RU']
cars.iloc[4]

# DataFrameを抽出
cars.loc[['RU']]
cars.iloc[[4]]

# DataFrameで複数の項目を抽出
cars.loc[['RU', 'AUS']]
cars.iloc[[4, 1]]

# rowとcolumnの両方を指定して1つの項目を抽出
cars.loc['IN', 'cars_per_cap']
cars.iloc[3, 0]

# 複数のrowと一つのcolumnを指定
cars.loc[['IN', 'RU'], 'cars_per_cap']
cars.iloc[[3, 4], 0]

# 複数のrowと複数のcolumnを指定
cars.loc[['IN', 'RU'], ['cars_per_cap', 'country']]
cars.iloc[[3, 4], [0, 1]]

# 項目を指定して全ての行をSereisでセレクト
cars.loc[:, 'country']
cars.iloc[:, 1]

# 複数の項目を指定して全ての行をDataFrameでセレクト
cars.loc[:, ['country','drives_right']]
cars.iloc[:, [1, 2]]

 # trueとfalseのステートメントを使ってサブセットを取り出せる
 df[df['age'] >= 30]
 df[df['city'] == 'Tokyo']
 # 応用例。booleanのSeriesを作って、条件を満たすデータからソート。
 atleast_100 = movie_stats['rating']['size'] >= 100
 movie_stats[atleast_100].sort_values(by=[('rating', 'mean')],
                                     ascending=False)[:15]
                                     
 >>>football[(football.wins > 10) & (football.team == "Packers")]
    losses     team  wins  year
 3       1  Packers    15  2011
 4       5  Packers    11  2012 

# nullの判定を実施
df['SAMPLE'].isnull()

# nullの行だけを抽出
df[df['SAMPLE'].isnull()]

(df[df['state'].isin(['NY', 'TX'])])

# 条件の中で変数を使う
val = 80
df.query('point > @val')

# 複数条件を指定
df.query('age < 25 & point > 65')
df.query('age < 25 and point > 65')

order_ts_dropped = order_ts.drop("TARGET_DATE", axis=1)

# NaNが1つでもある行を削除
df.dropna()
# 全ての列がNaNの行を削除
df.dropna(how='all')


# 項目を指定して対象項目がNaNの場合のみ除外
df.dropna(subset=['CHANNEL_KBN'])

# Add total col as sum of individuals and family_members
homelessness['total'] = homelessness['individuals'] + homelessness['family_members']

# Add p_individuals col as proportion of individuals
homelessness['p_individuals'] = homelessness['individuals'] / homelessness['total']

# See the result
print(homelessness)

s_f = s.astype('float64')
print(s_f)
# 0    13.0
# 1    23.0
# 2    33.0
# Name: c, dtype: float64

print(s)
# 0    13
# 1    23
# 2    33
# Name: c, dtype: int64

# Iterate over rows of cars
for lab, row in cars.iterrows():
    print(lab)
    print(row)
>>>
US
cars_per_cap              809
country         United States
drives_right             True
Name: US, dtype: object

# iterrowsを使って新しい項目を作ることもできる
# Code for loop that adds COUNTRY column
for lab, row in cars.iterrows() :
    cars.loc[lab, 'COUNTRY'] = row["country"].upper()

# Responseという項目について、値がNoなら0、それ以外なら1を設定した項目を追加
df['Engaged'] = df['Response'].apply(lambda x: 0 if x == 'No' else 1)

df['age_group'] = df['age'].apply(
    lambda x: '[18, 30)' if x < 30 else '[30, 40)' if x < 40 \
        else '[40, 50)' if x < 50 else '[50, 60)' if x < 60 \
        else '[60, 70)' if x < 70 else '70+'
)

def func_cate(x):
    if  x < 10000:
        return 0
    elif x >= 10000 and x < 15000:
        return 1
    elif x >= 15000 and x < 19500:
        return 2
    else:
        return 3
stocks['cate'] = stocks['open'].apply(func_cate)

def func_cate_change(x):
    if x.open < x.close:
        return 1
    elif x.open > x.close:
        return 2
    else:
        return 0
stocks['cate_change'] = stocks.apply(lambda x:func_cate_change(x),axis=1)

gender_values, gender_labels = df['Gender'].factorize()
gender_values # array([0, 0, 0, ..., 1, 1, 1])
gender_labels # Index(['F', 'M'], dtype='object')

categories = pd.Categorical(
    df['Education'],
    categories=['High School or Below', 'Bachelor', 'College', 'Master', 'Doctor']
)

sales['pd_date'] = pd.to_datetime(sales['date2'], format='%m/%d/%Y')

> order_by_week.sum()
loginID
00076289    1473.0
0011C8B3       2.0
0013AF4C      13.0
0017472F      12.0
001D1FC7     229.0

> order_by_week.sum(axis=1)
TARGET_DATE
2020-03-11    269191.0
2020-03-18    211634.0
2020-03-25    275430.0
2020-04-01    252704.0
2020-04-08    243389.0

df_percent = temp_df.iloc[:, [0,1]].apply(lambda x: round(x / x.sum() * 100, 2), axis = 1)

JST_OrderManagement_His_df["loginID"].unique().shape

# ユーザごとの平均評価点を求める
lens.groupby('user_id').mean()
# 映画ごとの平均評価点を求める
lens.groupby('movie_id').mean()
# ユーザごとに通番の番号をふる(0から始まるので+1する)
lens.groupby('user_id').cumcount()+1

# 全体に同じ関数を適用
>>> df.groupby('city').agg(np.mean)
price	quantity
city		
osaka	212.5	2.5
tokyo	250.0	6.0

# グループごとに適用する関数を指定
>>> df.groupby('city').agg({'price': np.mean, 'quantity': np.sum})
   price	quantity
city		
osaka	212.5	10
tokyo	250.0	18
# 同じグループに複数の関数を指定
>>> movie_stats = lens.groupby('title').agg({'rating': [np.size, np.mean]})
rating
size	mean
title		
'Til There Was You (1997)	9	2.333333
1-900 (1994)	5	2.600000

decade = 10 * (planets['year'] // 10)
decade = decade.astype(str) + 's'
decade.name = 'decade'
planets.groupby(['method', decade])['number'].sum().unstack().fillna(0)

age_marital_df = df.groupby(['age_group', 'marital'])['conversion'].sum().unstack('marital').fillna(0)

age_marital_df = age_marital_df.divide(
    df.groupby(
        by='age_group'
    )['conversion'].count(), 
    axis=0
)

df11=df.groupby('key1')['key2'].apply(lambda x: (x=='one').sum()).reset_index(name='count')

# groupby
titanic.groupby(['sex', 'class'])['survived'].aggregate('mean').unstack()
# pivot_table
titanic.pivot_table('survived', index='sex', columns='class')

age = pd.cut(titanic['age'], [0, 18, 80])
titanic.pivot_table('survived', ['sex', age], 'class')

titanic.pivot_table(index='sex', columns='class',
                    aggfunc={'survived':sum, 'fare': 'mean'})

titanic.pivot_table('survived', index='sex', columns='class', margins=True)

index = pd.DatetimeIndex(['2014-07-04', '2014-08-04',
                          '2015-07-04', '2015-08-04'])
data = pd.Series([0, 1, 2, 3], index=index)
data['2014-07-04':'2015-07-04']

order['TARGET_DATE'] = pd.to_datetime(order['TARGET_DATE'], format='%Y/%m/%d')
order_ts = order.rename(index=order['TARGET_DATE'])

df['RecordDate_dt'] = pd.to_datetime(df['RecordDate'], format='%Y%m%d')
start = 20200311
end = 20200609

df.query('RecordDate_dt >= @start & RecordDate_dt <= @end')

df1[(df1['日付'] >= dt.datetime(2019,5,3)) & (df1['日付'] < dt.datetime(2019,5,6))]

# 年にアクセス
df['date'].dt.year

# 月にアクセス
df['date'].dt.month

# 日付
df['InvoiceDate'].dt.date

uri_df.iplot(title="注文金額推移(売り)")
kai_df.iplot(title="注文金額推移(買い)")

# 日次, 週次, 月次ごとの注文金額合計と注文頻度の推移
for symbol, name in zip(["D", "W", "M"],
                        ["日次", "週次", "月次"]):
    sum_df = pd.concat([uri_df.resample(symbol).sum(), 
                        kai_df.resample(symbol).sum()],
                       axis=1).fillna(0)
    print("注文合計額平均({})".format(name))
    print(sum_df.mean())
    sum_df.iplot(title="注文金額推移({}合計)".format(name),
                                     kind="spread")
    
    count_df = pd.concat([uri_df.resample(symbol).count(), 
                          kai_df.resample(symbol).count()],
                         axis=1).fillna(0)
    print("注文頻度平均({})".format(name))
    print(count_df.mean())
    count_df.iplot(title="注文頻度推移({}合計)".format(name),
                   kind="spread")

display(df)   

pd.DataFrame(extracted_df['count']).resample('D').sum()

# 元データ
data_resample
>>>
	BRK-A	^GSPC
Date		
2009-12-31	99200.000000	1115.099976
2010-01-31	102636.315789	1123.581582

# shiftの結果
data_resample.shift(1)
>>>
    BRK-A	^GSPC
Date		
2009-12-31	NaN	NaN
2010-01-31	99200.000000	1115.099976

# shiftと組み合わせて変化率の取得
np.log(data_resample/data_resample.shift(1))
>>>
 	BRK-A	^GSPC
Date		
2009-12-31	NaN	NaN
2010-01-31	0.034054	0.007577
2010-02-28	0.109991	-0.031115

# groupbyに適用するケース
df.groupby(pd.Grouper(freq='2W')).count()

# pivot_tableに適用するケース
df.pivot_table(index=pd.Grouper(freq='2W'), columns='label', aggfunc={'id': 'count'})

for index, x_data in x.iterrows():
     point = 0
     if x_data[10910] > 0:
         point += 0.5
         
     if x_data[10940] > 0:
         point += 0.5
         
     if x_data[10960] > 0:
         point += 0.5
     
     if x_data[11000] > 0:
         point += 0.5
         
     # x_data['target'] = point
     x.loc[index,'target'] = point
     print(index, x_data['target'])

df_7203.pivot_table(values=['value'], index=['base_date'], columns=['Tag_Summary'], aggfunc='sum')

import pandas as pd

>>> df = pd.DataFrame({'a': range(1, 6),
                       'b': [x**2 for x in range(1, 6)],
                       'c': [x**3 for x in range(1, 6)]})
>>> df
	a	b	c
0	1	1	1
1	2	4	8
2	3	9	27
3	4	16	64
4	5	25	125

>>> df['shift'] = df['a'].shift().fillna(0)
>>> df
	a	b	c	shift
0	1	1	1	0.0
1	2	4	8	1.0
2	3	9	27	2.0
3	4	16	64	3.0
4	5	25	125	4.0

import pandas as pd
import pandas.io.sql as psql
 
connect = mysql.connector.connect(
     user=user, password=password, host=rdshost, db=db_name)
 
sql = 'SELECT * From Edinet_Api_Return'
data = psql.read_sql(sql, connect)

order = order[order['type'].astype(str).str[:1] == '1']

# 最後以外の行を抽出
df = df[:-1]

# 最後の行を削除
df.drop(df.tail(1).index)

f = df_group.pivot_table(index=pd.Grouper(key="約定年月日",freq="6M"),
               columns='no', values='約定金額', aggfunc='count').fillna(0)

def max_day(p):
    return max(p)

# KeyErrorになる
df_group.pivot_table(index=pd.Grouper(key="約定年月日",freq="6M"),
           columns='no', values='約定年月日', aggfunc=max_day)
>>>
KeyError['約定年月日']


# こっちはうまくいく
df_group['約定年月日_temp'] = df_group['約定年月日'] 
df_group.pivot_table(index=pd.Grouper(key="約定年月日",freq="6M"),
           columns='no', values='約定年月日_temp', aggfunc=max_day)

df['days'] = (df['date'] - today).dt.days

f_multi = pd.DataFrame(f_score.stack()).rename(columns={0: 'F'})
m_multi = pd.DataFrame(m_score.stack()).rename(columns={0: 'M'})
r_multi = pd.DataFrame(r_score.stack()).rename(columns={0: 'R'})

temp = m_multi.join(f_multi)
temp = temp.join(r_multi)

temp_df_10['sample'].apply(lambda x: 'NaNです' if(pd.isnull(x)) else x)