Extending and manipulating data

Intermediate Python for Finance

Kennedy Behrman

Data Engineer, Author, Founder

PCE

Personal consumption expenditures (PCE)

PCE =

Intermediate Python for Finance

PCE

Personal consumption expenditures (PCE)

PCE = PCDG

Durable goods

A Stove

1 By cactus cowboy 2 Open Clipart, CC0, https://commons.wikimedia.org/w/index.php?curid=64953673
Intermediate Python for Finance

PCE

Personal consumption expenditures (PCE)

PCE = PCDG + PCNDG

Non-durable goods

Pineapple

1 By Smart Servier 2 https://smart.servier.com/, CC BY 3.0, https://commons.wikimedia.org/w/index.php?curid=74765623
Intermediate Python for Finance

PCE

Personal consumption expenditures (PCE)

PCE = PCDG + PCNDG + PCESV

Services

Man with clipboard

1 By Clip Art by Vector Toons 2 Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=65937611
Intermediate Python for Finance

PCE - adding and removing columns

DATE PCDGA
1929-01-01 9.829
1930-01-01 7.661
1931-01-01 5.911
1932-01-01 3.959
Intermediate Python for Finance

PCE - adding and removing columns

pce['PCND'] = [[33.941,
                30.503,
                25.798000000000002,
                20.169]
Intermediate Python for Finance

PCE - adding and removing columns

pce
DATE PCDG PCND
1929-01-01 9.829 33.941
1930-01-01 7.661 30.503
1931-01-01 5.911 25.798
1932-01-01 3.959 20.169
Intermediate Python for Finance

PCE - adding and removing columns

pce
DATE PCDG PCND
1929-01-01 9.829 33.941
1930-01-01 7.661 30.503
1931-01-01 5.911 25.798
1932-01-01 3.959 20.169 
pcesv
PCESV
0 33.613
1 31.972
2 28.963
3 24.587
Intermediate Python for Finance

PCE - adding and removing columns

pce['PCESV'] = pcesv

pce
Intermediate Python for Finance

PCE - adding and removing columns

pce['PCESV'] = pcesv

pce
DATE PCDG PCND PCESV
1929-01-01 9.829 33.941 33.613
1930-01-01 7.661 30.503 31.972
1931-01-01 5.911 25.798 28.963
1932-01-01 3.959 20.169 24.587
Intermediate Python for Finance

PCE - adding and removing columns

pce['PCE'] = pce['PCDG'] + pce['PCND'] + pce['PCESV']
Intermediate Python for Finance

PCE - adding and removing columns

pce['PCE'] = pce['PCDG'] + pce['PCND'] + pce['PCESV']
DATE PCDG PCND PCESV PCE
1929-01-01 9.829 33.941 33.613 77.383
1930-01-01 7.661 30.503 31.972 70.136
1931-01-01 5.911 25.798 28.963 60.672
1932-01-01 3.959 20.169 24.587 48.715
Intermediate Python for Finance

PCE - adding and removing columns

pce.drop(columns=['PCDG', 'PCND', 'PCESV'], 
         axis=1, 
         inplace=True)
Intermediate Python for Finance

PCE - adding and removing columns

pce.drop(columns=['PCDG', 'PCND', 'PCESV'], 
         axis=1, 
         inplace=True)
DATE PCE
1929-01-01 77.383
1930-01-01 70.136
1931-01-01 60.672
1932-01-01 48.715
Intermediate Python for Finance

PCE - adding and removing rows

new_row
Intermediate Python for Finance

PCE - adding and removing rows

new_row
DATE PCE
1933-01-01 45.945 
pce.append(new_row)
Intermediate Python for Finance

PCE - adding and removing rows

new_row
DATE PCE
1933-01-01 45.945 
pce.append(new_row)
DATE PCE
1929-01-01 77.383
1930-01-01 70.136
1931-01-01 60.672
1932-01-01 48.715
1933-01-01 45.945
Intermediate Python for Finance

PCE - adding and removing rows

Adding multiple rows

new_rows = [ row1, row2, row3
]
for row in new_rows:
    pce = pce.append(row)
Intermediate Python for Finance

PCE - adding and removing rows

Adding multiple rows

for row in new_rows:
    pce = pce.append(row)
DATE PCE
1929-01-01 77.383
1930-01-01 70.136
1931-01-01 60.672
1932-01-01 48.715
1933-01-01 45.945
1934-01-01 51.461
1935-01-01 55.933
Intermediate Python for Finance

PCE - adding and removing rows

pce.drop(['1934-01-01',
          '1935-01-01',
          '1936-01-01',
          '1937-01-01',
          '1938-01-01',
          '1939-01-01'], 
          inplace=True)
Intermediate Python for Finance

PCE - adding and removing rows

pce.drop(['1934-01-01',
          '1935-01-01',
          '1936-01-01',
          '1937-01-01',
          '1938-01-01',
          '1939-01-01'], 
          inplace=True)
DATE PCE
1929-01-01 77.383
1930-01-01 70.136
1931-01-01 60.672
1932-01-01 48.715
1933-01-01 45.945
Intermediate Python for Finance

PCE - adding and removing rows

all_rows = [row1, row2, row3, pce]

pd.concat(all_rows)
Intermediate Python for Finance

PCE - adding and removing rows

all_rows = [row1, row2, row3, pce]

pd.concat(all_rows)
DATE PCE
1929-01-01 77.383
1930-01-01 70.136
1931-01-01 60.672
1932-01-01 48.715
1933-01-01 45.945
1934-01-01 51.461
1935-01-01 55.933
Intermediate Python for Finance

PCE - operations on DataFrames

ec = 0.88
pce * ec
Intermediate Python for Finance

PCE - operations on DataFrames

ec = 0.88
pce * ec
DATE PCE
1934-01-01 45.28568
1935-01-01 49.22104
1936-01-01 54.72544
1937-01-01 58.81832
Intermediate Python for Finance

PCE - map

def convert_to_euro(x):
    return x * 0.88

pce['EURO'] = pce['PCE'].map(convert_to_euro)
Intermediate Python for Finance

PCE - map

def convert_to_euro(x):
    return x * 0.88

pce['EURO'] = pce['PCE'].map(convert_to_euro)
DATE PCE EURO
1934-01-01 51.461 45.28568
1935-01-01 55.933 49.22104
1936-01-01 62.188 54.72544
Intermediate Python for Finance

Gross Domestic Product (GDP)

  • GDP = PCE + GE + GPDI + NE

  • PCE: Personal Consumption Expenditures

  • GE: Government Expenditures

  • GPDI: Gross Private Domestic Investment

  • NE: Net Exports

Intermediate Python for Finance

GDP - apply

map - Elements in a column (series)

apply - Across rows or columns

Intermediate Python for Finance

GDP - apply

GCE GPDI NE PCE
DATE
1929-01-01 9.622 17.170 0.383 77.383
1930-01-01 10.273 11.428 0.323 70.136
1931-01-01 10.169 6.549 0.001 60.672
1932-01-01 8.946 1.819 0.043 48.715
Intermediate Python for Finance

GDP - apply

gdp.apply(np.sum, axis=1)
Intermediate Python for Finance

GDP - apply

gdp['GDP'] = gdp.apply(np.sum, axis=1)
GCE GPDI NE PCE GDP
DATE
1929-01-01 9.622 17.170 0.383 77.383 104.558
1930-01-01 10.273 11.428 0.323 70.136 92.160
1931-01-01 10.169 6.549 0.001 60.672 77.391
1932-01-01 8.946 1.819 0.043 48.715 59.523
Intermediate Python for Finance

Let's practice!

Intermediate Python for Finance

Preparing Video For Download...