Graphing/visualization - Data Analysis with Python 3 and Pandas

Welcome to part 2 of the data analysis with Python and Pandas tutorials, where we're learning about the prices of Avocados at the moment. Soon, we'll find a new dataset, but let's learn a few more things with this one. Where we left off, we were graphing the price from Albany over time, but it was quite messy. Here's a recap:

import pandas as pd

df = pd.read_csv("datasets/avocado.csv")

albany_df = df[df['region']=="Albany"]
albany_df.set_index("Date", inplace=True)

albany_df["AveragePrice"].plot()

<matplotlib.axes._subplots.AxesSubplot at 0x11fd925f8>

So dates are funky types of data, since they are strings, but also have order, at least to us. When it comes to dates, we have to help computers out a bit. Luckily for us, Pandas comes built in with ways to handle for dates. First, we need to convert the date column to datetime objects:

df = pd.read_csv("datasets/avocado.csv")

df['Date'] = pd.to_datetime(df['Date'])

albany_df = df[df['region']=="Albany"]
albany_df.set_index("Date", inplace=True)

albany_df["AveragePrice"].plot()

<matplotlib.axes._subplots.AxesSubplot at 0x11fa86828>

Alright, the formatting looks better in terms of axis, but that graph is pretty wild! Could we settle it down a bit? We could smooth the data with a rolling average.

To do this, let's make a new column, and apply some smoothing:

albany_df["AveragePrice"].rolling(25).mean().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1223cc278>

Hmm, so what happened? Pandas understands that a date is a date, and to sort the X axis, but I am now wondering if the dataframe itself is sorted. If it's not, that would seriously screw up our moving average calculations. This data may be indexed by date, but is it sorted? Let's see.

albany_df.sort_index(inplace=True)

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

What's this warning above? Should we be worried? Basically, all it's telling us is that we're doing operations on a copy of a slice of a dataframe, and to watch out because we might not be modifying what we were hoping to modify (like the main df). In this case, we're not trying to work with the main dataframe, so I think this warning is just plain annoying, but whatever. It's just a warning, not an error.

albany_df["AveragePrice"].rolling(25).mean().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1223ccf98>

And there we have it! A more useful summary of avocado prices for Albany over the years.

Visualizations are cool, but what if we want to save our new, smoother, data like above? We can give it a new column in our dataframe:

albany_df["price25ma"] = albany_df["AveragePrice"].rolling(25).mean()

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

albany_df.head()

Unnamed: 0

AveragePrice

Total Volume

4046

4225

4770

Total Bags

Small Bags

Large Bags

XLarge Bags

type

year

region

price25ma

Date

2015-01-04

1.22

40873.28

2819.50

28287.42

49.90

9716.46

9186.93

529.53

0.0

conventional

2015

Albany

NaN

2015-01-04

1.79

1373.95

57.42

153.88

0.00

1162.65

0.00

0.0

organic

2015

Albany

NaN

2015-01-11

1.24

41195.08

1002.85

31640.34

127.12

8424.77

8036.04

388.73

0.0

conventional

2015

Albany

NaN

2015-01-11

1.77

1182.56

39.00

305.12

0.00

838.44

0.00

0.0

organic

2015

Albany

NaN

2015-01-18

1.17

44511.28

914.14

31540.32

135.77

11921.05

11651.09

269.96

0.0

conventional

2015

Albany

NaN

Perfect example of why tail is useful sometimes...

albany_df.tail()

Unnamed: 0

AveragePrice

Total Volume

4046

4225

4770

Total Bags

Small Bags

Large Bags

XLarge Bags

type

year

region

price25ma

Date

2018-03-11

1.68

2570.52

131.67

229.56

0.00

2209.29

0.00

0.0

organic

2018

Albany

1.4224

2018-03-18

1.66

3154.45

275.89

297.96

0.00

2580.60

2577.27

3.33

0.0

organic

2018

Albany

1.4316

2018-03-18

1.35

105304.65

13234.86

61037.58

55.00

30977.21

26755.90

3721.31

500.0

conventional

2018

Albany

1.4276

2018-03-25

1.57

149396.50

16361.69

109045.03

65.45

23924.33

19273.80

4270.53

380.0

conventional

2018

Albany

1.4272

