r/learnpython • • Jun 04 '20

Finally used Python at work for something useful

I'm a policy consultant and deal with a lot of basic statistics at work, particularly when we create city indexes on various topics (SDG compliance, competitiveness, Sustainable mobility...). We're refining our criteria to make sure indicators on one index do not correlate negatively to each other to make sure they all "row in the same direction". I started ATBS a few weeks ago because there's a lot of Boring Stuff in my work with the idea being to move on to statistics and PyQGIS after that (I'm the official 'Guy Who Does Maps'). So today I did a few searches and created correlation matrices for our past indexes with this code.

import pandas as pd

df = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx')

#df = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx', sheet_name='your Excel sheet name')

#this imports only one sheet

corrMatrix = df.corr()

#print(corrMatrix)

import seaborn as sn

import matplotlib.pyplot as plt

sn.heatmap(corrMatrix, annot=True)

plt.show() #this is if you want the image

df.to_excel("output.xlsx") #this exports the excel

Boom. I'd probably have spent a long, long time in Excel dealing with missing values and whatnot and this was done in 10 minutes max.

418 Upvotes

45 comments sorted by

23

u/djeiwnbdhxixlnebejei Jun 04 '20

Check out Jupyter notebooks if you do this kind of work, makes it a lot easier

8

u/tururut_tururut Jun 04 '20

I have used spyder, but I need to check Jupyter. The other colleague that uses Python uses both things.

8

u/monyouhoopz Jun 04 '20

Use vscode with the python extension. It supports Jupyter notebooks and it lightweight and awesome. I switched from Jupyter lab and barely use it now.

4

u/[deleted] Jun 04 '20

Hi I have been using pycharm so far. Why would one use Jupyter, what can I do with it?

4

u/[deleted] Jun 04 '20

I started with pycharm and moved to juypter. I work as a data analyst and I cant reccomend it enough.

Basically you get to visualise your code as youre going along. Unsure if youre familiar with pandas but when your working on the equivilent of a worksheet (dataframe or df) you can make changes and view them in the output of the cell youre working in.

You dont have to run the whole script again. I found it a huge advantage. If youre doing data analysis in pycharm you should change straight away. Im sure you will blown away like I was.

1

u/CBizCool Jun 04 '20

Building this is Jupyter means each time you need to launch the notebook and run the code correct, is there a way to build out a script and save it as an executable .py where one just has to double click the file for the script for the script to run?

2

u/djeiwnbdhxixlnebejei Jun 04 '20

you can easily just pull the code into a script. you can also schedule the notebook to run.

1

u/jahaz Jun 05 '20

Yeah Netflix has a couple great articles about how they use it. Very powerful solution

8

u/kimjeongpwn Jun 04 '20

What does the code do? Sorry still very new so don't know about pandas.

9

u/dr_hippie Jun 04 '20

It's a python library to create dataframes (think excel table) and use them to do a bunch of analysis

1

u/dackou77 Jun 04 '20

Please give an example

8

u/tururut_tururut Jun 04 '20

In concrete, I'm doing a matrix of correlation, this means that it gives me how correlated is each indicator to each one.

1

u/kimjeongpwn Jun 05 '20

Ahhh, so the .corr() function is built-in to pandas and does that without more lines of code?

2

u/dackou77 Jun 04 '20

Can you describe the whole process step by step?

12

u/tururut_tururut Jun 04 '20

Yes sure. First, I import pandas to allow me to read an excel file and work with data frames. Then I ask python to read an excel sheet with the results of a bunch of indicators for a bunch of cities. Next, I ask it to make a correlation matrix, which means that all indicators are plotted both in the vertical and horizontal axis and each cell contains the correlation coefficient between each pair of indicators. After that, I have the possibility to make an image output of it using seaborn and matplotlib or export it as a new excel field, which is what I do.

4

u/dackou77 Jun 04 '20

Thank you for explaining.

2

u/[deleted] Jun 04 '20

What industry are you in?

2

u/tururut_tururut Jun 04 '20

Policy consulting, I'm actually a political scientist by trade.

2

u/impshum x != y % z Jun 04 '20

You should probably include error handling. Just saying!

1

u/tururut_tururut Jun 04 '20

I'm not sure about how to do that, though.

1

u/[deleted] Jun 05 '20

Depends on your specific problem. Tons of resources online once you have the general idea down for what it is that you want to do.

10

u/Crypt0Nihilist Jun 04 '20

There is a lot of career mileage to be had out of being the "Guy Who Does Maps". Everyone loves to see their data on a map. Sometimes it isn't even a population density map in disguise...

