!echo %R_HOME%

# Some prep work to get the data from R and into pandas
%matplotlib inline
#bad tetst : move magic befor module imprort
#%load_ext rpy2.ipython

import rpy2
%load_ext rpy2.ipython
from rpy2.robjects.conversion import ri2py
from rpy2.ipython.rmagic import ri2ipython
ri2ipython.register(rpy2.robjects.Sexp, ri2py)
import numpy as np
import pandas as pd
import seaborn as sns

pd.set_option("display.max_rows", 5)

"""
Explanation: Winpython with R : comparing DPLYR and Pandas
It is based on the Thomas Augspurger comparison Notebook (refreshed for Pandas 0.16.0)
http://nbviewer.ipython.org/urls/gist.githubusercontent.com/TomAugspurger/6e052140eaa5fdb6e8c0/raw/811585624e843f3f80b9b6fe89e18119d7d2d73c/dplyr_pandas.ipynb
We just play the "R" code at the same time, instead of keeping it in comments
End of explanation
"""


%%R
library("dplyr") # for functions
library("nycflights13")
write.csv(flights, "flights.csv")

"""
Explanation: Transform this Markdown cell to a Code cell, if ever you need to re-feed a basic R environnement
%R install.packages("tidyr")
%R install.packages("dplyr") 
%R install.packages("ggplot2")
%R install.packages("rvest")
%R install.packages('RSQLite') 
%R install.packages("zoo") 
%R install.packages("forecast") 
%R install.packages('R.utils')
%R install.packages("nycflights13")
%R install.packages('hflights')
Thomas Augspurger part (with comments replaced by true %R code)
This notebook compares pandas
and dplyr.
The comparison is just on syntax (verbage), not performance. Whether you're an R user looking to switch to pandas (or the other way around), I hope this guide will help ease the transition.
We'll work through the introductory dplyr vignette to analyze some flight data.
I'm working on a better layout to show the two packages side by side.
But for now I'm just putting the dplyr code in a comment above each python call.
End of explanation
"""


flights = pd.read_csv("flights.csv", index_col=0)

%R dim(flights)

# dim(flights)   <--- The R code
flights.shape  # <--- The python code

%R head(flights)

# head(flights)
flights.head()

"""
Explanation: Data: nycflights13
End of explanation
"""


%R filter(flights, month == 1, day == 1)

# filter(flights, month == 1, day == 1)
flights.query("month == 1 & day == 1")

"""
Explanation: Single table verbs
dplyr has a small set of nicely defined verbs. I've listed their closest pandas verbs.
<table>
  <tr>
    <td><b>dplyr</b></td>
    <td><b>pandas</b></td>
  </tr>
  <tr>
    <td>filter() (and slice())</td>
    <td>query() (and loc[], iloc[])</td>
  </tr>
  <tr>
    <td>arrange()</td>
    <td>sort()</td>
  </tr>
  <tr>
  <td>select() (and rename())</td>
    <td>\_\_getitem\_\_ (and rename())</td>
  </tr>
  <tr>
  <td>distinct()</td>
    <td>drop_duplicates()</td>
  </tr>
  <tr>
    <td>mutate() (and transmute())</td>
    <td>None</td>
  </tr>
  <tr>
    <td>summarise()</td>
    <td>None</td>
  </tr>
  <tr>
    <td>sample_n() and sample_frac()</td>
    <td>None</td>
  </tr>
</table>

Some of the "missing" verbs in pandas are because there are other, different ways of achieving the same goal. For example summarise is spread across mean, std, etc. Others, like sample_n, just haven't been implemented yet.
Filter rows with filter(), query()
End of explanation
"""


%R flights[flights$month == 1 & flights$day == 1, ]

# flights[flights$month == 1 & flights$day == 1, ]
flights[(flights.month == 1) & (flights.day == 1)]

%R slice(flights, 1:10)

# slice(flights, 1:10)
flights.iloc[:9]

"""
Explanation: The more verbose version:
End of explanation
"""


%R arrange(flights, year, month, day) 

# arrange(flights, year, month, day) 
flights.sort(['year', 'month', 'day'])

%R arrange(flights, desc(arr_delay))

# arrange(flights, desc(arr_delay))
flights.sort('arr_delay', ascending=False)

"""
Explanation: Arrange rows with arrange(), sort()
End of explanation
"""


%R select(flights, year, month, day) 

# select(flights, year, month, day) 
flights[['year', 'month', 'day']]

%R select(flights, year:day) 

# select(flights, year:day) 

# No real equivalent here. Although I think this is OK.
# Typically I'll have the columns I want stored in a list
# somewhere, which can be passed right into __getitem__ ([]).

%%R
select(flights, -(year:day)) 

# select(flights, -(year:day)) 

# Again, simliar story. I would just use
# flights.drop(cols_to_drop, axis=1)
# or fligths[flights.columns.difference(pd.Index(cols_to_drop))]
# point to dplyr!

%R select(flights, tail_num = tailnum)

# select(flights, tail_num = tailnum)
flights.rename(columns={'tailnum': 'tail_num'})['tail_num']

"""
Explanation: Select columns with select(), []
End of explanation
"""


%R rename(flights, tail_num = tailnum)

# rename(flights, tail_num = tailnum)
flights.rename(columns={'tailnum': 'tail_num'})

"""
Explanation: But like Hadley mentions, not that useful since it only returns the one column. dplyr and pandas compare well here.
End of explanation
"""


%R distinct(select(flights, tailnum))

# distinct(select(flights, tailnum))
flights.tailnum.unique()

"""
Explanation: Pandas is more verbose, but the the argument to columns can be any mapping. So it's often used with a function to perform a common task, say df.rename(columns=lambda x: x.replace('-', '_')) to replace any dashes with underscores. Also, rename (the pandas version) can be applied to the Index.
Extract distinct (unique) rows
End of explanation
"""


%R distinct(select(flights, origin, dest))

# distinct(select(flights, origin, dest))
flights[['origin', 'dest']].drop_duplicates()

"""
Explanation: FYI this returns a numpy array instead of a Series.
End of explanation
"""


%R mutate(flights, gain = arr_delay - dep_delay, speed = distance / air_time * 60)

# mutate(flights,
#   gain = arr_delay - dep_delay,
#   speed = distance / air_time * 60)

#before pandas 0.16.0
# flights['gain'] = flights.arr_delay - flights.dep_delay
# flights['speed'] = flights.distance / flights.air_time * 60
# flights
flights.assign(gain=flights.arr_delay - flights.dep_delay,
               speed=flights.distance / flights.air_time * 60)

%R mutate(flights, gain = arr_delay - dep_delay, gain_per_hour = gain / (air_time / 60) )


# mutate(flights,
#   gain = arr_delay - dep_delay,
#   gain_per_hour = gain / (air_time / 60)
# )

#before pandas 0.16.0
# flights['gain'] = flights.arr_delay - flights.dep_delay
# flights['gain_per_hour'] = flights.gain / (flights.air_time / 60)
# flights
(flights.assign(gain=flights.arr_delay - flights.dep_delay)
        .assign(gain_per_hour = lambda df: df.gain / (df.air_time / 60)))

"""
Explanation: OK, so dplyr wins there from a consistency point of view. unique is only defined on Series, not DataFrames. The original intention for drop_duplicates is to check for records that were accidentally included twice. This feels a bit hacky using it to select the distinct combinations, but it works!
Add new columns with mutate()
End of explanation
"""


%R transmute(flights,  gain = arr_delay - dep_delay,  gain_per_hour = gain / (air_time / 60)  )

# transmute(flights,
#   gain = arr_delay - dep_delay,
#   gain_per_hour = gain / (air_time / 60)
# )

#before pandas 0.16.0
# flights['gain'] = flights.arr_delay - flights.dep_delay
# flights['gain_per_hour'] = flights.gain / (flights.air_time / 60)
# flights[['gain', 'gain_per_hour']]
(flights.assign(gain=flights.arr_delay - flights.dep_delay)
        .assign(gain_per_hour = lambda df: df.gain / (df.air_time / 60))
        [['gain', 'gain_per_hour']])

"""
Explanation: The first example is pretty much identical (aside from the names, mutate vs. assign).
The second example just comes down to language differences. In R, it's possible to implement a function like mutate where you can refer to gain in the line calcuating gain_per_hour, even though gain hasn't actually been calcuated yet.
In Python, you can have arbitrary keyword arguments to functions (which we needed for .assign), but the order of the argumnets is arbitrary. So you can't have something like df.assign(x=df.a / df.b, y=x **2), because you don't know whether x or y will come first (you'd also get an error saying x is undefined.
To work around that with pandas, you'll need to split up the assigns, and pass in a callable to the second assign. The callable looks at itself to find a column named gain. Since the line above returns a DataFrame with the gain column added, the pipeline goes through just fine.
End of explanation
"""


flights.dep_delay.mean()

"""
Explanation: Summarise values with summarise()
End of explanation
"""


%R sample_n(flights, 10)

# sample_n(flights, 10)
flights.loc[np.random.choice(flights.index, 10)]

%R sample_frac(flights, 0.01)

# sample_frac(flights, 0.01)
flights.iloc[np.random.randint(0, len(flights),
                               .1 * len(flights))]

"""
Explanation: Randomly sample rows with sample_n() and sample_frac()
There's an open PR on Github to make this nicer (closer to dplyr). For now you can drop down to numpy.
End of explanation
"""


