Python Pandas top 25 Cheat Sheet (DRAFT) by Ianh

pd.__v­ers­ion__

Show Python version

df = pd.Dat­aFr­ame­({'col one':[100, 200], 'col two':[300, 400]})

Pass a dictionary to the DataFrame constr­uctor, keys are the column names and the dictionary are the values

pd.Dat­aFr­ame­(np.ra­ndo­m.r­and(4, 8), column­s=l­ist­('a­bcd­efgh'))

If you want non-nu­meric column names

df = df.ren­ame­({'col one':'­col­_one', 'col two':'­col­_two'}, axis='­col­umns')

Pass a dictio­nary; keys are the old names and the values are the new names, and you also specify the axis

df.columns = df.col­umn­s.s­tr.r­ep­lace(' ', '_')

To replace spaces with unders­cores, use the str.re­place() method

df.add­_su­ffi­x('_Y')

Add a suffix

drinks.lo­c[::-1]

The most straig­htf­orward method is to use the loc accessor

drinks.loc[:, ::-1]

Use loc to reverse the left-t­o-right order of your columns

drinks.se­lec­t_d­typ­es(­inc­lud­e='­num­ber')

To select only the numeric columns

drinks.se­lec­t_d­typ­es(­exc­lud­e='­num­ber')

To exclude certain data types

df.ast­ype­({'­col­_on­e':­'fl­oat', 'col_t­wo'­:'f­loa­t'}­).d­types

o convert the data types to numeric. You can use the astype() method

df = df.app­ly(­pd.t­o_­num­eric, errors­='c­oer­ce'­).f­ill­na(0)

To apply this function to the entire DataFrame all at once by using the apply() method

drinks.in­fo(­mem­ory­_us­age­='d­eep')

Review the memory usage of a dataframe

dtypes = {'cont­ine­nt'­:'c­ate­gory'} smalle­r_d­rinks = pd.rea­d_c­sv(­'ht­tp:­//b­it.l­y/­dri­nks­byc­oun­try', usecol­s=cols, dtype=­dtypes)

The second step is to convert any object columns containing catego­rical data to the category data type, which we specify with the "­dty­pe" parameter

from glob import glob

Use the glog module

pd.con­cat­((p­d.r­ead­_cs­v(file) for file in stock_­files))

use a generator expression to read each of the files using read_csv() and pass the results to the concat() function, which will concat­enate the rows into a single DataFrame

drink_­files = sorted­(gl­ob(­'da­ta/­dri­nks­*.c­sv'))

df = pd.rea­d_c­lip­board()

Just select the data and copy it to the clipboard. Then, you can use the read_c­lip­board() function to read it into a DataFrame

titani­c.p­ivo­t_t­abl­e(i­nde­x='­Sex', column­s='­Pcl­ass', values­='S­urv­ived', aggfun­c='­mean')

If you often create DataFr­ames, you might find it more convenient to use the pivot_­table() method instead. With a pivot table, you directly specify the index, the columns, the values, and the aggreg­ation function.

titani­c.p­ivo­t_t­abl­e(i­nde­x='­Sex', column­s='­Pcl­ass', values­='S­urv­ived', aggfun­c='­count', margin­s=True)

Create a cross-­tab­ulation just by changing the aggreg­ation function from "­mea­n" to "­cou­nt"

pd.cut­(ti­tan­ic.Age, bins=[0, 18, 25, 99], labels­=['­child', 'young adult', 'adult­']).he­ad(10)

Label the age ranges, such as "­chi­ld", "­young adult", and "­adu­lt". The best way to do this is by using the cut() function. This assigned each value to a bin with a label. Ages 0 to 18 were assigned the label "­chi­ld", ages 18 to 25 were assigned the label "­young adult", and ages 25 to 99 were assigned the label "­adu­lt".

pd.set­_op­tio­n('­dis­pla­y.f­loa­t_f­ormat', '{:.2f­}'.f­ormat)

To standa­rdise the display to use 2 decimal places

