CODEWITHSUNDEEP
pythonpandascsv
Syahmi Shaarani
Syahmi Shaarani

Reputation: 75

Parsing date as string from csv to pandas

I have been trying to use panda to parse date as string from my csv.

What I am trying to achieve here is to extract a row (from the csv) which contains two type of data, string and integer.

From here, I want to use docx-mailmerge to produce Microsoft Word reports.

For context, I was referring to the solutions here when figuring out: datetime dtypes in pandas read_csv

Thank you.

Here is error I am getting:

ValueError: invalid literal for int() with base 10: 'Num'

Here is my code:

import pandas as pd   

headers = ['AA', 'Num']
dtypes = {'AA': 'str', 'Num': 'int'}
pdate = ['AA']

df = pd.read_csv('test1.csv', 
header=None,
names = headers,
dtype = dtypes,
parse_dates = pdate, 
date_parser=None) 

top = df.head()
print(top)

Here is my csv file:

AA,Num
1/1/2020,5
2/1/2020,10
3/1/2020,15
4/1/2020,20
5/1/2020,25
6/1/2020,30
7/1/2020,35
8/1/2020,40
9/1/2020,45
10/1/2020,50
11/1/2020,55

Upvotes: 3

Views: 657

Answers (2)

Syahmi Shaarani
Syahmi Shaarani

Reputation: 75

Here is the working code:

headers = ['AA', 'Num']
dtypes = {'AA': 'str', 'Num': 'int'}
pdate = ['AA']

df = pd.read_csv('test1.csv', 
dtype = dtypes,
parse_dates = pdate, 
date_parser=None)

top = df.head()
print(top)

Upvotes: 0

jezrael
jezrael

Reputation: 863781

I think problem is parameter header=None, it is used if no headers in csv. So remove it. Also parameter names = headers should be omit, if need parse all columns:

#here is set new columns names, so original headers are shift to first row of data
df = pd.read_csv('test1.csv',
header=None,
names = headers,
#dtype = dtypes,
#parse_dates = pdate, 
date_parser=None) 

top = df.head()
print(top)
         AA  Num
0        AA  Num
1  1/1/2020    5
2  2/1/2020   10
3  3/1/2020   15
4  4/1/2020   20

Correct parsing:

df = pd.read_csv('test1.csv',
#header=None,
#names = headers,
dtype = dtypes,
parse_dates = pdate, 
date_parser=None) 

top = df.head()
print(top)
          AA  Num
0 2020-01-01    5
1 2020-02-01   10
2 2020-03-01   15
3 2020-04-01   20
4 2020-05-01   25

If need filter only some columns instead names = headers use usecols=headers parameter.

Upvotes: 1

Related Questions