Merging DataFrames

import pandas as pd
import numpy as np

filenames = ['sales_gold.csv', 'sales_silver.csv', 'sales_bronze.csv']

# 1. Loop
dataframes = []
for filename in filenames:
    dataframes.append(pd.read_csv('data/'+filename))

dataframes[0].head()

# 2. comprehension
dataframes = [pd.read_csv('data/'+f) for f in filenames] 

dataframes[0].head()

# 3. glob
from glob import glob

filenames = glob('data/sales*.csv')
dataframes = [pd.read_csv(f) for f in filenames] 

dataframes[0].head()

# combine

filenames

items = dataframes[1].copy()
items.columns = ['item','gold']
items['silver'] = dataframes[2]['total']
items['bronze'] = dataframes[0]['total']
items

items2 = items.set_index('item')
items2

items2.sort_index()

items2.sort_values('gold')

# Reindexing DataFrame from a list

itemlist = ['apple', 'banana', 'berry', 'orange', 'peach', 'mango']
items3 = items2.reindex(itemlist)
items3

# Reindexing using another DataFrame Index

items4 = items3.reindex(items2.index)
items4

df = pd.DataFrame({'Date':['2017-01-10','2017-01-11','2017-01-12'], 'TemperatureF':[32,25,34]})
temps_f = df.set_index('Date')
temps_f

temps_f.info()

temps_c = ((temps_f - 32) * 5/9).round(2)
temps_c.columns = temps_c.columns.str.replace('F', 'C')
temps_c

df1 = pd.DataFrame({'area':['CT', 'MA', 'NY'], 'stores':[320,205,845]})
df_area1 = df1.set_index('area')
df_area1

df2 = pd.DataFrame({'area':['FL', 'GA', 'AL'], 'stores':[90,115,25]})
df_area2 = df2.set_index('area')
df_area2

# append

store = df_area1.append(df_area2)
store

# concat

store = pd.concat([df_area1, df_area2])
store

# Dataframe의 컬럼이 서로 다른 경우

df_area1

df3 = pd.DataFrame({'area':['FL', 'NY', 'AL'], 'members':[90,115,25]})
df_area3 = df3.set_index('area')
df_area3

store = df_area1.append(df_area3)   # ---> index 동일한 것이 있으면 중복.
store

store = pd.concat([df_area1, df_area3], axis=1)    # index 동일한 것은 하나로 묶음.
store

df2015 = df_area1.copy()
df2015

df1 = pd.DataFrame({'area':['CT', 'MA', 'NY'], 'stores':[839,560,745]})
df2016 = df1.set_index('area')
df2016

df_store = pd.concat([df2015, df2016], keys=[2015,2016], axis=0)
df_store

df_store = pd.concat([df2015, df2016], keys=[2015,2016], axis='columns')
df_store

store_dic = {2015:df2015, 2016:df2016}
df_store = pd.concat(store_dic, axis='columns')
df_store

df_area1

df_area3

pd.concat([df_area1, df_area3], axis=1, join='inner')

pd.concat([df_area1, df_area3], axis=1, join='outer')

# pd.merge  :  index가  없는 DataFrame 들을 컬럼 기준으로 합친다.

df1 = pd.DataFrame({'area':['CT', 'MA', 'NY'], 'stores':[320,205,845]})
df1

df2 = pd.DataFrame({'area':['FL', 'NY', 'AL'], 'members':[90,115,25]})
df2

pd.merge(df1, df2)

df2015 = pd.DataFrame({'area':['CT', 'MA', 'NY'], 'stores':[320,205,845]})
df2016 = pd.DataFrame({'area':['CT', 'MA', 'NY'], 'stores':[900,115,250]})

pd.merge(df2015, df2016)

pd.merge(df2015, df2016, on='area')

pd.merge(df2015, df2016, on='area', suffixes=['_2015', '_2016'])

df1 = pd.DataFrame({'area':['CT', 'MA', 'NY'], 'stores':[320,205,845]})
df2 = pd.DataFrame({'state':['CT', 'MA', 'NY'], 'stores':[900,115,250]})

pd.merge(df1, df2, left_on='area', right_on='state')

df2015 = pd.DataFrame({'area':['NY', 'MA', 'CT'], 'stores':[320,205,845]})
df2016 = pd.DataFrame({'area':['CT', 'TX', 'NY'], 'stores':[900,115,250]})

pd.merge(df2015, df2016, on='area', suffixes=['_2015', '_2016'])   # inner

pd.merge(df2015, df2016, on='area', suffixes=['_2015', '_2016'], how='left')

pd.merge(df2015, df2016, on='area', suffixes=['_2015', '_2016'], how='right')

pd.merge(df2015, df2016, on='area', suffixes=['_2015', '_2016'], how='outer')

# Ordered Merge

pd.merge_ordered(df2015, df2016, on='area', suffixes=['_2015', '_2016'], how='outer')