{"id":9462,"date":"2016-08-15T11:34:44","date_gmt":"2016-08-15T11:34:44","guid":{"rendered":"http:\/\/www.highcharts.com\/blog\/?p=9462"},"modified":"2026-01-12T09:06:44","modified_gmt":"2026-01-12T09:06:44","slug":"226-get-your-data-ready-for-charts-with-python","status":"publish","type":"post","link":"https:\/\/www.highcharts.com\/blog\/integration\/226-get-your-data-ready-for-charts-with-python\/","title":{"rendered":"Get your data ready for charts with Python"},"content":{"rendered":"

PYTHON DATES AND TIMES<\/h2>\n

Python is my data analysis language of choice. After using ParseHub<\/a> to collect huge databases of JSON from online sources, I then use Python\u2019s great libraries like pandas<\/a> and matplotlib<\/a> to get the data ready to visualize. In this tutorial, I\u2019ll give you a few examples of how you can use Python\u2019s datetime<\/a> library to convert extracted dates and times into a format that is understood by your computer. With this tool you will be able to make visuals that change over the past, present, and future.<\/p>\n

This example will use data from the table found on this Wikipedia page<\/a>, which includes the date that each one of the current members joined the United Nations. I will show you how to get it ready for visualizing by converting all of the strings in the date columns into datetimes.<\/p>\n

I scraped the table<\/a> using ParseHub, which allows me to download the extracted data as JSON.<\/p>\n

The next step is to import the libraries you will need into Jupyter notebook<\/a>.<\/span><\/p>\n

import<\/span> math<\/span>\r\nimport<\/span> pandas as<\/span> pd<\/span>\r\nimport<\/span> pylab as<\/span> pl<\/span>\r\nimport<\/span> numpy as<\/span> np<\/span>\r\nimport<\/span> json<\/span>\r\nimport<\/span> datetime<\/span><\/code><\/pre>\n
Python\u2019s pandas library has a function read_json to import JSON into a pandas data structure. I used it to first import the data oriented as one column:<\/p>\n
data<\/span> = pd.read_json(\u2018UN_members<\/span>.json'<\/span>, orient<\/span>='columns'<\/span>)<\/span><\/span><\/code><\/pre>\n
Next, each cell will be read as an index:<\/p>\n
data<\/span> = pd.read_json(data<\/span>['states'<\/span>].to_json<\/span>()<\/span>, orient='index')<\/span><\/code><\/pre>\n
For this example, I would like to calculate how long each country has been a member of the UN, so I add a blank column called \u201ctime\u201d to my DataFrame.<\/p>\n
time<\/span> = pd.Series<\/span>(None<\/span>, index = data<\/span>.index, name = 'time')<\/span> data<\/span> = data<\/span>.join(time<\/span>)<\/span><\/span><\/code><\/pre>\n
\"\"<\/p>\n
You can\u2019t calculate anything with the dates found in the \u201cadmission\u201d column just yet, because they are still being stored as strings. Luckily, pandas has a function to_datetime that will be able to convert them to a computer readable datetime variable, as long as you tell it what the format of each date is.<\/p>\n
I\u2019ll first find today\u2019s date with the datetime today<\/strong> function. Then will use a for loop, which will convert each date string into datetimes and calculate the number of days it has been since that date, one index at a time. Indexing a pandas DataFrame can be done in many different ways<\/a>, but for this loop I used: dataframe.loc[index<\/em>, column name<\/em>]<\/p>\n
for<\/span> i in data.index<\/span> :\r\n\r\n    data.loc[i, 'admission'<\/span>] = pd.to_datetime(data.loc[i, 'admission'<\/span>], format<\/span>=\"%d<\/span> %B<\/span> %Y<\/span>\"<\/span>)\r\n    data.loc[i, 'time'<\/span>] = date - data.loc[i, 'admission'<\/span>]<\/code><\/pre>\n
I then sorted my data frame by length of membership, found in the \u201ctime\u201d column, and alphabetical order of the country names.<\/p>\n
data<\/span> = data<\/span>.sort_values(['time'<\/span>, 'name'<\/span>])<\/span><\/span><\/code><\/pre>\n
\"\"<\/p>\n
One way to associate a country to its location on a map using Highmaps is to use its ISO country code. I found a set of data<\/a> from one of the Highmaps examples that had the name of every country along with their associated country code.<\/p>\n
I imported this data as a list, converted it to json using python\u2019s dumps function and then to a pandas DataFrame using the read_json function.<\/p>\n
# converts lists into json<\/span>\r\ncountry_codes = json.dumps(country_codes)\r\n# converts json into dataframe<\/span>\r\ncountry_codes = pd.read_json(country_codes)<\/code><\/pre>\n
In the UN DataFrame, I had to change some of the country names, because the two sources didn\u2019t always use the exact same names. There could have been an elegant way to do this with regex, but I simply hard coded the changes.<\/p>\n
data.loc[data['name'<\/span>] == 'East Timor'<\/span>, 'name'<\/span>] = 'Timor-Leste'<\/span>\r\ndata.loc[data['name'<\/span>] == 'Macedonia'<\/span>, 'name'<\/span>] = 'Macedonia, FYR'<\/span>\r\ndata.loc[data['name'<\/span>] == 'Slovakia'<\/span>, 'name'<\/span>] = 'Slovak Republic'<\/span>\r\n# etc...<\/span><\/code><\/pre>\n
After that I was able to join the two DataFrames, giving me one with the time data as well as the country codes.<\/p>\n
joined = pd.merge<\/span>(data, country_codes, how='inner'<\/span>, on<\/span>='name'<\/span>)<\/span><\/code><\/pre>\n
\"\"
\nAll I need for the visualisation is country code and times, so I selected those columns and then changed the column names. I changed their names to \u201chc-key\u201d and \u201cvalue\u201d.<\/p>\n
sh\r\nnew_data = pd.concat([joined['code'].str.lower(), joined['time']], axis=1)\r\nnew_data.columns = ['hc-key', 'value']\r\n<\/pre>\n
\"\"
\nUsing the datetime days function, I was able to turn each datetime object into an integer number of days.<\/p>\n
for<\/span> i in<\/span> new_data.index<\/span>:   \r\n    new_data.loc[i, 'value'<\/span>] = new_data.loc[i, 'value'<\/span>].days<\/code><\/pre>\n
I exported the DataFrame as JSON into my python directory to save it.<\/p>\n
export<\/span> = new_data.to_json('UN_data.json'<\/span>, orient='records'<\/span>)\r\n<\/code><\/pre>\n
\"\"<\/p>\n
From there it was ready to bring into a Highmaps visual. I copied and pasted the saved JSON, but it could have just as easily been imported from a URL.<\/p>\n
I gave my map navigation buttons bottom left and a color center bottom center of the chart.<\/p>\n
It also has a hover state, so that the countries change color when hovered over, as well as a tool tip that explicitly states the number of days. You can see the full code in the JavaScript tab below and see how easily it comes together.
\n
Now my data is ready to be exported, analyzed and visualized! I decided to visualize this particular data as a highly interactive map using HighChart\u2019s Highmaps<\/a>.<\/p>\n
In this case, the format is the day<\/strong>, followed by a space, then the name of the month<\/strong>, then another space, then theyear<\/strong>. Using this reference<\/a>, I know that the format is written: \u201c%d %B %Y\u201d<\/p>\n