1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
\section{Excel to Python}\label{excel-to-python}
\begin{itemize}
\tightlist
\item
Importing
\item
Plotting
\item
Statistical analysis
\end{itemize}
\subsection{\texorpdfstring{\textbf{How Excel Translates to
Python}}{How Excel Translates to Python}}\label{how-excel-translates-to-python}
Here's how common Excel functionalities map to Python:
\begin{longtable}[]{@{}
>{\raggedright\arraybackslash}p{(\columnwidth - 2\tabcolsep) * \real{0.2911}}
>{\raggedright\arraybackslash}p{(\columnwidth - 2\tabcolsep) * \real{0.7089}}@{}}
\toprule\noalign{}
\begin{minipage}[b]{\linewidth}\raggedright
\textbf{Excel Feature}
\end{minipage} & \begin{minipage}[b]{\linewidth}\raggedright
\textbf{Python Equivalent}
\end{minipage} \\
\midrule\noalign{}
\endhead
\bottomrule\noalign{}
\endlastfoot
Formulas (SUM, AVERAGE) & \texttt{numpy}, \texttt{pandas}
(\texttt{df.sum()}, \texttt{df.mean()}) \\
Sorting \& Filtering & \texttt{pandas.sort\_values()},
\texttt{df{[}df{[}\textquotesingle{}col\textquotesingle{}{]}\ \textgreater{}\ value{]}} \\
Conditional Formatting & \texttt{matplotlib} for highlighting \\
Pivot Tables & \texttt{pandas.pivot\_table()} \\
Charts \& Graphs & \texttt{matplotlib}, \texttt{seaborn},
\texttt{plotly} \\
Regression Analysis & \texttt{scipy.stats.linregress},
\texttt{sklearn.linear\_model} \\
Solver/Optimization & \texttt{scipy.optimize} \\
VBA Macros & Python scripting with \texttt{openpyxl}, \texttt{pandas},
or \texttt{xlwings} \\
\end{longtable}
\subsection{Statistical functions}\label{statistical-functions}
\paragraph{SUM}\label{sum}
Built-in:
\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{my\_array }\OperatorTok{=}\NormalTok{ [}\DecValTok{1}\NormalTok{, }\DecValTok{2}\NormalTok{, }\DecValTok{3}\NormalTok{, }\DecValTok{4}\NormalTok{, }\DecValTok{5}\NormalTok{]}
\NormalTok{total }\OperatorTok{=} \BuiltInTok{sum}\NormalTok{(my\_array)}
\BuiltInTok{print}\NormalTok{(total) }\CommentTok{\# Output: 15}
\end{Highlighting}
\end{Shaded}
Numpy:
\begin{Shaded}
\begin{Highlighting}[]
\ImportTok{import}\NormalTok{ numpy }\ImportTok{as}\NormalTok{ np}
\NormalTok{my\_array }\OperatorTok{=}\NormalTok{ np.array([}\DecValTok{1}\NormalTok{, }\DecValTok{2}\NormalTok{, }\DecValTok{3}\NormalTok{, }\DecValTok{4}\NormalTok{, }\DecValTok{5}\NormalTok{])}
\NormalTok{total }\OperatorTok{=}\NormalTok{ np.}\BuiltInTok{sum}\NormalTok{(my\_array)}
\BuiltInTok{print}\NormalTok{(total) }\CommentTok{\# Output: 15}
\end{Highlighting}
\end{Shaded}
\subsubsection{Average}\label{average}
Built-in:
\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{my\_array }\OperatorTok{=}\NormalTok{ [}\DecValTok{1}\NormalTok{, }\DecValTok{2}\NormalTok{, }\DecValTok{3}\NormalTok{, }\DecValTok{4}\NormalTok{, }\DecValTok{5}\NormalTok{]}
\NormalTok{average }\OperatorTok{=} \BuiltInTok{sum}\NormalTok{(my\_array) }\OperatorTok{/} \BuiltInTok{len}\NormalTok{(my\_array)}
\BuiltInTok{print}\NormalTok{(average) }\CommentTok{\# Output: 3.0}
\end{Highlighting}
\end{Shaded}
Numpy:
\begin{Shaded}
\begin{Highlighting}[]
\ImportTok{import}\NormalTok{ numpy }\ImportTok{as}\NormalTok{ np}
\NormalTok{my\_array }\OperatorTok{=}\NormalTok{ np.array([}\DecValTok{1}\NormalTok{, }\DecValTok{2}\NormalTok{, }\DecValTok{3}\NormalTok{, }\DecValTok{4}\NormalTok{, }\DecValTok{5}\NormalTok{])}
\NormalTok{average }\OperatorTok{=}\NormalTok{ np.mean(my\_array)}
\BuiltInTok{print}\NormalTok{(average) }\CommentTok{\# Output: 3.0}
\end{Highlighting}
\end{Shaded}
\subsection{Plotting}\label{plotting}
We can use the package \emph{matplotlib} to plot our graphs in python.
Matplotlib provides data visualization tools for the Scientific Python
Ecosystem. You can make very professional looking figures with this
tool.
Here is a section from the matplotlib documentation page that you can
run in python.
\begin{Shaded}
\begin{Highlighting}[]
\ImportTok{import}\NormalTok{ matplotlib.pyplot }\ImportTok{as}\NormalTok{ plt}
\NormalTok{fig, ax }\OperatorTok{=}\NormalTok{ plt.subplots() }\CommentTok{\# Create a figure containing a single Axes.}
\NormalTok{ax.plot([}\DecValTok{1}\NormalTok{, }\DecValTok{2}\NormalTok{, }\DecValTok{3}\NormalTok{, }\DecValTok{4}\NormalTok{], [}\DecValTok{1}\NormalTok{, }\DecValTok{4}\NormalTok{, }\DecValTok{2}\NormalTok{, }\DecValTok{3}\NormalTok{]) }\CommentTok{\# Plot some data on the Axes.}
\NormalTok{plt.show() }\CommentTok{\# Show the figure.}
\end{Highlighting}
\end{Shaded}
Check out the documentation pages for a
\href{https://matplotlib.org/stable/users/explain/quick_start.html\#a-simple-example}{simple
example} or more information on the types of plots you came create
\href{https://matplotlib.org/stable/plot_types/index.html}{here}.
|
