Engineers can collect data in a number of formats. One is simply written. If an engineer has written data, this can be entered maually into a numpy array or pandas dataframe for future analysis. But other data is electronic or digital and saved in a file. Digital data can come from test instruments, robotics, statistical process control and many other areas.
In this post, we will read three different file types into pandas and make a simple plot of each. The three file types are:
.csv (Comma Seperated Values)
.xlsx (Microsoft Excel file)
.json (JavaScript Object Notation)
Each file type requires a different pandas function.
Let's start out with a .csv file. .csv stands for comma seperated values. A .csv file is a type of text file that organizes data into columns and rows. .csv files have commas, spaces, tabs or other characters to seperate columns, and new lines to seperate rows. To read in a .csv file with pandas, we first need to import pandas. We'll also import matplotlib, numpy and run the matplotlib inline command to show the plots if you are using a Jupyter notebook.
In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
In [2]:
df = pd.read_csv('tensile_test_data.csv', )
In [3]:
df.head()
Out[3]:
This gives us some strange column names. '237.7605198' is one of the values in the data set, not the column name. We need to specify header=None as an optional arguemnt, to ensure that pandas doesn't use the first row of our data set as column headers.
In [4]:
df = pd.read_csv('tensile_test_data.csv', header=None)
df.head()
Out[4]:
Now for a quick plot. The x-values will be from the 3rd column (which has a header '2') and our y-values will come from the second column (which has a header '1'). Remember that Python starts counting at zero, not 1.
In [5]:
df.plot(x=2, y=1)
Out[5]:
Next let's read in an Excel file. Excel files have an .xlsx or .xls file extension. We use the pandas function pd.read_excel().
When I tried to run the pd.read_excel() function the first time, I was returned an error:
"ImportError: Install xlrd >= 0.9.0 for Excel support"
To solve this, I went to the Anaconda Prompt and typed:
conda install xlrdOnce the xlrd module was installed, the pd.read_excel() function worked just fine.
In [6]:
df = pd.read_excel('weather_data.xlsx')
df.head()
Out[6]:
Again, we have the problem with the headers. Pandas is using the first row of our excel sheet as the column headers in our dataframe. Including header=None will add sequential column numbers and make the first row of the datafram from the first row of the excel sheet.
In [7]:
df = pd.read_excel('weather_data.xlsx', header=None)
df.head()
Out[7]:
In [8]:
df2 = df.transpose()
df2.head()
Out[8]:
In [9]:
df2.replace(-99999, np.nan, inplace=True)
df2.tail()
#df2.plot()
Out[9]:
In [10]:
#df.sum()
df2.sum().plot()
Out[10]:
In [11]:
df = pd.read_json('json_data.json')
In [12]:
df.head()
Out[12]:
In [ ]:
In [ ]: