Return values that meet some criteria based in other columns - Pandas

Some simple tasks are much faster and simpler in pandas than in Excel.

For example: return values that meet some criteria based in other columns.

If we want to list all the basins with area greater than 100ha, a simple code will do.

Assuming we had copied this table from excel:

import pandas as pd

df0=pd.read_clipboard()

df0[df0.iloc[:,-1]>100].to_clipboard()

This code will put the result in the clipboard, to paste back into Excel (for ex.).

Excel to Python Pandas DataFrame

The most easy way is to use the clipboard. Just copy your table in Excel to the clipboard, and then call the read_clipboard method from pandas:

df1 = pd.read_clipboard()

Pandas can read Excel files directly too, with read_excel

df1 = pd.read_excel('tmp.xlsx',index_col=0, header=0)

Taking care with header and indexes, and other options as described here.

Multiple linear regression in Python

Sometimes we need to do a linear regression, and we know most used spreadsheet software does not do it well nor easily.

In the other hand, a multiple regression in Python, using the scikit-learn library - sklearn - it is rather simple.

import matplotlib.pyplot as plt
import pandas as pd
from sklearn.linear_model import LinearRegression

# Importing the dataset
dataset = pd.read_csv('data.csv')
# separate last column of dataset as dependent variable - y
X = dataset.iloc[:, :-1].values
y = dataset.iloc[:, -1].values

# build the regressor and print summary results
regressor = LinearRegression()
regressor.fit(X,y)
print('Coefficients:\t','\t'.join([str(c) for c in regressor.coef_]))
print('R2 =\t',regressor.score(X,y, sample_weight=None))

#plot the results if you like
y_pred = regressor.predict(X)
plt.scatter(y_pred,y)
plt.plot([min(y_pred),max(y_pred)],[min(y_pred),max(y_pred)])
plt.legend()
plt.show()

A Library to connect Excel with Python - PYTHON FOR EXCEL

PYTHON FOR EXCEL. FREE & OPEN SOURCE.

xlwings - Make Excel Fly!

xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa:
Scripting: Automate/interact with Excel from Python using a syntax close to VBA.

- Macros: Replace VBA macros with clean and powerful Python code.
- UDFs: Write User Defined Functions (UDFs) in Python (Windows only).

Numpy arrays and Pandas Series/DataFrames are fully supported. xlwings-powered workbooks are easy to distribute and work on Windows and Mac.

Install with: pip install xlwings;

It is already included in Winpython and in Anaconda.

Links

https://www.xlwings.org/

http://docs.xlwings.org/en/stable/index.html

https://winpython.github.io/

https://www.continuum.io/downloads

Python and Pandas - How to plot Multiple Curves with 5 Lines of Code

In this post I will show how to use pandas to do a minimalist but pretty line chart, with as many curves we want.

In this case I will use a I-D-F precipitation table, with lines corresponding to Return Periods (years) and columns corresponding to durations, in minutes. as shown below:

For the code to work properly, the table must have headers in the columns and lines, and the first cell have to be blank. Select the table you want in your SpreadSheet Editor, and copy it to clipboard.

Then, run the following code:

import pandas as pd

table = pd.read_clipboard()
tabTr = table.transpose().convert_objects(convert_numeric=True)
eixox = tabTr.index.values.astype(float)
tabTr.set_index(eixox).plot(grid=True)

And Voila!:

Text string to Excel

Sometimes we just want to handle some data, from or to excel.

Here is a tip that can help in your scripts, when you want to copy data from or to an Excel spreadsheet.

Microsoft Excel recognizes the tabulation character ('\t') as cell separation, and a line break character ('\n') as a normal line break.

For example, if you copy some string data to the clipboard, as explained in previous post, you can paste it to excel cells, if the data is separated by '\t' character.