2. Data Frames

Question 1

Data Frames

Answer 1

mutli dimensional data

Excel is 2 dimensional, to get a data you need two points of reference

Question 2

auto convert to float

Answer 2

if there is any NaN value, even integer will be converted to float

Question 3

df basic shared attributes

Answer 3

df. index
df. values: is a numpy object
df. dtypes: all columns

df.shapre => tuple

Question 4

df specific attributes

Answer 4

df. columns
df. axes
df. info() => count non-null

Question 5

df shared methods

Answer 5

df. sum() => row total for each columns; axis default to be 0
rev. sum(axis = “columns”) or rev.sum(axis = 1) => sum by row across “columns”

Question 6

extracting column in df

Answer 6

df.[“Col1”]

df.Col1 => return series
but doesn’t work with column name with space
so do not use

Question 7

extracting multiple columns

Answer 7

nba[[“Name”,”Team”]] => multiple columns

nba[mylsit], basically put a list in Series or df

Question 8

adding new column

Answer 8

1. Assignment
   nba[“new column”] = nba[“salary”] /2
2. nba.insert(loc, Name ,values)
   cannot repeat column name

Question 9

axis

Answer 9

0 = row
1 = column

Question 10

NaN detection and handle

Answer 10

dropna(), fillna()
isnull(), notnull()

df.dropna(): by default remove any row with any NaN
can pass in a subset of column(s)
df.dropna(how=”all”): remove any row with all NaN values

df[“Col1”].fillna(“abc”)

df[“Team”].isnull()

df = pd.read_csv(“chicago.csv”).dropna(how = “all”)

Question 11

change dtype and category

Answer 11

nba[“Salary”] = nba[“Salary”].astype(“int”)
nba[“Position”] = nba[“Position”].astype(“category”)
this reduce the data usage

Question 12

sort

Answer 12

nba. sort_values(“Name”)

nba. sort_index()

Question 13

“object” in pandas

Answer 13

i.e. string

Question 14

date time object

Answer 14

df[“Start Date”] = pd.to_datetime(df[“Start Date”])

df = pd.read_csv(“employees.csv”, parse_dates = [“Start Date”,”Last Login Time”])

Question 15

filtering (masking)

Answer 15

df[df[“Gender”] == “Male”]
basically parse in a boolean series into a df
but hard to read

so better use
mask = df[“Start Date”] > “2000-3-31”
df[mask], where mask is a boolean series

And: df[mask1 & mask2]
Or: df[mask1 | mask2]
df[(mask1 & mask2) | mask3]

Question 16

filtering (method)

Answer 16

.between
df[df[“Salary”].between(60000,70000)]

isin() method:
df[df[“Team”].isin([“Legal”,”Sales”,”Product”])]

isnull()
notnull()

Question 17

extraction rows and columns

Answer 17

df. loc[]: slicing is inclusive, use index label i.e. string or date time
df. iloc[]: slicing is non-inclusive, only index position “index” loc

Note use [] not () because it’s “retrieving”

df.loc[Index] -> return series / return df if multiple result

df. loc[“Moonraker”,”Director”]
df. loc[mask,”Year”:”Director”]
df. loc[[“Moonraker”,”A View to a Kill”],”Director”:”Budget”]
df. loc[:,”Director”:”Budget”]
df. iloc[0:5,0:5]

Question 18

check duplicate

Answer 18

df[[“First Name”]].duplicated(): first one mark as True, subsequent duplicate mark as False
df[[“First Name”]].duplicated(keep=False): all duplicate mark as False

df.drop_duplicates(subset = [“First Name”], keep = False)

Question 19

negation of a Series

Answer 19

~df[“First Name”].duplicated(keep=False)

Question 20

unique

Answer 20

nba[“Position”].nunique() => give you the number of unique, parameter to set to count NaN or not

.unique() give you an array

Question 21

adjusting index

Answer 21

set_index
reset_index

read_csv can specify index_col

Question 22

editing data

Answer 22

df[“col1”][“something”] = “abc” doesnot work as it is a new df but not connect to the df; use df.loc instead

can use mask as well
df.loc[mask,ColA:ColE] = “abc” or mylist with same shape

Question 23

rename column

Answer 23

df.rename(index = mydict) => change index
df.rename(columns = mydict) by default on index
or columns asignment
df.columns = [‘Yearabc’, ‘Actor’, ‘Director’, ‘Box Office’, ‘Budget’, ‘Bond Actor Salary’]

df. rename(mapper = mydict) by default on index
df. rename(mapper = {“Year”:”Release Date”,”Box Office”:”Revenue”},axis=1) =>

Question 24

delete row or columns

Answer 24

df.drop(“row index”)
df.drop(““,axis = 1)
or
df.pop (inplace and return)
or
del df[“col1”]

Question 25

sample

Answer 25

random

df.random(5)

Question 26

min max

Answer 26

df. nlargest(3,columns=”Box Office”)
df. nsmallest

can be applie to series as well
df[“Col1”].nlargest(3)

Question 27

bulk replace column name by “_”

Answer 27

df.columns = [x.replace(“ “,”_”) for x in df.columns]
columns assignment + list comprehension

df.columns = df.columns.str.replace(“ “,”_”)

Question 28

query

Answer 28

df.query(‘Actor == “Sean Connery”’)

note that parse in string, no need “” for Column name and use and’/or

df. query(‘Actor == “Sean Connery” and Director ==”Guy Hamilton”’)
df. query(‘Actor in mylist and Director ==”Guy Hamilton”’)

Question 29

apply(myfunc)

Answer 29

columns = [“Box_Office”, “Budget”, “Bond_Actor_Salary”]

for col in columns:
df[col] = df[col].apply(mycon)

Question 30

apply method for parsing a row

Answer 30

df.apply(good_move,axis =1)

“apply” moving along column

Question 31

copy method

Answer 31

df2 = df1.copy()

split the ref

Question 32

read in csv with no header

Answer 32

df = pd.read_csv(file_path, usecols=[3,6], names=[‘colA’, ‘colB’], header=None)

Question 33

read csv with range of columns

Answer 33

pd.read_csv(“GOOG_1min_sample.txt”, header=None, usecols=[*range(0,3)])

the * It’s like somewhat “unpacking” the iterator returned by the range function. For example, [range(0, 3), 3] == [[0, 1, 2], 3] but [*range(0, 3), 3] == [0, 1, 2, 3]

Question 34

writing back value to df

Answer 34

df[“Col”] = “abc” won’t work as it is a copy of the df
use
df2.loc[:,”local_min”]=”abc”