Nrthugu

I want to convert the columns Actual, Forecast, Previous into float so I can perform calculations on them. The csv also contains some NaNs which should stay in place.

The csv file looks like this:

2018-01-04 04:30:00,GBP,Low Impact Expected,Mortgage Approvals,65K,64K,65K

2018-01-04 04:51:00,EUR,Low Impact Expected,Spanish 10-y Bond Auction,1.53|1.8,,1.49|2.0

2018-01-04 05:01:00,EUR,Low Impact Expected,French 10-y Bond Auction,0.79|1.4,,0.36|1.9

2018-01-04 07:30:00,USD,Low Impact Expected,Challenger Job Cuts y/y,-3.6%,,30.1%

So far I have tried this:

df.columns = ['Date','Currency','Impact','Event','Actual','Forecast','Previous']



df = df[~(df['Actual'].isin('|','<']))]

#df = df[~df.Actual.str.contains("|")]



df['Actual'] = df['Actual'].str.replace('%', '')

df['Forecast'] = df['Forecast'].str.replace('%', '')

df['Previous'] = df['Previous'].str.replace('%', '')



df['Actual'] = df['Actual'].str.replace('K', '000')

df['Forecast'] = df['Forecast'].str.replace('K', '000')

df['Previous'] = df['Previous'].str.replace('K', '000')



for i in df['Actual']: float(i)

for i in df['Forecast']: float(i)

for i in df['Previous']: float(i)

The functions for getting rid of the | and < do not work. Many suggestions on the internet seem not to work with NaN values in the file.

Also I cannot figure out how to replace the % while at the same time move the decimal so the number representation is correct.

Hope someone can help. Thanks!

Not the prettiest way to do this, but I believe this is what you want:

from io import StringIO

import pandas as pd

df = pd.read_table(StringIO("""2018-01-04 04:30:00,GBP,Low Impact Expected,Mortgage Approvals,65K,64K,65K

2018-01-04 04:51:00,EUR,Low Impact Expected,Spanish 10-y Bond Auction,1.53|1.8,,1.49|2.0

2018-01-04 05:01:00,EUR,Low Impact Expected,French 10-y Bond Auction,0.79|1.4,,0.36|1.9

2018-01-04 07:30:00,USD,Low Impact Expected,Challenger Job Cuts y/y,-3.6%,,30.1%"""), names=['Date','Currency','Impact','Event','Actual','Forecast','Previous'], sep=',')

df = df.loc[~df['Actual'].str.contains('[|<]')]

for col in ['Actual', 'Forecast', 'Previous']:

    df.loc[pd.notnull(df[col]) & df[col].str.contains('%'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.contains('%'), col].str.replace('%', '')) / 100

    df.loc[pd.notnull(df[col]) & df[col].str.endswith('K'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.endswith('K'), col].str.replace('K', '')) * 1000

Here is my current solution if anyone is interested, thanks to the help of cosmic_inquiry.

import pandas as pd



#  Importing economic calendar

df = pd.read_csv('EconomicCalendar.csv')

df.columns = ['Date','Currency','Impact','Event','Actual','Forecast','Previous']





#  Remove no and low impact rows, remove votes beacuse of #format not convertable

df = df[df.Impact != 'Non-Economic']



event_filter = ['Asset Purchase Facility Votes', 'Official Bank Rate Votes']

df = df.loc[~df['Event'].str.contains('|'.join(event_filter))]



for col in ['Actual', 'Forecast', 'Previous']:

    #  Remove rows with certain formats not convertable

    df = df.loc[~df[col].str.contains('|'.join(['|','<']), na=False)]



    #  Change %, K, M, B, T into numerics

    df.loc[pd.notnull(df[col]) & df[col].str.contains('%'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.contains('%'), col].str.replace('%', '')) / 100

    df.loc[pd.notnull(df[col]) & df[col].str.endswith('K'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.endswith('K'), col].str.replace('K', '')) * 1000

    df.loc[pd.notnull(df[col]) & df[col].str.endswith('M'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.endswith('M'), col].str.replace('M', '')) * 1000000

    df.loc[pd.notnull(df[col]) & df[col].str.endswith('B'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.endswith('B'), col].str.replace('B', '')) * 1000000000

    df.loc[pd.notnull(df[col]) & df[col].str.endswith('T'), col] = pd.to_numeric(df.loc[pd.notnull(df[col]) & df[col].str.endswith('T'), col].str.replace('T', '')) * 1000000000000



    #  Change all to numeric to perform calculation

    df[col] = pd.to_numeric(df[col])





#  Creating Surprise column which is Actual minus Forecast

df['Surprise'] = df['Actual']-df['Forecast']