%R planes <- group_by(flights, tailnum)
%R delay <- summarise(planes, count = n(),dist = mean(distance, na.rm = TRUE), delay = mean(arr_delay, na.rm = TRUE))
%R delay <- filter(delay, count > 20, dist < 2000)


# planes <- group_by(flights, tailnum)
# delay <- summarise(planes,
#   count = n(),
#   dist = mean(distance, na.rm = TRUE),
#   delay = mean(arr_delay, na.rm = TRUE))
# delay <- filter(delay, count > 20, dist < 2000)

planes = flights.groupby("tailnum")
delay = (planes.agg({"year": "count",
                     "distance": "mean",
                     "arr_delay": "mean"})
               .rename(columns={"distance": "dist",
                                "arr_delay": "delay",
                                "year": "count"})
               .query("count > 20 & dist < 2000"))
delay

"""
Explanation: Grouped operations
End of explanation
"""


import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(12, 6))

sns.regplot("dist", "delay", data=delay, lowess=True, ax=ax,
            scatter_kws={'color': 'k', 'alpha': .5, 's': delay['count'] / 10}, ci=90,
            line_kws={'linewidth': 3});

"""
Explanation: For me, dplyr's n() looked is a bit starge at first, but it's already growing on me.
I think pandas is more difficult for this particular example.
There isn't as natural a way to mix column-agnostic aggregations (like count) with column-specific aggregations like the other two. You end up writing could like .agg{'year': 'count'} which reads, "I want the count of year", even though you don't care about year specifically.
Additionally assigning names can't be done as cleanly in pandas; you have to just follow it up with a rename like before.
We may as well reproduce the graph. It looks like ggplots geom_smooth is some kind of lowess smoother. We can either us seaborn:
End of explanation
"""


import statsmodels.api as sm

smooth = sm.nonparametric.lowess(delay.delay, delay.dist, frac=1/8)
ax = delay.plot(kind='scatter', x='dist', y = 'delay', figsize=(12, 6),
                color='k', alpha=.5, s=delay['count'] / 10)
ax.plot(smooth[:, 0], smooth[:, 1], linewidth=3);
std = smooth[:, 1].std()
ax.fill_between(smooth[:, 0], smooth[:, 1] - std, smooth[:, 1] + std, alpha=.25);

%R destinations <- group_by(flights, dest)
%R summarise(destinations, planes = n_distinct(tailnum), flights = n())

# destinations <- group_by(flights, dest)
# summarise(destinations,
#   planes = n_distinct(tailnum),
#   flights = n()
# )

destinations = flights.groupby('dest')
destinations.agg({
    'tailnum': lambda x: len(x.unique()),
    'year': 'count'
    }).rename(columns={'tailnum': 'planes',
                       'year': 'flights'})

"""
Explanation: Or using statsmodels directly for more control over the lowess, with an extremely lazy "confidence interval".
End of explanation
"""


%R daily <- group_by(flights, year, month, day)
%R (per_day   <- summarise(daily, flights = n()))

# daily <- group_by(flights, year, month, day)
# (per_day   <- summarise(daily, flights = n()))

daily = flights.groupby(['year', 'month', 'day'])
per_day = daily['distance'].count()
per_day

%R (per_month <- summarise(per_day, flights = sum(flights)))

# (per_month <- summarise(per_day, flights = sum(flights)))
per_month = per_day.groupby(level=['year', 'month']).sum()
per_month

%R (per_year  <- summarise(per_month, flights = sum(flights)))

# (per_year  <- summarise(per_month, flights = sum(flights)))
per_year = per_month.sum()
per_year

"""
Explanation: Similar to how dplyr provides optimized C++ versions of most of the summarise functions, pandas uses cython optimized versions for most of the agg methods.
End of explanation
"""


%R flights %>% group_by(year, month, day) %>%  select(arr_delay, dep_delay) %>%  summarise( arr = mean(arr_delay, na.rm = TRUE),  dep = mean(dep_delay, na.rm = TRUE)) %>% filter(arr > 30 | dep > 30)

# flights %>%
#   group_by(year, month, day) %>%
#   select(arr_delay, dep_delay) %>%
#   summarise(
#     arr = mean(arr_delay, na.rm = TRUE),
#     dep = mean(dep_delay, na.rm = TRUE)
#   ) %>%
#   filter(arr > 30 | dep > 30)
(
flights.groupby(['year', 'month', 'day'])
    [['arr_delay', 'dep_delay']]
    .mean()
    .query('arr_delay > 30 | dep_delay > 30')
)

"""
Explanation: I'm not sure how dplyr is handling the other columns, like year, in the last example. With pandas, it's clear that we're grouping by them since they're included in the groupby. For the last example, we didn't group by anything, so they aren't included in the result.
Chaining
Any follower of Hadley's twitter account will know how much R users love the %&gt;% (pipe) operator. And for good reason!
End of explanation
"""