2018-03-25

1.71

2321.82

42.95

272.41

0.00

2006.46

1996.46

10.00

0.0

organic

2018

Albany

1.4368

That warning sure is annoying though isn't it. What could we do? A common idea is to silence it all of the warnings. What if we instead...

albany_df = df.copy()[df['region']=="Albany"]
albany_df.set_index('Date', inplace=True)
albany_df["price25ma"] = albany_df["AveragePrice"].rolling(25).mean()

This way, we are explicit. Pandas is happy, we're happy. You can ignore pandas warnings, but I would strongly advise against silencing them. Now you know you can do it, but I am not going to show you how :)

Another subtle thing you might have glossed over is the requirement for us to sort things how we intend before we start performing operations and calcs. Many times, you wont be visualizing columns before you make them. You may actually never visualize them. Imagine if we wrote the above code before we sorted by date, basically just assuming things were ordered by date. We'd have produced bad data.

Then imagine maybe we're doing some machine learning or other statistical analysis on that data. Well, chances are, our MA column is not only fairly useless, it's also being informed often of future data!

It's very easy to make mistakes like this. Check your code early and check it often through printing it out and visualizing it where possible!

Alright, we want more cool stuff, what else can we do?

Let's graph prices in the different regions. We hard-coded the Albany region, but hmm, we don't know all of the regions. What do we do?! If we every just wanted to get a "list" from one of our columns, we could reference just that column, like:

df['region']

0                  Albany
1                  Albany
2                  Albany
3                  Albany
4                  Albany
5                  Albany
6                  Albany
7                  Albany
8                  Albany
9                  Albany
10                 Albany
11                 Albany
12                 Albany
13                 Albany
14                 Albany
15                 Albany
16                 Albany
17                 Albany
18                 Albany
19                 Albany
20                 Albany
21                 Albany
22                 Albany
23                 Albany
24                 Albany
25                 Albany
26                 Albany
27                 Albany
28                 Albany
29                 Albany
               ...
18219             TotalUS
18220             TotalUS
18221             TotalUS
18222             TotalUS
18223             TotalUS
18224             TotalUS
18225                West
18226                West
18227                West
18228                West
18229                West
18230                West
18231                West
18232                West
18233                West
18234                West
18235                West
18236                West
18237    WestTexNewMexico
18238    WestTexNewMexico
18239    WestTexNewMexico
18240    WestTexNewMexico
18241    WestTexNewMexico
18242    WestTexNewMexico
18243    WestTexNewMexico
18244    WestTexNewMexico
18245    WestTexNewMexico
18246    WestTexNewMexico
18247    WestTexNewMexico
18248    WestTexNewMexico
Name: region, Length: 18249, dtype: object

Then convert to array with:

df['region'].values

array(['Albany', 'Albany', 'Albany', ..., 'WestTexNewMexico',
       'WestTexNewMexico', 'WestTexNewMexico'], dtype=object)

Could go to list like:

df['region'].values.tolist()

# and then maybe get the uniques with:

print(set(df['region'].values.tolist()))

{'Orlando', 'RaleighGreensboro', 'Charlotte', 'NewOrleansMobile', 'Syracuse', 'Nashville', 'DallasFtWorth', 'Chicago', 'Columbus', 'SanFrancisco', 'Southeast', 'Tampa', 'Jacksonville', 'SanDiego', 'MiamiFtLauderdale', 'Seattle', 'Philadelphia', 'California', 'SouthCentral', 'Pittsburgh', 'GrandRapids', 'Atlanta', 'Indianapolis', 'CincinnatiDayton', 'RichmondNorfolk', 'Louisville', 'Roanoke', 'LasVegas', 'Northeast', 'NorthernNewEngland', 'Detroit', 'Portland', 'Plains', 'Spokane', 'LosAngeles', 'HarrisburgScranton', 'SouthCarolina', 'TotalUS', 'West', 'Albany', 'NewYork', 'WestTexNewMexico', 'BuffaloRochester', 'Sacramento', 'BaltimoreWashington', 'Boston', 'Boise', 'Denver', 'HartfordSpringfield', 'PhoenixTucson', 'Houston', 'GreatLakes', 'Midsouth', 'StLouis'}

