How do I prevent a value from converting to a date or executing as division?

Question

I have a column in a dataframe that has values in the format XX/XX (Ex: 05/23, 4/22, etc.) When I convert it to a csv, it converts to a date. How do I prevent this from happening?

I tried putting an equals sign in front but then it executes like division (Ex: =4/20 comes out to 0.5).

df['unique_id'] = '=' + df['unique_id']

I want the output to be in the original format XX/XX (Ex: 5/23 stays 5/23 in the csv file in Excel).

There's not enough information. What kind of "database" are you using? How are you converting your data to CSV? — ForceBru, Aug 13 '19 at 17:42
csv's dont know anything about dates or division. do you mean when you open it in excel that excel then applies its formating logic on it? Have you tried just wrapping it in quotes? — Chris Doyle, Aug 13 '19 at 17:44
i have made a sample test and the issue isnt with pandas oy python the csv priduced and vied in a text file is normal it an excel processing issue — Chris Doyle, Aug 13 '19 at 18:03

score 1 · Answer 1 · answered Aug 13 '19 at 17:51

1

Check the datatypes of your dataframe with df.dtypes. I assume your column is interpreted as date. Then you can do df[col] = df[col].astype(np_type_you_want)

If that doenst bring the wished result, check why the column is interpreted as date when creating the df. Solution depends on where you get the data from.

answered Aug 13 '19 at 17:51

ArsenieBoca

68
5

The colums isnt interpreted as a date. The issue occurs isnt a pandas or python issue. the issue is when the CSV is opened in excel excel it interpreting 4/5 as a date and then defaulting the column to a date – Chris Doyle Aug 13 '19 at 18:01

score 1 · Answer 2 · answered Aug 13 '19 at 18:10

The issue is not an issue with python or pandas. The issue is that excel thinks its clever and assumes it knows your data type. you were close with trying to put an = before your data but your data needs to be wrapped in qoutes and prefixed with an =. I cant claim to have come up with this answer myself. I obtained it from this answer

The following code will allow you to write a CSV file that will then open in excel without any formating trying to convert to date or executing division. However it shoudl be noted that this is only really a strategy if you will only be opening the CSV in excel. as you are wrapping formating info around your data which will then be stripped out by excel. If you are using this csv in any other software you might need to rethink about it.

import pandas as pd
import csv
data = {'key1': [r'4/5']}
df = pd.DataFrame.from_dict(data)
df['key1'] = '="' + df['key1'] + '"'
print(df)
print(df.dtypes)
with open(r'C:\Users\cd00119621\myfile.csv', 'w') as output:
    df.to_csv(output)

RAW OUTPUT in file

,key1
0,"=""4/5"""

EXCEL OUTPUT

How do I prevent a value from converting to a date or executing as division?

2 Answers2