5. Manipulating DataFrame Structure
การจัดโครงสร้าง Dataframe
Configuring pandas
# import numpy and pandas
import numpy as np
import pandas as pd
# used for dates
import datetime
from datetime import datetime, date
# Set some pandas options controlling output format
pd.set_option('display.notebook_repr_html', False)
pd.set_option('display.max_columns', 7)
pd.set_option('display.max_rows', 10)
pd.set_option('display.width', 60)
# read in the data and print the first five rows
# use the Symbol column as the index, and
# only read in columns in positions 0, 2, 3, 7
sp500 = pd.read_csv("data/sp500.csv",
index_col='Symbol',
usecols=[0, 2, 3, 7])Renaming columns
# rename the Book Value column to not have a space
# this returns a copy with the column renamed
newSP500 = sp500.rename(columns=
{'Book Value': 'BookValue'})
# print first 2 rows
newSP500[:2] Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573# verify the columns in the original did not change
sp500.columnsIndex(['Sector', 'Price', 'Book Value'], dtype='object')# this changes the column in-place
sp500.rename(columns=
{'Book Value': 'BookValue'},
inplace=True)
# we can see the column is changed
sp500.columnsIndex(['Sector', 'Price', 'BookValue'], dtype='object')# and now we can use .BookValue
sp500.BookValue[:5]Symbol
MMM 26.668
ABT 15.573
ABBV 2.954
ACN 8.326
ACE 86.897
Name: BookValue, dtype: float64Adding new columns with [] and .insert()
# make a copy so that we keep the original data unchanged
sp500_copy = sp500.copy()
# add the new column
sp500_copy['RoundedPrice'] = sp500.Price.round()
sp500_copy[:2] Sector Price BookValue RoundedPrice
Symbol
MMM Industrials 141.14 26.668 141.0
ABT Health Care 39.60 15.573 40.0# make a copy so that we keep the original data unchanged
copy = sp500.copy()
# insert sp500.Price * 2 as the
# second column in the DataFrame
copy.insert(1, 'RoundedPrice', sp500.Price.round())
copy[:2] Sector RoundedPrice Price BookValue
Symbol
MMM Industrials 141.0 141.14 26.668
ABT Health Care 40.0 39.60 15.573Adding columns through enlargement
# copy of subset / slice
ss = sp500[:3].copy()
# add the new column initialized to 0
ss.loc[:,'PER'] = 0
# take a look at the results
ss Sector Price BookValue PER
Symbol
MMM Industrials 141.14 26.668 0
ABT Health Care 39.60 15.573 0
ABBV Health Care 53.95 2.954 0# copy of subset / slice
ss = sp500[:3].copy()
# add the new column initialized with random numbers
np.random.seed(123456)
ss.loc[:,'PER'] = pd.Series(np.random.normal(size=3), index=ss.index)
# take a look at the results
ss Sector Price BookValue PER
Symbol
MMM Industrials 141.14 26.668 0.469112
ABT Health Care 39.60 15.573 -0.282863
ABBV Health Care 53.95 2.954 -1.509059Adding columns using concatenation
# create a DataFrame with only the RoundedPrice column
rounded_price = pd.DataFrame({'RoundedPrice':
sp500.Price.round()})
# concatenate along the columns axis
concatenated = pd.concat([sp500, rounded_price], axis=1)
concatenated[:5] Sector Price BookValue \
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954
ACN Information Technology 79.79 8.326
ACE Financials 102.91 86.897
RoundedPrice
Symbol
MMM 141.0
ABT 40.0
ABBV 54.0
ACN 80.0
ACE 103.0 # create a DataFrame with only the RoundedPrice column
rounded_price = pd.DataFrame({'Price': sp500.Price.round()})
rounded_price[:5] Price
Symbol
MMM 141.0
ABT 40.0
ABBV 54.0
ACN 80.0
ACE 103.0# this will result in duplicate Price columm
dups = pd.concat([sp500, rounded_price], axis=1)
dups[:5] Sector Price BookValue Price
Symbol
MMM Industrials 141.14 26.668 141.0
ABT Health Care 39.60 15.573 40.0
ABBV Health Care 53.95 2.954 54.0
ACN Information Technology 79.79 8.326 80.0
ACE Financials 102.91 86.897 103.0# retrieves both Price columns
dups.Price[:5] Price Price
Symbol
MMM 141.14 141.0
ABT 39.60 40.0
ABBV 53.95 54.0
ACN 79.79 80.0
ACE 102.91 103.0Reordering columns
# return a new DataFrame with the columns reversed
reversed_column_names = sp500.columns[::-1]
sp500[reversed_column_names][:5] BookValue Price Sector
Symbol
MMM 26.668 141.14 Industrials
ABT 15.573 39.60 Health Care
ABBV 2.954 53.95 Health Care
ACN 8.326 79.79 Information Technology
ACE 86.897 102.91 FinancialsReplacing the contents of a column
# this occurs in-place so let's use a copy
copy = sp500.copy()
# replace the Price column data with the new values
# instead of adding a new column
copy.Price = rounded_price.Price
copy[:5] Sector Price BookValue
Symbol
MMM Industrials 141.0 26.668
ABT Health Care 40.0 15.573
ABBV Health Care 54.0 2.954
ACN Information Technology 80.0 8.326
ACE Financials 103.0 86.897# this occurs in-place so let's use a copy
copy = sp500.copy()
# replace the Price column data wwith rounded values
copy.loc[:,'Price'] = rounded_price.Price
copy[:5] Sector Price BookValue
Symbol
MMM Industrials 141.0 26.668
ABT Health Care 40.0 15.573
ABBV Health Care 54.0 2.954
ACN Information Technology 80.0 8.326
ACE Financials 103.0 86.897Deleting columns
# Example of using del to delete a column
# make a copy as this is done in-place
copy = sp500.copy()
del copy['BookValue']
copy[:2] Sector Price
Symbol
MMM Industrials 141.14
ABT Health Care 39.60# Example of using pop to remove a column from a DataFrame
# first make a copy of a subset of the data frame as
# pop works in place
copy = sp500.copy()
# this will remove Sector and return it as a series
popped = copy.pop('Sector')
# Sector column removed in-place
copy[:2] Price BookValue
Symbol
MMM 141.14 26.668
ABT 39.60 15.573# and we have the Sector column as the result of the pop
popped[:5]Symbol
MMM Industrials
ABT Health Care
ABBV Health Care
ACN Information Technology
ACE Financials
Name: Sector, dtype: object# Example of using drop to remove a column
# make a copy of a subset of the data frame
copy = sp500.copy()
# this will return a new DataFrame with 'Sector’ removed
# the copy DataFrame is not modified
afterdrop = copy.drop(['Sector'], axis = 1)
afterdrop[:5] Price BookValue
Symbol
MMM 141.14 26.668
ABT 39.60 15.573
ABBV 53.95 2.954
ACN 79.79 8.326
ACE 102.91 86.897Appending rows from other DataFrame objects with .append()
# copy the first three rows of sp500
df1 = sp500.iloc[0:3].copy()
# copy 10th and 11th rows
df2 = sp500.iloc[[10, 11, 2]]
# append df1 and df2
appended = df1.append(df2)
# the result is the rows of the first followed by
# those of the second
appended Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954
A Health Care 56.18 16.928
GAS Utilities 52.98 32.462
ABBV Health Care 53.95 2.954# data frame using df1.index and just a PER column
# also a good example of using a scalar value
# to initialize multiple rows
df3 = pd.DataFrame(0.0,
index=df1.index,
columns=['PER'])
df3 PER
Symbol
MMM 0.0
ABT 0.0
ABBV 0.0# append df1 and df3
# each has three rows, so 6 rows is the result
# df1 had no PER column, so NaN from for those rows
# df3 had no BookValue, Price or Sector, so NaN's
df1.append(df3)c:\users\user\appdata\local\programs\python\python37-32\lib\site-packages\pandas\core\frame.py:6692: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.
To accept the future behavior, pass 'sort=False'.
To retain the current behavior and silence the warning, pass 'sort=True'.
sort=sort)
BookValue PER Price Sector
Symbol
MMM 26.668 NaN 141.14 Industrials
ABT 15.573 NaN 39.60 Health Care
ABBV 2.954 NaN 53.95 Health Care
MMM NaN 0.0 NaN NaN
ABT NaN 0.0 NaN NaN
ABBV NaN 0.0 NaN NaN# ignore index labels, create default index
df1.append(df3, ignore_index=True) BookValue PER Price Sector
0 26.668 NaN 141.14 Industrials
1 15.573 NaN 39.60 Health Care
2 2.954 NaN 53.95 Health Care
3 NaN 0.0 NaN NaN
4 NaN 0.0 NaN NaN
5 NaN 0.0 NaN NaNConcatenating rows
# copy the first three rows of sp500
df1 = sp500.iloc[0:3].copy()
# copy 10th and 11th rows
df2 = sp500.iloc[[10, 11, 2]]
# pass them as a list
pd.concat([df1, df2]) Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954
A Health Care 56.18 16.928
GAS Utilities 52.98 32.462
ABBV Health Care 53.95 2.954# copy df2
df2_2 = df2.copy()
# add a column to df2_2 that is not in df1
df2_2.insert(3, 'Foo', pd.Series(0, index=df2.index))
# see what it looks like
df2_2 Sector Price BookValue Foo
Symbol
A Health Care 56.18 16.928 0
GAS Utilities 52.98 32.462 0
ABBV Health Care 53.95 2.954 0# now concatenate
pd.concat([df1, df2_2])c:\users\user\appdata\local\programs\python\python37-32\lib\site-packages\ipykernel_launcher.py:2: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.
To accept the future behavior, pass 'sort=False'.
To retain the current behavior and silence the warning, pass 'sort=True'.
BookValue Foo Price Sector
Symbol
MMM 26.668 NaN 141.14 Industrials
ABT 15.573 NaN 39.60 Health Care
ABBV 2.954 NaN 53.95 Health Care
A 16.928 0.0 56.18 Health Care
GAS 32.462 0.0 52.98 Utilities
ABBV 2.954 0.0 53.95 Health Care# specify keys
r = pd.concat([df1, df2_2], keys=['df1', 'df2'])
r BookValue Foo Price Sector
Symbol
df1 MMM 26.668 NaN 141.14 Industrials
ABT 15.573 NaN 39.60 Health Care
ABBV 2.954 NaN 53.95 Health Care
df2 A 16.928 0.0 56.18 Health Care
GAS 32.462 0.0 52.98 Utilities
ABBV 2.954 0.0 53.95 Health CareAdding and replacing rows via setting with enlargement
# get a small subset of the sp500
# make sure to copy the slice to make a copy
ss = sp500[:3].copy()
# create a new row with index label FOO
# and assign some values to the columns via a list
ss.loc['FOO'] = ['the sector', 100, 110]
ss Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954
FOO the sector 100.00 110.000Removing rows using .drop()
# get a copy of the first 5 rows of sp500
ss = sp500[:5]
ss Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954
ACN Information Technology 79.79 8.326
ACE Financials 102.91 86.897# drop rows with labels ABT and ACN
afterdrop = ss.drop(['ABT', 'ACN'])
afterdrop[:5] Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABBV Health Care 53.95 2.954
ACE Financials 102.91 86.897Removing rows using Boolean selection
# determine the rows where Price > 300
selection = sp500.Price > 300
# report number of rows and number that will be dropped
(len(selection), selection.sum())(500, 10)# select the complement of the expression
# note the use of the complement of the selection
price_less_than_300 = sp500[~selection]
price_less_than_300 Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954
ACN Information Technology 79.79 8.326
ACE Financials 102.91 86.897
... ... ... ...
YHOO Information Technology 35.02 12.768
YUM Consumer Discretionary 74.77 5.147
ZMH Health Care 101.84 37.181
ZION Financials 28.43 30.191
ZTS Health Care 30.53 2.150
[490 rows x 3 columns]Removing rows using a slice
# get only the first three rows
only_first_three = sp500[:3]
only_first_three Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954# first three, but a copy of them
only_first_three = sp500[:3].copy()
only_first_three Sector Price BookValue
Symbol
MMM Industrials 141.14 26.668
ABT Health Care 39.60 15.573
ABBV Health Care 53.95 2.954Last updated