Since pandas
aims to provide a lot of the data manipulation and analysis functionality that people use R for, this page was started to provide a more detailed look at the R language and its many third party libraries as they relate to pandas
. In comparisons with R and CRAN libraries, we care about the following things:
- Functionality / flexibility: what can/cannot be done with each tool
- Performance: how fast are operations. Hard numbers/benchmarks are preferable
- Ease-of-use: Is one tool easier/harder to use (you may have to be the judge of this, given side-by-side code comparisons)
This page is also here to offer a bit of a translation guide for users of these R packages.
For transfer of DataFrame
objects from pandas
to R, one option is to use HDF5 files, see External Compatibility for an example.
Quick Reference
We?ll start off with a quick reference guide pairing some common R operations using dplyr with pandas equivalents.
Querying, Filtering, Sampling
R | pandas |
---|---|
dim(df) | df.shape |
head(df) | df.head() |
slice(df, 1:10) | df.iloc[:9] |
filter(df, col1 == 1, col2 == 1) | df.query('col1 == 1 & col2 == 1') |
df[df$col1 == 1 & df$col2 == 1,] | df[(df.col1 == 1) & (df.col2 == 1)] |
select(df, col1, col2) | df[['col1', 'col2']] |
select(df, col1:col3) | df.loc[:, 'col1':'col3'] |
select(df, -(col1:col3)) |
df.drop(cols_to_drop, axis=1) but see [1]
|
distinct(select(df, col1)) | df[['col1']].drop_duplicates() |
distinct(select(df, col1, col2)) | df[['col1', 'col2']].drop_duplicates() |
sample_n(df, 10) | df.sample(n=10) |
sample_frac(df, 0.01) | df.sample(frac=0.01) |
[1] | R?s shorthand for a subrange of columns (select(df, col1:col3) ) can be approached cleanly in pandas, if you have the list of columns, for example df[cols[1:3]] or df.drop(cols[1:3]) , but doing this by column name is a bit messy. |
Sorting
R | pandas |
---|---|
arrange(df, col1, col2) | df.sort_values(['col1', 'col2']) |
arrange(df, desc(col1)) | df.sort_values('col1', ascending=False) |
Transforming
R | pandas |
---|---|
select(df, col_one = col1) | df.rename(columns={'col1': 'col_one'})['col_one'] |
rename(df, col_one = col1) | df.rename(columns={'col1': 'col_one'}) |
mutate(df, c=a-b) | df.assign(c=df.a-df.b) |
Grouping and Summarizing
R | pandas |
---|---|
summary(df) | df.describe() |
gdf <- group_by(df, col1) | gdf = df.groupby('col1') |
summarise(gdf, avg=mean(col1, na.rm=TRUE)) | df.groupby('col1').agg({'col1': 'mean'}) |
summarise(gdf, total=sum(col1)) | df.groupby('col1').sum() |
Base R
Slicing with R?s c
R makes it easy to access data.frame
columns by name
df <- data.frame(a=rnorm(5), b=rnorm(5), c=rnorm(5), d=rnorm(5), e=rnorm(5)) df[, c("a", "c", "e")]
or by integer location
df <- data.frame(matrix(rnorm(1000), ncol=100)) df[, c(1:10, 25:30, 40, 50:100)]
Selecting multiple columns by name in pandas
is straightforward
In [1]: df = pd.DataFrame(np.random.randn(10, 3), columns=list('abc')) In [2]: df[['a', 'c']] Out[2]: a c 0 -1.039575 -0.424972 1 0.567020 -1.087401 2 -0.673690 -1.478427 3 0.524988 0.577046 4 -1.715002 -0.370647 5 -1.157892 0.844885 6 1.075770 1.643563 7 -1.469388 -0.674600 8 -1.776904 -1.294524 9 0.413738 -0.472035 In [3]: df.loc[:, ['a', 'c']] Out[3]: a c 0 -1.039575 -0.424972 1 0.567020 -1.087401 2 -0.673690 -1.478427 3 0.524988 0.577046 4 -1.715002 -0.370647 5 -1.157892 0.844885 6 1.075770 1.643563 7 -1.469388 -0.674600 8 -1.776904 -1.294524 9 0.413738 -0.472035
Selecting multiple noncontiguous columns by integer location can be achieved with a combination of the iloc
indexer attribute and numpy.r_
.
In [4]: named = list('abcdefg') In [5]: n = 30 In [6]: columns = named + np.arange(len(named), n).tolist() In [7]: df = pd.DataFrame(np.random.randn(n, n), columns=columns) In [8]: df.iloc[:, np.r_[:10, 24:30]] Out[8]: a b c d e f g \ 0 -0.013960 -0.362543 -0.006154 -0.923061 0.895717 0.805244 -1.206412 1 0.545952 -1.219217 -1.226825 0.769804 -1.281247 -0.727707 -0.121306 2 2.396780 0.014871 3.357427 -0.317441 -1.236269 0.896171 -0.487602 3 -0.988387 0.094055 1.262731 1.289997 0.082423 -0.055758 0.536580 4 -1.340896 1.846883 -1.328865 1.682706 -1.717693 0.888782 0.228440 5 0.464000 0.227371 -0.496922 0.306389 -2.290613 -1.134623 -1.561819 6 -0.507516 -0.230096 0.394500 -1.934370 -1.652499 1.488753 -0.896484 .. ... ... ... ... ... ... ... 23 -0.083272 -0.273955 -0.772369 -1.242807 -0.386336 -0.182486 0.164816 24 2.071413 -1.364763 1.122066 0.066847 1.751987 0.419071 -1.118283 25 0.036609 0.359986 1.211905 0.850427 1.554957 -0.888463 -1.508808 26 -1.179240 0.238923 1.756671 -0.747571 0.543625 -0.159609 -0.051458 27 0.025645 0.932436 -1.694531 -0.182236 -1.072710 0.466764 -0.072673 28 0.439086 0.812684 -0.128932 -0.142506 -1.137207 0.462001 -0.159466 29 -0.909806 -0.312006 0.383630 -0.631606 1.321415 -0.004799 -2.008210 7 8 9 24 25 26 27 \ 0 2.565646 1.431256 1.340309 0.875906 -2.211372 0.974466 -2.006747 1 -0.097883 0.695775 0.341734 -1.743161 -0.826591 -0.345352 1.314232 2 -0.082240 -2.182937 0.380396 1.266143 0.299368 -0.863838 0.408204 3 -0.489682 0.369374 -0.034571 0.221471 -0.744471 0.758527 1.729689 4 0.901805 1.171216 0.520260 0.650776 -1.461665 -1.137707 -0.891060 5 -0.260838 0.281957 1.523962 -0.008434 1.952541 -1.056652 0.533946 6 0.576897 1.146000 1.487349 2.015523 -1.833722 1.771740 -0.670027 .. ... ... ... ... ... ... ... 23 0.065624 0.307665 -1.898358 1.389045 -0.873585 -0.699862 0.812477 24 1.010694 0.877138 -0.611561 -1.040389 -0.796211 0.241596 0.385922 25 -0.617855 0.536164 2.175585 1.872601 -2.513465 -0.139184 0.810491 26 0.937882 0.617547 0.287918 -1.584814 0.307941 1.809049 0.296237 27 -0.026233 -0.051744 0.001402 0.150664 -3.060395 0.040268 0.066091 28 -1.788308 0.753604 0.918071 0.922729 0.869610 0.364726 -0.226101 29 -0.481634 -2.056211 -2.106095 0.039227 0.211283 1.440190 -0.989193 28 29 0 -0.410001 -0.078638 1 0.690579 0.995761 2 -1.048089 -0.025747 3 -0.964980 -0.845696 4 -0.693921 1.613616 5 -1.226970 0.040403 6 0.049307 -0.521493 .. ... ... 23 -0.469503 1.142702 24 -0.486078 0.433042 25 0.571599 -0.000676 26 -0.143550 0.289401 27 -0.192862 1.979055 28 -0.657647 -0.952699 29 0.313335 -0.399709 [30 rows x 16 columns]
aggregate
In R you may want to split data into subsets and compute the mean for each. Using a data.frame called df
and splitting it into groups by1
and by2
:
df <- data.frame( v1 = c(1,3,5,7,8,3,5,NA,4,5,7,9), v2 = c(11,33,55,77,88,33,55,NA,44,55,77,99), by1 = c("red", "blue", 1, 2, NA, "big", 1, 2, "red", 1, NA, 12), by2 = c("wet", "dry", 99, 95, NA, "damp", 95, 99, "red", 99, NA, NA)) aggregate(x=df[, c("v1", "v2")], by=list(mydf2$by1, mydf2$by2), FUN = mean)
The groupby()
method is similar to base R aggregate
function.
In [9]: df = pd.DataFrame({ ...: 'v1': [1,3,5,7,8,3,5,np.nan,4,5,7,9], ...: 'v2': [11,33,55,77,88,33,55,np.nan,44,55,77,99], ...: 'by1': ["red", "blue", 1, 2, np.nan, "big", 1, 2, "red", 1, np.nan, 12], ...: 'by2': ["wet", "dry", 99, 95, np.nan, "damp", 95, 99, "red", 99, np.nan, ...: np.nan] ...: }) ...: In [10]: g = df.groupby(['by1','by2']) In [11]: g[['v1','v2']].mean() Out[11]: v1 v2 by1 by2 1 95 5.0 55.0 99 5.0 55.0 2 95 7.0 77.0 99 NaN NaN big damp 3.0 33.0 blue dry 3.0 33.0 red red 4.0 44.0 wet 1.0 11.0
For more details and examples see the groupby documentation.
match
/ %in%
A common way to select data in R is using %in%
which is defined using the function match
. The operator %in%
is used to return a logical vector indicating if there is a match or not:
s <- 0:4 s %in% c(2,4)
The isin()
method is similar to R %in%
operator:
In [12]: s = pd.Series(np.arange(5),dtype=np.float32) In [13]: s.isin([2, 4]) Out[13]: 0 False 1 False 2 True 3 False 4 True dtype: bool
The match
function returns a vector of the positions of matches of its first argument in its second:
s <- 0:4 match(s, c(2,4))
The apply()
method can be used to replicate this:
In [14]: s = pd.Series(np.arange(5),dtype=np.float32) In [15]: pd.Series(pd.match(s,[2,4],np.nan)) Out[15]: 0 NaN 1 NaN 2 0.0 3 NaN 4 1.0 dtype: float64
For more details and examples see the reshaping documentation.
tapply
tapply
is similar to aggregate
, but data can be in a ragged array, since the subclass sizes are possibly irregular. Using a data.frame called baseball
, and retrieving information based on the array team
:
baseball <- data.frame(team = gl(5, 5, labels = paste("Team", LETTERS[1:5])), player = sample(letters, 25), batting.average = runif(25, .200, .400)) tapply(baseball$batting.average, baseball.example$team, max)
In pandas
we may use pivot_table()
method to handle this:
In [16]: import random In [17]: import string In [18]: baseball = pd.DataFrame({ ....: 'team': ["team %d" % (x+1) for x in range(5)]*5, ....: 'player': random.sample(list(string.ascii_lowercase),25), ....: 'batting avg': np.random.uniform(.200, .400, 25) ....: }) ....: In [19]: baseball.pivot_table(values='batting avg', columns='team', aggfunc=np.max) Out[19]: team team 1 0.394457 team 2 0.395730 team 3 0.343015 team 4 0.388863 team 5 0.377379 Name: batting avg, dtype: float64
For more details and examples see the reshaping documentation.
subset
New in version 0.13.
The query()
method is similar to the base R subset
function. In R you might want to get the rows of a data.frame
where one column?s values are less than another column?s values:
df <- data.frame(a=rnorm(10), b=rnorm(10)) subset(df, a <= b) df[df$a <= df$b,] # note the comma
In pandas
, there are a few ways to perform subsetting. You can use query()
or pass an expression as if it were an index/slice as well as standard boolean indexing:
In [20]: df = pd.DataFrame({'a': np.random.randn(10), 'b': np.random.randn(10)}) In [21]: df.query('a <= b') Out[21]: a b 0 -1.003455 -0.990738 1 0.083515 0.548796 3 -0.524392 0.904400 4 -0.837804 0.746374 8 -0.507219 0.245479 In [22]: df[df.a <= df.b] Out[22]: a b 0 -1.003455 -0.990738 1 0.083515 0.548796 3 -0.524392 0.904400 4 -0.837804 0.746374 8 -0.507219 0.245479 In [23]: df.loc[df.a <= df.b] Out[23]: a b 0 -1.003455 -0.990738 1 0.083515 0.548796 3 -0.524392 0.904400 4 -0.837804 0.746374 8 -0.507219 0.245479
For more details and examples see the query documentation.
with
New in version 0.13.
An expression using a data.frame called df
in R with the columns a
and b
would be evaluated using with
like so:
df <- data.frame(a=rnorm(10), b=rnorm(10)) with(df, a + b) df$a + df$b # same as the previous expression
In pandas
the equivalent expression, using the eval()
method, would be:
In [24]: df = pd.DataFrame({'a': np.random.randn(10), 'b': np.random.randn(10)}) In [25]: df.eval('a + b') Out[25]: 0 -0.920205 1 -0.860236 2 1.154370 3 0.188140 4 -1.163718 5 0.001397 6 -0.825694 7 -1.138198 8 -1.708034 9 1.148616 dtype: float64 In [26]: df.a + df.b # same as the previous expression Out[26]: 0 -0.920205 1 -0.860236 2 1.154370 3 0.188140 4 -1.163718 5 0.001397 6 -0.825694 7 -1.138198 8 -1.708034 9 1.148616 dtype: float64
In certain cases eval()
will be much faster than evaluation in pure Python. For more details and examples see the eval documentation.
plyr
plyr
is an R library for the split-apply-combine strategy for data analysis. The functions revolve around three data structures in R, a
for arrays
, l
for lists
, and d
for data.frame
. The table below shows how these data structures could be mapped in Python.
R | Python |
---|---|
array | list |
lists | dictionary or list of objects |
data.frame | dataframe |
ddply
An expression using a data.frame called df
in R where you want to summarize x
by month
:
require(plyr) df <- data.frame( x = runif(120, 1, 168), y = runif(120, 7, 334), z = runif(120, 1.7, 20.7), month = rep(c(5,6,7,8),30), week = sample(1:4, 120, TRUE) ) ddply(df, .(month, week), summarize, mean = round(mean(x), 2), sd = round(sd(x), 2))
In pandas
the equivalent expression, using the groupby()
method, would be:
In [27]: df = pd.DataFrame({ ....: 'x': np.random.uniform(1., 168., 120), ....: 'y': np.random.uniform(7., 334., 120), ....: 'z': np.random.uniform(1.7, 20.7, 120), ....: 'month': [5,6,7,8]*30, ....: 'week': np.random.randint(1,4, 120) ....: }) ....: In [28]: grouped = df.groupby(['month','week']) In [29]: grouped['x'].agg([np.mean, np.std]) Out[29]: mean std month week 5 1 71.840596 52.886392 2 71.904794 55.786805 3 89.845632 49.892367 6 1 97.730877 52.442172 2 93.369836 47.178389 3 96.592088 58.773744 7 1 59.255715 43.442336 2 69.634012 28.607369 3 84.510992 59.761096 8 1 104.787666 31.745437 2 69.717872 53.747188 3 79.892221 52.950459
For more details and examples see the groupby documentation.
reshape / reshape2
melt.array
An expression using a 3 dimensional array called a
in R where you want to melt it into a data.frame:
a <- array(c(1:23, NA), c(2,3,4)) data.frame(melt(a))
In Python, since a
is a list, you can simply use list comprehension.
In [30]: a = np.array(list(range(1,24))+[np.NAN]).reshape(2,3,4) In [31]: pd.DataFrame([tuple(list(x)+[val]) for x, val in np.ndenumerate(a)]) Out[31]: 0 1 2 3 0 0 0 0 1.0 1 0 0 1 2.0 2 0 0 2 3.0 3 0 0 3 4.0 4 0 1 0 5.0 5 0 1 1 6.0 6 0 1 2 7.0 .. .. .. .. ... 17 1 1 1 18.0 18 1 1 2 19.0 19 1 1 3 20.0 20 1 2 0 21.0 21 1 2 1 22.0 22 1 2 2 23.0 23 1 2 3 NaN [24 rows x 4 columns]
melt.list
An expression using a list called a
in R where you want to melt it into a data.frame:
a <- as.list(c(1:4, NA)) data.frame(melt(a))
In Python, this list would be a list of tuples, so DataFrame()
method would convert it to a dataframe as required.
In [32]: a = list(enumerate(list(range(1,5))+[np.NAN])) In [33]: pd.DataFrame(a) Out[33]: 0 1 0 0 1.0 1 1 2.0 2 2 3.0 3 3 4.0 4 4 NaN
For more details and examples see the Into to Data Structures documentation.
melt.data.frame
An expression using a data.frame called cheese
in R where you want to reshape the data.frame:
cheese <- data.frame( first = c('John', 'Mary'), last = c('Doe', 'Bo'), height = c(5.5, 6.0), weight = c(130, 150) ) melt(cheese, id=c("first", "last"))
In Python, the melt()
method is the R equivalent:
In [34]: cheese = pd.DataFrame({'first' : ['John', 'Mary'], ....: 'last' : ['Doe', 'Bo'], ....: 'height' : [5.5, 6.0], ....: 'weight' : [130, 150]}) ....: In [35]: pd.melt(cheese, id_vars=['first', 'last']) Out[35]: first last variable value 0 John Doe height 5.5 1 Mary Bo height 6.0 2 John Doe weight 130.0 3 Mary Bo weight 150.0 In [36]: cheese.set_index(['first', 'last']).stack() # alternative way Out[36]: first last John Doe height 5.5 weight 130.0 Mary Bo height 6.0 weight 150.0 dtype: float64
For more details and examples see the reshaping documentation.
cast
In R acast
is an expression using a data.frame called df
in R to cast into a higher dimensional array:
df <- data.frame( x = runif(12, 1, 168), y = runif(12, 7, 334), z = runif(12, 1.7, 20.7), month = rep(c(5,6,7),4), week = rep(c(1,2), 6) ) mdf <- melt(df, id=c("month", "week")) acast(mdf, week ~ month ~ variable, mean)
In Python the best way is to make use of pivot_table()
:
In [37]: df = pd.DataFrame({ ....: 'x': np.random.uniform(1., 168., 12), ....: 'y': np.random.uniform(7., 334., 12), ....: 'z': np.random.uniform(1.7, 20.7, 12), ....: 'month': [5,6,7]*4, ....: 'week': [1,2]*6 ....: }) ....: In [38]: mdf = pd.melt(df, id_vars=['month', 'week']) In [39]: pd.pivot_table(mdf, values='value', index=['variable','week'], ....: columns=['month'], aggfunc=np.mean) ....: Out[39]: month 5 6 7 variable week x 1 114.001700 132.227290 65.808204 2 124.669553 147.495706 82.882820 y 1 225.636630 301.864228 91.706834 2 57.692665 215.851669 218.004383 z 1 17.793871 7.124644 17.679823 2 15.068355 13.873974 9.394966
Similarly for dcast
which uses a data.frame called df
in R to aggregate information based on Animal
and FeedType
:
df <- data.frame( Animal = c('Animal1', 'Animal2', 'Animal3', 'Animal2', 'Animal1', 'Animal2', 'Animal3'), FeedType = c('A', 'B', 'A', 'A', 'B', 'B', 'A'), Amount = c(10, 7, 4, 2, 5, 6, 2) ) dcast(df, Animal ~ FeedType, sum, fill=NaN) # Alternative method using base R with(df, tapply(Amount, list(Animal, FeedType), sum))
Python can approach this in two different ways. Firstly, similar to above using pivot_table()
:
In [40]: df = pd.DataFrame({ ....: 'Animal': ['Animal1', 'Animal2', 'Animal3', 'Animal2', 'Animal1', ....: 'Animal2', 'Animal3'], ....: 'FeedType': ['A', 'B', 'A', 'A', 'B', 'B', 'A'], ....: 'Amount': [10, 7, 4, 2, 5, 6, 2], ....: }) ....: In [41]: df.pivot_table(values='Amount', index='Animal', columns='FeedType', aggfunc='sum') Out[41]: FeedType A B Animal Animal1 10.0 5.0 Animal2 2.0 13.0 Animal3 6.0 NaN
The second approach is to use the groupby()
method:
In [42]: df.groupby(['Animal','FeedType'])['Amount'].sum() Out[42]: Animal FeedType Animal1 A 10 B 5 Animal2 A 2 B 13 Animal3 A 6 Name: Amount, dtype: int64
For more details and examples see the reshaping documentation or the groupby documentation.
factor
New in version 0.15.
pandas has a data type for categorical data.
cut(c(1,2,3,4,5,6), 3) factor(c(1,2,3,2,2,3))
In pandas this is accomplished with pd.cut
and astype("category")
:
In [43]: pd.cut(pd.Series([1,2,3,4,5,6]), 3) Out[43]: 0 (0.995, 2.667] 1 (0.995, 2.667] 2 (2.667, 4.333] 3 (2.667, 4.333] 4 (4.333, 6] 5 (4.333, 6] dtype: category Categories (3, object): [(0.995, 2.667] < (2.667, 4.333] < (4.333, 6]] In [44]: pd.Series([1,2,3,2,2,3]).astype("category") Out[44]: 0 1 1 2 2 3 3 2 4 2 5 3 dtype: category Categories (3, int64): [1, 2, 3]
For more details and examples see categorical introduction and the API documentation. There is also a documentation regarding the differences to R?s factor.
Please login to continue.