So, very quickly you can take your pandas data and get it out into array/list form and use your own knowledge of python. Or, you could also just use some Pandas method. Just know, if you're trying to do it, it probably has a method!

df['region'].unique()

array(['Albany', 'Atlanta', 'BaltimoreWashington', 'Boise', 'Boston',
       'BuffaloRochester', 'California', 'Charlotte', 'Chicago',
       'CincinnatiDayton', 'Columbus', 'DallasFtWorth', 'Denver',
       'Detroit', 'GrandRapids', 'GreatLakes', 'HarrisburgScranton',
       'HartfordSpringfield', 'Houston', 'Indianapolis', 'Jacksonville',
       'LasVegas', 'LosAngeles', 'Louisville', 'MiamiFtLauderdale',
       'Midsouth', 'Nashville', 'NewOrleansMobile', 'NewYork',
       'Northeast', 'NorthernNewEngland', 'Orlando', 'Philadelphia',
       'PhoenixTucson', 'Pittsburgh', 'Plains', 'Portland',
       'RaleighGreensboro', 'RichmondNorfolk', 'Roanoke', 'Sacramento',
       'SanDiego', 'SanFrancisco', 'Seattle', 'SouthCarolina',
       'SouthCentral', 'Southeast', 'Spokane', 'StLouis', 'Syracuse',
       'Tampa', 'TotalUS', 'West', 'WestTexNewMexico'], dtype=object)

That was quick and painless!

graph_df = pd.DataFrame()

for region in df['region'].unique()[:16]:
    print(region)
    region_df = df.copy()[df['region']==region]
    region_df.set_index('Date', inplace=True)
    region_df.sort_index(inplace=True)
    region_df[f"{region}_price25ma"] = region_df["AveragePrice"].rolling(25).mean()

    if graph_df.empty:
        graph_df = region_df[[f"{region}_price25ma"]]  # note the double square brackets!
    else:
        graph_df = graph_df.join(region_df[f"{region}_price25ma"])

Albany
Atlanta
BaltimoreWashington
Boise
Boston
BuffaloRochester
California
Charlotte
Chicago
CincinnatiDayton
Columbus
DallasFtWorth
Denver
Detroit
GrandRapids
GreatLakes

I set the limit to 16 just to show we're getting bogged down. This one really tripped me up. I couldn't quite figure out what was going on. Things were taking exponentially longer and longer, then memory was getting exhausted. That's not what I know and love with Pandas, so what gives? Upon some digging, we find that hmm, dates are still getting duplicated. For example:

graph_df.tail()

Albany_price25ma

Atlanta_price25ma

BaltimoreWashington_price25ma

Boise_price25ma

Boston_price25ma

BuffaloRochester_price25ma

California_price25ma

Charlotte_price25ma

Chicago_price25ma

CincinnatiDayton_price25ma

Columbus_price25ma

DallasFtWorth_price25ma

Denver_price25ma

Detroit_price25ma

GrandRapids_price25ma

GreatLakes_price25ma

Date

2018-03-25

1.4368

1.2884

1.3844

1.5016

1.588

1.2232

1.4232

1.4916

1.5708

1.2792

1.1704

1.118

1.2888

1.1492

1.3264

1.2788

2018-03-25

1.4368

1.2884

1.3844

1.5016

1.588

1.2232

1.4232

1.4916

1.5708

1.2792

1.1704

1.118

1.2888

1.1728

1.3164

1.2568

2018-03-25

1.4368

1.2884

1.3844

1.5016

1.588

1.2232

1.4232

1.4916

1.5708

1.2792

1.1704

1.118

1.2888

1.1728

1.3164

1.2788

2018-03-25

1.4368

1.2884

1.3844

1.5016

1.588

1.2232

1.4232

1.4916

1.5708

1.2792

1.1704

1.118

1.2888

1.1728

1.3264

1.2568

2018-03-25

1.4368

1.2884

1.3844

1.5016

1.588

1.2232

1.4232

1.4916

1.5708

1.2792

1.1704

1.118

1.2888

1.1728

1.3264

1.2788

Each row should be a separate date, but it's not. Through some debugging, we can discover what's happening, which actually informs us to why our previous data looked so ugly too.

Our avocados have multiple prices: Organic and Conventional! So, let's pick one. I'll go with organic. So we'll just start over pretty much.

