[PYTHON] Export pandas dataframe to excel

Use pandas to_excel to export the data frame to excel.

• [pandas.DataFrame.to_excel — pandas 0.15.1 documentation](http://pandas.pydata.org/pandas- docs/version/0.15.1/generated/pandas.DataFrame.to_excel.html)

First, make sure you can import the Python modules you need.

import pandas as pd
import matplotlib.pyplot as plt
from pandas.io import wb
import xlsxwriter

Data reading

Use the World Bank API to get US and Japanese population and GDP. The indicator string is obtained from the World Bank API.

df_gdp = wb.download(indicator='NY.GDP.PCAP.KD', country=['US', 'JP'], start=1960, end=2013)
df_population = wb.download(indicator='SP.POP.TOTL', country=['US', 'JP'], start=1960, end=2013)

Data confirmation

df_gdp.head(3)

df_gdp.dtypes

NY.GDP.PCAP.KD    float64
dtype: object
df_gdp.index

MultiIndex(levels=[['Japan', 'United States'], ['1960', '1961', '1962', '1963', '1964', '1965', '1966', '1967', '1968', '1969', '1970', '1971', '1972', '1973', '1974', '1975', '1976', '1977', '1978', '1979', '1980', '1981', '1982', '1983', '1984', '1985', '1986', '1987', '1988', '1989', '1990', '1991', '1992', '1993', '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013']],
           labels=[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...], [53, 52, 51, 50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 53, 52, 51, 50, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, ...]],
           names=['country', 'year'])

df_gdp.describe()

The GDP data frame is:

• Multi-index of "country" and "year"
• There are 108 elements
• The minimum value is 7079.439251 and the maximum value is 45863.019564.
• Data is in descending order from latest

The population data frame has a similar structure.

Format data

Since it is difficult to use as it is, format the data.

df_gdp.unstack(level=0).head(3)

df_gdp.unstack(level=0).describe()

df_gdp.unstack(level=0).plot(figsize=(16, 4), colormap='seismic')

Population data is converted in the same way.

df_population.unstack(level=0).head(3)

ax = df_population.unstack(level=0).plot(figsize=(16, 4), colormap='seismic')

When calculating the five-year moving average, it is a little clear that the population of the United States is increasing and that of Japan is flat.

ax = pd.stats.moments.rolling_mean(df_population.unstack(level=0), 5).plot(figsize=(16, 4), colormap='seismic')

ax = pd.stats.moments.rolling_std(df_population.unstack(level=0)['SP.POP.TOTL'], 5).plot(figsize=(16, 4), colormap='seismic')

Data combination

Combines two data frames.

pd.concat([df_gdp, df_population], axis=1).unstack(level=0).head(3)

df = pd.concat([df_gdp, df_population], axis=1).unstack(level=0)
df.describe()

Let's draw a graph with GDP on the left axis and population on the right axis.

ax = df.plot(figsize=(16, 6), colormap='seismic',
                 secondary_y=[('SP.POP.TOTL', 'Japan'), ('SP.POP.TOTL', 'United States')])
    ax.set_ylabel('GDP')
    _ = ax.right_ax.set_ylabel('Population')

It can now be handled in a single data frame.

Calculating the variance in 5-year increments, it can be said that Japan's population has been sluggish since around 1980 and GDP has been flat since around 1995. In the case of the United States, there was a wave of population growth around 1990, and the increase in GDP peaked around 2000, and it can be said that there are regular waves.

ax = pd.stats.moments.rolling_var(df, 5).plot(subplots=True, layout=(2, 2), figsize=(16, 6))

Data output

Output to Excel. Call a method in the data frame, but xlsxwriter Excel export module such as must be installed.

df.to_excel('/data/sample.xlsx', sheet_name='Japan_US')
%ls /data

sample.xlsx

Please open the completed data in Excel. You can also use LibreOffice.

When exporting multiple data frames to separate sheets, the argument is a writer object (ʻExcelWriter`) instead of a filename. Is specified. There is an example in the official documentation, but look for more information on Stack Overflow.

• [pandas.DataFrame.to_excel — pandas 0.15.1 documentation](http://pandas.pydata.org/pandas- docs/stable/generated/pandas.DataFrame.to_excel.html)
• [creating multiple excel worksheets using data in a pandas dataframe - Stack Overflow](http://stackoverflow.com/questions/21981820/creating-multiple-excel- worksheets-using-data-in-a-pandas-dataframe)

Operating environment

• version_information * Extensions have been enabled. The operating environment of this notebook is as follows.

  %version_information numpy, pandas, matplotlib, xlsxwriter

• This document is a fine-tuned version of IPython Notebook converted to Markdown.

$ ipython nbconvert --to markdown /data/worldbank_to_excel.ipynb