CODEWITHSUNDEEP
pythoncsvpandas
Mohan
Mohan

Reputation: 91

Loading CSV data using Pandas

i am unable load csv using the below pandas commnads. 

f1 = pd.read_csv(r'C:\Users\sana.mohan.reddy\Desktop\Python_Practice\Test1.CSV', skiprows=[0,1,2], skip_footer=[0], sep = ',')

I have to skip first 3 rows and last row.

Below is the sample data.

Contacts - Total Opens by Campaign

Email Open Date/Time,"Total Opens"
3/25/2016 6:00:35 AM,"1"
3/25/2016 6:00:35 AM,"1"
3/25/2016 6:00:46 AM,"1"
3/25/2016 6:00:46 AM,"1"
3/25/2016 6:00:51 AM,"1"
3/25/2016 6:00:52 AM,"1"
Total,"796"

could you please correct me where i am going wrong

Upvotes: 2

Views: 748

Answers (2)

user2314737
user2314737

Reputation: 29317

You need to correct your read_csv to:

f1 = pd.read_csv('yourFile.csv', skiprows=3, skip_footer=1, sep = ',')

since skip_footer requires an integer value (the number of lines to skip at the bottom of the file) see http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html

Upvotes: 1

jezrael
jezrael

Reputation: 862641

I think you can use read_csv with other parameters (sep = ',' is omited, because , is default value of sep):

import pandas as pd
import io

temp=u'''Email Open Date/Time,"Total Opens"
3/25/2016 6:00:35 AM,"1"
3/25/2016 6:00:35 AM,"1"
3/25/2016 6:00:46 AM,"1"
3/25/2016 6:00:46 AM,"1"
3/25/2016 6:00:51 AM,"1"
3/25/2016 6:00:52 AM,"1"
Total,"796"'''
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp),
                 skipfooter=1, #skip last row
                 engine='python', #remove warning
                 skiprows=[0,1,2], #remove first 3 rows
                 header=None) #no header, set default 0,1,... 
print (df)

                      0  1
0  3/25/2016 6:00:46 AM  1
1  3/25/2016 6:00:46 AM  1
2  3/25/2016 6:00:51 AM  1
3  3/25/2016 6:00:52 AM  1

EDIT by real data:

There was main problem with encoding - I have to set utf-16.

import pandas as pd

df = pd.read_csv('Test 1.csv',
                 skipfooter=1, #skip last row
                 engine='python', #remove warning
                 skiprows=[0,1], #remove first 2 rows
                 encoding='utf-16', #set encoding
                 parse_dates=[0]) #convert first column to datetime 
print (df)

    Email Open Date/Time  Total Opens
0    2016-03-25 06:00:35            1
1    2016-03-25 06:00:35            1
2    2016-03-25 06:00:46            1
3    2016-03-25 06:00:46            1
4    2016-03-25 06:00:51            1
5    2016-03-25 06:00:52            1
6    2016-03-25 06:00:57            1
7    2016-03-25 06:00:58            1
8    2016-03-25 06:01:03            1
9    2016-03-25 06:01:20            1
10   2016-03-25 06:01:20            1
11   2016-03-25 06:01:25            1

Upvotes: 1

Related Questions