import pandas as pd

df = pd.read_csv("datasets/avocado.csv")
df = df.copy()[df['type']=='organic']

df["Date"] = pd.to_datetime(df["Date"])

df.sort_values(by="Date", ascending=True, inplace=True)
df.head()

Unnamed: 0

Date

AveragePrice

Total Volume

4046

4225

4770

Total Bags

Small Bags

Large Bags

XLarge Bags

type

year

region

9489

2015-01-04

1.24

142349.77

107490.73

25711.96

2.93

9144.15

0.00

0.0

organic

2015

California

10269

2015-01-04

1.50

6329.83

3730.80

2141.91

0.00

457.12

426.67

30.45

0.0

organic

2015

LasVegas

10893

2015-01-04

1.12

17296.85

14569.66

1868.59

0.00

858.60

830.00

28.60

0.0

organic

2015

PhoenixTucson

9437

2015-01-04

1.73

379.82

0.00

59.82

0.00

320.00

0.00

0.0

organic

2015

BuffaloRochester

11621

2015-01-04

1.30

5782.70

723.29

4221.15

0.00

838.26

223.33

614.93

0.0

organic

2015

Spokane

Now, let's just copy and paste the code from above, minus the print:

graph_df = pd.DataFrame()

for region in df['region'].unique():
    region_df = df.copy()[df['region']==region]
    region_df.set_index('Date', inplace=True)
    region_df.sort_index(inplace=True)
    region_df[f"{region}_price25ma"] = region_df["AveragePrice"].rolling(25).mean()

    if graph_df.empty:
        graph_df = region_df[[f"{region}_price25ma"]]  # note the double square brackets! (so df rather than series)
    else:
        graph_df = graph_df.join(region_df[f"{region}_price25ma"])

graph_df.tail()

California_price25ma

LasVegas_price25ma

PhoenixTucson_price25ma

BuffaloRochester_price25ma

Spokane_price25ma

LosAngeles_price25ma

Philadelphia_price25ma

Boston_price25ma

StLouis_price25ma

Louisville_price25ma

...

Houston_price25ma

Chicago_price25ma

Plains_price25ma

Indianapolis_price25ma

SouthCentral_price25ma

Columbus_price25ma

Albany_price25ma

Detroit_price25ma

NewOrleansMobile_price25ma

NewYork_price25ma

Date

2018-02-25

1.9128

1.9120

1.7468

1.2912

2.1544

1.9024

1.6324

1.7636

1.9240

1.7044

...

1.6132

1.8160

1.8116

1.4928

1.5728

1.6064

1.5112

1.4980

1.5384

1.9308

2018-03-04

1.8876

1.8748

1.7404

1.2744

2.1040

1.8656

1.6260

1.7708

1.8868

1.6816

...

1.5960

1.8024

1.7900

1.4744

1.5592

1.5804

1.4992

1.4692

1.5288

1.9156

2018-03-11

1.8636

1.8440

1.7324

1.2652

2.0552

1.8284

1.6300

1.7824

1.8468

1.6508

...

1.5696

1.7836

1.7672

1.4540

1.5400

1.5496

1.5044

1.4444

1.5076

1.9092

2018-03-18

1.8516

1.8204

1.7216

1.2560

2.0012

1.8160

1.6304

1.7932

1.8192

1.6176

...

1.5360

1.7732

1.7452

1.4320

1.5204

1.5088

1.5140

1.4092

1.4860

1.8948

2018-03-25

1.8364

1.7968

1.7104

1.2416

1.9496

1.8016

1.6256

1.7984

1.7976

1.5844

...

1.5128

1.7672

1.7232

1.4160

1.5072

1.4848

1.5188

1.3964

1.4616

1.8876

5 rows A-- 54 columns

Now it's quick! Awesome!

Let's graph!

graph_df.plot(figsize=(8,5), legend=False)

<matplotlib.axes._subplots.AxesSubplot at 0x124046fd0>

Lots more we could poke around with here, but, in the next tutorial, we'll be visiting a new dataset with new challenges.

PreviousIntroduction - Data Analysis with Python 3 and Pandas NextGroupby - Data Analysis with Python 3 and Pandas

Last updated 5 years ago

Was this helpful?