Lesson 14, Part 4: Pandas¶
- Reading a .csv file into a Pandas DataFrame
- Listing rows
- Getting metadata
In [7]:
fd = open('C:/Explore/SAS/Lesson14/SAS_Codes/TV_Data_noheader.csv')
print(fd.read())
fd.close()
opinion,party,income,age Agree,Republican,35000,30 Disagree,Democrat,40000,50 Strongly Agree,Independent,100000,40 No Opinion,Green Party,90000,45 Strongly Disagree,Democrat,65000,45 Disagree,Independent,55000,39 Strongly Agree,Green Party,75000,80 No Opinion,Independent,55000,70 Strongly Disagree,Green Party,28000,30 No Opinion,Democrat,80000,45 Strongly Disagree,Independent,45000,25 Disagree,Green Party,110000,45 Agree,Democrat,55000,60 Disagree,Independent,37000,29 Strongly Agree,Green Party,81000,81 No Opinion,Green Party,59000,40 Strongly Disagree,Democrat,150000,45 Disagree,Independent,67000,61 Strongly Agree,Green Party,78000,45 No Opinion,Independent,200000,52 Strongly Disagree,Green Party,8000,21 No Opinion,Green Party,55000,50 Strongly Disagree,Independent,70000,39 Disagree,Green Party,67000,40
pd.read_csv() reads a raw data file and produces a dataframe¶
In [9]:
import pandas as pd
df=pd.read_csv('C:/Explore/SAS/Lesson14/SAS_Codes/TV_Data_noheader.csv', sep=',')
df.head()
Out[9]:
| opinion | party | income | age | |
|---|---|---|---|---|
| 0 | Agree | Republican | 35000 | 30 |
| 1 | Disagree | Democrat | 40000 | 50 |
| 2 | Strongly Agree | Independent | 100000 | 40 |
| 3 | No Opinion | Green Party | 90000 | 45 |
| 4 | Strongly Disagree | Democrat | 65000 | 45 |
In [27]:
type(df)
Out[27]:
pandas.core.frame.DataFrame
Attributes¶
- Find out the data type of each column of the data frame named df
In [28]:
df.dtypes
Out[28]:
opinion object party object income int64 age int64 dtype: object
Convert a SAS data set into a pandas dataframe¶
In [7]:
import saspy
sas=saspy.SASsession()
cars= sas.sd2df('cars', libref='sashelp')
Using SAS Config named: winlocal SAS Connection established. Subprocess id is 2928
In [2]:
type(cars)
Out[2]:
pandas.core.frame.DataFrame
Select a single column by passing the column name as a string to the indexing operator.¶
In [11]:
import pandas as pd
cars['Make'].head()
Out[11]:
0 Acura 1 Acura 2 Acura 3 Acura 4 Acura Name: Make, dtype: object
Alternatively, you can perform the same task by simply using the dot operator.¶
In [11]:
cars.Make.head()
Out[11]:
0 Acura 1 Acura 2 Acura 3 Acura 4 Acura Name: Make, dtype: object
Create a new dataframe with fewer columns based on an existing dataframe¶
In [ ]:
import pandas as pd
df_x = pd.DataFrame(cars,columns=['Make','Type','Invoice'])
df_x.head()
Alternatively, you can assign all your columns to a list and then pass that list to the indexing operator.¶
In [15]:
selected_cols = ['Make', 'Type','Invoice']
list_of_cols = cars[selected_cols]
list_of_cols.head()
Out[15]:
| Make | Type | Invoice | |
|---|---|---|---|
| 0 | Acura | SUV | 33337 |
| 1 | Acura | Sedan | 21761 |
| 2 | Acura | Sedan | 24647 |
| 3 | Acura | Sedan | 30299 |
| 4 | Acura | Sedan | 39014 |
In [19]:
cars.columns
Out[19]:
Index(['Make', 'Model', 'Type', 'Origin', 'DriveTrain', 'MSRP', 'Invoice',
'EngineSize', 'Cylinders', 'Horsepower', 'MPG_City', 'MPG_Highway',
'Weight', 'Wheelbase', 'Length'],
dtype='object')
Find out the number of columns for each data types.¶
Python has the following data types.¶
- Numbers
- int (signed integers)
- float (floating point real values)
- long (long integers which can berepresented in octal and hexadecimal)
- complex (complex numbers)
- String
- List
- Tuple
- Dictionary
In [2]:
import saspy
In [3]:
%%SAS
proc sql;
select nobs format=comma7.
,nvar
from dictionary.tables
where libname = 'SASHELP' and memname = 'CARS';
quit;
Using SAS Config named: winlocal SAS Connection established. Subprocess id is 19236
Out[3]:
| Number of Physical Observations | Number of Variables |
|---|---|
| 428 | 15 |
Get the number of rows and columns¶
In [36]:
cars.shape
Out[36]:
(428, 15)
Get the number of rows¶
In [14]:
len(cars)
Out[14]:
428
Get the number of columns¶
In [16]:
len(cars.columns)
Out[16]:
15
In [4]:
%%SAS
options nodate nonumber nonotes nosource;
ods html close;
data _NULL_;
set sashelp.cars;
array char[*] $ _CHAR_;
array num[*] _NUMERIC_;
nCharVar = dim(char);
nNumerVar = dim(num);
put "Data Types in SASHELP.CARS: " nCharVar= nNumerVar= ;
stop; /* stop processing after first observation */
run;
Out[4]:
The SAS System
Data Types in SASHELP.CARS: nCharVar=5 nNumerVar=10
E3969440A681A2408885998500000005
Find data types in Python¶
In [25]:
cars.dtypes.value_counts()
Out[25]:
int64 8 object 5 float64 2 dtype: int64
In [8]:
import numpy as np
stats=df_x.groupby(['Make'])['Invoice'].aggregate(['count', 'min', 'max', 'mean', np.median])
print(stats)
count min max mean median Make Acura 7 21761 79978 38590.857143 33337.0 Audi 19 23508 76417 39330.105263 36739.0 BMW 20 26155 66830 39620.650000 35710.0 Buick 9 20351 36927 27854.888889 26047.0 Cadillac 8 28575 70546 46426.875000 45101.5 Chevrolet 27 10965 45193 24060.814815 21551.0 Chrysler 15 16919 35063 25270.066667 24909.0 Dodge 13 12849 74451 24160.076923 18821.0 Ford 23 12482 36494 21953.000000 20857.0 GMC 8 14877 40534 26289.375000 24487.0 Honda 17 12175 29965 19512.764706 18419.0 Hummer 1 45815 45815 45815.000000 45815.0 Hyundai 12 10107 23486 16035.333333 15654.0 Infiniti 8 26157 47575 32880.000000 30769.5 Isuzu 2 19261 29977 24619.000000 24619.0 Jaguar 12 27355 79226 56098.416667 60172.5 Jeep 3 18973 25686 22644.666667 23275.0 Kia 11 9875 23764 14890.363636 13790.0 Land Rover 3 23969 65807 41851.000000 35777.0 Lexus 11 27404 56455 38760.000000 36196.0 Lincoln 9 29969 46360 39135.777778 39443.0 MINI 2 15437 18137 16787.000000 16787.0 Mazda 11 14070 26600 20192.454545 20482.0 Mercedes-Benz 26 24249 119600 56453.307692 47848.0 Mercury 9 19848 31558 25657.555556 27148.0 Mitsubishi 13 13751 30763 21834.769231 23456.0 Nissan 17 12205 31845 22997.470588 24926.0 Oldsmobile 3 17642 26120 21749.000000 21485.0 Pontiac 11 14375 32997 22159.636364 20595.0 Porsche 7 37886 173560 73662.857143 67128.0 Saab 7 29269 40883 35620.285714 37721.0 Saturn 8 10319 21779 16068.000000 15055.0 Scion 2 12340 13480 12910.000000 12910.0 Subaru 11 18399 29130 23297.545455 23022.0 Suzuki 8 12116 22307 15891.250000 15834.5 Toyota 28 10144 47986 20295.928571 20072.0 Volkswagen 15 17427 69130 29682.666667 21898.0 Volvo 12 23701 42573 34216.166667 35462.0