4

u/Mr_Batfleck Jun 04 '20

You can even change the type of correlation that you want. By default it is set to Pearson's Correlation you can change it to Spearman' s Correlation and Kendall Tau using the 'method' argument.

3

u/[deleted] Jun 04 '20

Good job! I could automate a lot of the stuff I do, but my work will not let me.

3

u/Blessing_Dryad Jun 04 '20

This is why I love programming. Programming is a tool to make your life easy. Let the computers do the dirty work for you. You should sit back, relax and have a cup of nice, warm coffee. That's how big brain people live.

2

u/Sotovision Jun 04 '20

Cool! I'm still struggling with numpy :(

2

u/chombi94 Jun 04 '20

What are you struggling with? :)

1

u/Sotovision Jun 04 '20

It's not really with numpy, in fact is with sqlite3 library. Because I'm doing like a meal planner so the program is going to choose my meals for the day randomly, but I want to retrieve the data and turn the data into an array so i can slice/index and return the random value from my meals (which is stored in a database that I created and its indexed as breakfast, lunch and dinner ). So I know that I have to query the data and choose randomly 1 element from each column.

1

u/TheRiteGuy Jun 04 '20

Yeah, i'm just learning, but this stuff is still way over my head. I get what he's doing, but I'm not understanding how such a simple code does such a complex operation.

2

u/bumpkinspicefatte Jun 04 '20

Your solution and business problem goes way over my dumb head, but congrats anyways. Would’ve loved to see maybe a video demonstration of how it works, but maybe that there is too much.

2

u/testfire10 Github mokrunka Jun 04 '20

Great job. I had a similar experience with my first work project using python (I also work in an industry not really related to software programming).

Learn the lesson I didn't learn. Go back and comment the shit out of it and clean up your code so that you know what it means and how to use it in a year.

2

u/TheClimber7 Jun 05 '20

Don’t tell your manager

2

u/[deleted] Jun 04 '20

Nice.

0

u/[deleted] Jun 04 '20

[deleted]

16

u/beje_ro Jun 04 '20

I am also a beginner and I have a question: would this be a candidate scenario for using jupyter notebook?

12

u/Socrato Jun 04 '20 edited Jun 04 '20

Yes, you would need to include the %matplotlib inline in one of the top cells so that plots appear in the return. Also you will probably want to configure a larger default figure size.

edit: this is typically included in my 1st or second cell:

import pandas as pd  
import numpy as np  
import matplotlib.pyplot as plt   
%matplotlib inline
plt.rcParams['figure.figsize'] = (15.0, 7.5)

5

u/snip3r77 Jun 04 '20

is there still a need to include

%matplotlib inline?

3

u/Socrato Jun 04 '20

As far as I know, mine still requires it otherwise I just get the object returned with not plotting.

My workstation still runs 3.6.5 though, future versions may have removed this requirement.

2

u/constableVisit Jun 04 '20

Honestly, I tend to use Jupyter for everything. What would not be a candidate scenario for using a Jupyter notebook, outside of full-on internal code? (I hope this doesn't come off as sarcastic, this is a genuine question.)

1

u/vaja_ Jun 05 '20

Jupyter is great, but it can be annoying when working in a team and using version control.

1

u/constableVisit Jun 04 '20

How do you set up a script for reuse? Practical steps, ease of execution and comments etc.?

0

u/[deleted] Jun 04 '20

[deleted]

1

u/tururut_tururut Jun 05 '20

Spotted me there, I was supposed to export the correlation matrix, not the original data frame. I corrected that manually in the Spyder console but forgot to correct it in the script. Thanks!

1

u/constableVisit Jun 04 '20

I turned it into its own standalone deal.

Sorry yeah I meant to ask how you do this?

That and a comment would be the other way to do it, when it would take an equivalent number of lines, or greater number, to get the same effect (2 lines to import and call, 2 lines to use and comment, same diff).

Not sure I understood this one

0

u/[deleted] Jun 04 '20

[deleted]

3

u/Thundershrimp Jun 04 '20

Does cleansheets.py have to be in the same folder as the python file you are working on to call it like that? (from cleansheet import *)

1

u/constableVisit Jun 05 '20

Thanks!
Also, is it possible to set up a script so that you can just copy and paste one executable file into a folder and run it - it applies the script to the local folder and then self-terminates?

1

u/[deleted] Jun 05 '20

Beautifying like...resize columns, bold the headers, change font size...etc?

Hm..gives me an idea for my own stuff. Thanks!