0

I am working on automating a process with python using pandas. Previously I would use Excel PowerQuery to combine files and manipulate data but PowerQuery is not as versatile as I need so I am now using pandas. I have the process working up to a point where I can loop through files, select the columns that I need in the correct order, dependent on each workbook, and insert that into a dataframe. Once each dataframe is created, I then concatenate them into a single dataframe and write to csv. Before writing, I need to apply some validation to certain columns.

For example, I have a Stock Number column that will always need to be exactly 11 characters long. Sometimes, dependent on the workbook, the data will be missing the leading zeros or will have more than 11 characters (but those extra characters should be removed). I know that what I need to do is something along the lines of:

STOCK_NUM.zfill(13)[:13]

but I'm not sure how to actually modify the existing dataframe values. Do I actually need to loop through the dataframe or is there a way to apply formatting to an entire column?

e.g. 

dataset = [['51346812942315.01', '01-15-2018'], ['13415678', '01-15-2018'], ['5134687155546628', '01/15/2018']] 
df = pd.DataFrame(dataset, columns = ['STOCK_NUM', 'Date']) 

for x in df["STOCK_NUM"]:
    print(x.zfill(13)[:13])

I would like to know the most optimal way to apply that format to the existing values and only if those values are present (i.e. not touching it if there are null values).

Also, I have a need to ensure that the date columns are truly date values. Sometimes the dates are formatted as MM-DD-YYYY or sometimes MM/DD/YY, etc.. and any of those are fine but what is not fine is if the actual value in the date column is an Excel serial number that Excel can fomat as a date. Is there some way to apply validation logic to an entire dataframe column the ensure that as there is a valid date instead of serial number?

I honestly have no idea how to approach this date issue.

Any and all advice, insight would be greatly appreciated!

hlbly
  • 25
  • 3
  • I don't know for the first part but for date: `df['Date'] = pd.to_datetime(df['Date'])` If one of the values is something like "'15/40/2018'" then it will fail, so you will need to do that inside a `try` – Nerdrigo Nov 22 '19 at 00:29

2 Answers2

1

Not an expert, but from things I could gather here and there you could try try:

df['STOCK_NUM']=df['STOCK_NUM'].str.zfill(13)

followed by:

df['STOCK_NUM'] = df['STOCK_NUM'].str.slice(0,13)

For the first part. For dates you can do a try-except on: 

df['Date'] = pd.to_datetime(df['Date'])
Nerdrigo
  • 308
  • 1
  • 5
  • 14
  • Thank you so much. That works perfectly. I was able to use the following: dataframe["STOCK_NUM"] = dataframe["STOCK_NUM"].apply(str).str.zfill(13)[:13] ## Ensure date columns have date values for column in OUTPUT_COLUMNS: if column["date"]: dataframe[column["name"]] = pd.to_datetime(dataframe[column["name"]]) for record in dataframe[column["name"]]: try: record = datetime.datetime.strftime(record, "%m-%d-%Y") except ValueError: pass and everything looks great so far. – hlbly Nov 22 '19 at 01:16
0

for your STOCK_NUM question, you could potentially apply a function to the column but the way I approach this is using list comprehensions. The first thing I would do is replace all the NAs in your STOCK_NUM column by a unique string and then apply the list comprehension as you can see in the code below:

import pandas as pd
dataset = [['51346812942315.01', '01-15-2018'], ['13415678', '01-15-2018'], ['5134687155546628', '01/15/2018'], [None,42139]] 
df = pd.DataFrame(dataset, columns = ['STOCK_NUM', 'Date'])

#replace NAs with a string
df.STOCK_NUM.fillna('IS_NA',inplace=True)

#use list comprehension to reformat the STOCK_NUM column 
df['STOCK_NUM'] = [None if i=='IS_NA' else i.zfill(13)[:13] for i in df.STOCK_NUM]

Then for your question relating to converting excel serial number to a date, I looked at an already answered question. I am assuming that the serial number in your dataframe is an integer type:

import datetime
def xldate_to_datetime(xldate):
    temp = datetime.datetime(1900, 1, 1)
    delta = datetime.timedelta(days=xldate) - datetime.timedelta(days=2)
    return pd.to_datetime(temp+delta)

df['Date'] = [xldate_to_datetime(i) if type(i)==int else pd.to_datetime(i) for i in df.Date]

Hopefully this works for you! Accept this answer if it does, otherwise reply with whatever remains an issue.

Pol Molinas
  • 56
  • 3