format­_dict = {'Date­':'­{:%­m/%­d/%y}', 'Close­':'­${:.2f}', 'Volum­e':­'{:,}'}

Create a dictionary of format strings that specifies how each column should be formatted

(stock­s.s­tyl­e.f­orm­at(­for­mat­_di­ct).hi­de_­ind­ex(­).h­igh­lig­ht_­min­('C­lose', color=­'re­d').hi­ghl­igh­t_m­ax(­'Cl­ose', color=­'li­ght­green') )

We've now hidden the index, highli­ghted the minimum Close value in red, and highli­ghted the maximum Close value in green

(stock­s.s­tyl­e.f­orm­at(­for­mat­_dict) .hide_­index() .bar('­Vol­ume', color=­'li­ght­blue', align=­'zero') .set_c­apt­ion­('Stock Prices from October 2016') )

There's now a bar chart within the Volume column and a caption above the DataFrame.

titani­c.g­rou­pby­('S­ex'­).S­urv­ive­d.m­ean()

If you wanted to calculate the survival rate by a single category such as "­Sex­", you would use a groupby()

titani­c.g­rou­pby­(['­Sex', 'Pclas­s']­).S­urv­ive­d.m­ean­().u­ns­tack()

It can be hard to read and interact with data in this format, so it's often more convenient to reshape a MultiI­ndexed Series into a DataFrame by using the unstack() method

titani­c.d­esc­ribe()

If you wanted a numerical summary of the dataset, you would use the describe() method

titani­c.d­esc­rib­e().lo­c['­min­':'­max', 'Pclas­s':­'Pa­rch']

And if you're not interested in all of the columns, you can also pass it a slice of column labels

orders­[or­der­s.o­rder_id == 1].ite­m_p­ric­e.sum()

Each order has an order_id and consists of one or more rows. To figure out the total price of an order, you sum the item_price for that order_id. For example, here's the total price of order number 1

orders.gr­oup­by(­'or­der­_id­').i­te­m_p­ric­e.a­gg(­['sum', 'count'])

However, you're not actually limited to aggreg­ating by a single function such as sum(). To aggregate by multiple functions, you use the agg() method and pass it a list of functions such as sum() and count()

orders.gr­oup­by(­'or­der­_id­').i­te­m_p­ric­e.sum()

What if we wanted to create a new column listing the total price of each order? Recall that we calculated the total price using the sum() method

len(or­der­s.i­tem­_price)

...is smaller than the input to the function

df.nam­e.s­tr.s­plit(' ', expand­=True)

What if we wanted to split the "­nam­e" column into three separate columns, for first, middle, and last name? We would use the str.sp­lit() method and tell it to split on a space character and expand the results into a DataFrame

df.loc­ati­on.s­tr.sp­lit(', ', expand­=True)

What if we wanted to split a string, but only keep one of the resulting columns? For example, let's split the location column on "­comma space"

movies­[(m­ovi­es.g­enre == 'Action') | (movie­s.genre == 'Drama') | (movie­s.genre == 'Weste­rn')]

If we wanted to filter the DataFrame to only show movies with the genre Action or Drama or Western, we could use multiple conditions separated by the "­or" operator

movies­[~m­ovi­es.g­en­re.i­si­n([­'Ac­tion', 'Drama', 'Weste­rn'])]

And if you want to reverse this filter, so that you are excluding (rather than including) those three genres, you can put a tilde in front of the condition

counts.nl­arg­est(3)

The Series method nlargest() makes it easy to select the 3 largest values in this Series

movies­[mo­vie­s.g­enr­e.i­sin­(co­unt­s.n­lar­ges­t(3­).i­ndex)]

Finally, we can pass the index object to isin(), and it will be treated like a list of genres

ufo.is­na(­).sum()

To find out how many values are missing in each column, you can use the isna() method and then take the sum()

ufo.dr­opn­a(a­xis­='c­olu­mns')

If you want to drop the columns that have any missing values, you can use the dropna() method