GitHub Repository: restrepo/ComputationalMethods
Path: blob/master/material/Pandas.ipynb
⁹³⁴ views

Kernel: Python 3 (ipykernel)

Pandas

From http://pandas.pydata.org/pandas-docs/stable/

pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language. It is already well on its way toward this goal.

Standard way to load the module

In [5]:

import pandas as pd

Introduction

We already saw how NumPy arrays can improve the analysis of numerical data. For heterogeneous data the recommended tool are Pandas dataframes.

Heterogeneous and nested data can be stored as list of dictionaries. For example, for people with names, birth date, sex, and a job list with start and end date, we can have

In [3]:

# Create a dictionary for each person's data
person1 = {"Name": "John Doe", "Birth Date": "01-01-1980", "Sex": "Male", 
           "Job": [{"Job Title": "Software Engineer", "Start Date": "01-01-2000", "End Date": "01-01-2005"}, 
                   {"Job Title": "Data Scientist", "Start Date": "01-01-2005", "End Date": None}]}
person2 = {"Name": "Jane Doe", "Birth Date": "01-01-1985", "Sex": "Female", 
           "Job": [{"Job Title": "Product Manager", "Start Date": "01-01-2010", "End Date": "01-01-2015"}, 
                   {"Job Title": "Project Manager", "Start Date": "01-01-2015", "End Date": "01-01-2020"}]}
person3 = {"Name": "Jim Smith", "Birth Date": "01-01-1990", "Sex": "Male", 
           "Job": [{"Job Title": "Data Analyst", "Start Date": "01-01-2010", "End Date": "01-01-2015"}, 
                   {"Job Title": "Business Analyst", "Start Date": "01-01-2015", "End Date": "01-01-2020"}]}
person4 = {"Name": "Sara Johnson", "Birth Date": "01-01-1995", "Sex": "Female", 
           "Job": [{"Job Title": "Product Designer", "Start Date": "01-01-2015", "End Date": "01-01-2020"}, 
                   {"Job Title": "UX Designer", "Start Date": "01-01-2020", "End Date": None}]}

# Create a list of dictionaries
people = [person1, person2, person3, person4]

We can create a DataFrame from the list of dictionaries

In [6]:

df = pd.DataFrame(people)
df

Out[6]:

As with NumPy, we can create masks in order to filter out specific rows of the dataframe. For example, to filter out the female persons by using the syntax:

In [7]:

df[df["Sex"] == "Female"]

Out[7]:

To filter out the last job of each person by using the following code (.get is a safer way to obtain the value of the key of a dictionary)

In [12]:

df['Last job']=df["Job"].apply(lambda L: L[-1].get('Job Title'))
df[['Name','Birth Date','Sex','Last job']]

Out[12]:

Basic structure: DataFrame

An flat spreadsheet can be seen in terms of the types of variables of Python just as dictionary of lists, where each column of the spreadsheet is a pair key-list of the dictionary

	A	B
1	even	odd
2	0	1
3	2	3
4	4	5
5	6	7
6	8	9

In [3]:

numbers={"even": [0,2,4,6,8],   #  First  key-list
         "odd" : [1,3,5,7,9] }  #  Second key-list

Data structures

Pandas has two new data structures:

DataFrame which are similar to numpy arrays but with some assigned key. For example, for the previous case

import numpy as np
np.array([[0,1],
          [2,3],
          [4,5],
          [6,7],
          [8,9] 
         ])

Series which are enriched to dictionaries, as the ones defined for the rows of the previous example: {'even':0,'odd':1}.

The rows in a two-dimensional DataFrame corresponds to Series with similar keys, while the columns are also Series with the indices as keys.

An example of a DataFrame is a spreadsheet, as the one before.

`DataFrame`

Pandas can convert a dictionary of lists, like the numbers dictionary before, into a DataFrame, which is just an spreadsheet but interpreted at the programming level:

In [4]:

numbers

Out[4]:

{'even': [0, 2, 4, 6, 8], 'odd': [1, 3, 5, 7, 9]}

In [5]:

import pandas as pd
df=pd.DataFrame(numbers)
df

Out[5]:

In [7]:

import matplotlib.pyplot as plt

In [10]:

plt.plot(df['even'],df['odd'])

Out[10]:

[<matplotlib.lines.Line2D at 0x7f7d7a779880>]

See below for other possibilities of creating Pandas DataFrames from lists and dictionaries

The main advantage of the DataFrame,df, upon a spreadsheet, is that it can be managed just at the programming level without any graphical interface.

We can check the shape of the DataFrame

In [11]:

df.shape

Out[11]:

(5, 2)

Export DataFrame to other formats

To export to excel:

In [12]:

df.to_excel('example.xlsx',index=False)

In [42]:

newdf=pd.read_excel('example.xlsx')
newdf

Out[42]:

In [44]:

newdf['fractions']=[0.5,2.5,4.5,6.5,8.5]
newdf

Out[44]:

In [45]:

newdf['next fractions']=1.5
newdf

Out[45]:

In [47]:

newdf.loc[3,'next to next fractions']=1.7
newdf

Out[47]:

Activity: Open the resulting spreadsheet in Google Drive, publish it and open from the resulting link with Pandas in the next cell

In [ ]:

df=pd.read_excel('PASTE THE PUBLISHED LINK HERE')
df

In [14]:

df=pd.read_excel('https://docs.google.com/spreadsheets/d/e/2PACX-1vQ1HFwErJcHkkOCT4Je-yuLSRe2L_GKWcCGVooc6rbOvTLxJhqglTZh31I_eB_dcw/pub?output=xlsx')
df

Out[14]:

`Series`

Each column of the DataFrame is now an augmented dictionary called Series, with the indices as the keys of the Series

A Pandas Series object can be just initialized from a Python dictionary:

In [20]:

df['even']

Out[20]:

  0
  2
  4
  6
  8
Name: even, dtype: int64

In [15]:

type( df['even'] )

Out[15]:

pandas.core.series.Series

In [21]:

df.even

Out[21]:

  0
  2
  4
  6
  8
Name: even, dtype: int64

The keys are the index of the DataFrame

In [22]:

#df['even']
df.even[4]

Out[22]:

8

Each row is also a series

In [24]:

df.loc[0]

Out[24]:

even    0
odd     1
Name: 0, dtype: int64

with keys: 'even' and 'odd'

or as a filter

In [26]:

df.loc[[4]]

Out[26]:

In [27]:

df.loc[0]['even']

Out[27]:

0

or attributes even and odd

In [28]:

df.loc[0].odd

Out[28]:

1

One specific cell value can be reached with the index and the key:

In [ ]:

df.iloc[2,1]

In [19]:

df.loc[2,'odd']

Out[19]:

5

In [29]:

df.at[2,'even']

Out[29]:

4

A Pandas Series object can be just initialized from a Python dictionary:

In [32]:

s=pd.Series({'Name':'Juan Valdez','Nacionality':'Colombia','Age':23})
s

Out[32]:

Name           Juan Valdez
Nacionality       Colombia
Age                     23
dtype: object

In [33]:

s['Name']

Out[33]:

'Juan Valdez'

but also as containers of name spaces!

In [34]:

s.Name

Out[34]:

'Juan Valdez'

The power of Pandas rely in that their main data structures: DataFrames and Series, are enriched with many useful methods and attributes.

Official definition of Pandas

Pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language. It is already well on its way toward this goal.

"relational": the list of data is identified with some unique index (like a SQL table)
"labeled": the list is identified with a key, like the previous odd or even keys.

For example. A double bracket [[...]], can be used to filter data.

A row in a two-dimensional DataFrame corresponds to Series with the same keys of the DataFrame, but with single values instead of a list

In [30]:

df.loc[[0]]

Out[30]:

To filter a column:

In [ ]:

df[['odd']]

`DataFrame` initialization

Initialization from an existing spreadsheet.

This can be locally in your computer o from some downloadable link

In [49]:

df=pd.read_excel('http://bit.ly/spreadsheet_xlsx')
df

Out[49]:

To make a downloadable link for any spread sheet in Google Drive, follow the sequence:

File → Share → Publish to the web...→ Entire Document → Web page → Microsoft excel (xlsx)

In [36]:

df.loc[0,'Edad']=32
#df.at[0,'Edad']=32
df

Out[36]:

After some modification

it can be saved again as an excel file with the option to not create a column of indices: index=False

Initialization from lists and dictionaries

Inizialization from Series

We start with an empty DataFrame:

Creating Pandas DataFrame from list and dictionaries offers many alternatives

creating dataframes

Column oriented way

In addition to the dictionary of lists already illustrated at the beginning that in this case corresponds to:

In [25]:

pd.DataFrame({'Nombre'   : ['Juan Valdez','Álvaro Uribe Vélez'],
              'Edad'     : [32,            69                 ],
              'Compañia' : ['Café de Colombia','Senado de la República']})

Out[25]:

We can obtain the DataFrame from list of items

In [ ]:

pd.DataFrame.from_items([ [ 'Nombre'  , ['Juan Valdez','Álvaro Uribe Vélez']],
                          [ 'Edad'    , [  32,            65               ]],
                          [ 'Compañia', ['Café de Colombia','Senado de la República']] ])

We can obtain the DataFrame from dictionary

In [37]:

pd.DataFrame( [{'Nombre':'Juan Valdez',        'Edad': 32   ,'Compañia':'Café de Colombia'      },
              {'Nombre':'Álvaro Uribe Vélez', 'Edad': 65   ,'Compañia':'Senado de la República'}]
            )

Out[37]:

Special DataFrames

Empty DataFrame

In [38]:

df=pd.DataFrame()
df

Out[38]:

In [39]:

df.empty

Out[39]:

True

Single row DataFrame from dictionary

In [4]:

d={
    'first key' :'first value',
    'second key':'second value'

  }
pd.DataFrame([d])

Out[4]:

Initialization from sequential rows as Series

We start with an empty DataFrame:

In [11]:

import pandas as pd
df=pd.DataFrame()
df.empty

Out[11]:

True

We can append a dictionary (or Series) as a row of the DataFrame, provided that we always use the option: ignore_index=True

In [12]:

d={'Name':'Juan Valdez','Nacionality':'Colombia','Age':23}
df=pd.concat([df,pd.DataFrame([d])])
df

Out[12]:

To add a second file we build another dict

In [57]:

d={}
for k in ['Name','Nacionality','Age','Company']:
    var=input('{}:\n'.format(k))
    d[k]=var

Out[57]:

{"name":"stdin","output_type":"stream","text":"Name:\n Diego Restrepo\nNacionality:\n Colombia\nAge:\n 51\nCompany:\n UdeA\n"}

In [58]:

Out[58]:

{'Name': 'Diego Restrepo',
 'Nacionality': 'Colombia',
 'Age': '51',
 'Company': 'UdeA'}

In [ ]:

df=pd.concat([df,pd.DataFrame([d])])

To concatenate a list of dataframes side by side use the option axis='columns'

Exercises

Display the resulting Series in the screen:

In [62]:

df['Name']

Out[62]:

0       Juan Valdez
1    Diego Restrepo
Name: Name, dtype: object

Activity: Append a new row to the previous DataFrame and visualize it:

In [14]:

Out[14]:

Fill NaN with empty strings

In [63]:

df=df.fillna('')

In [64]:

df

Out[64]:

Save Pandas DataFrame as an Excel file

In [65]:

df.to_excel('prof.xlsx',index=False)

Load pandas DataFrame from the saved file in Excel

In [66]:

pd.read_excel('prof.xlsx')

Out[66]:

Common operations upon `DataFrames`

See https://github.com/restrepo/PythonTipsAndTricks

To fill a specific cell

In [67]:

df.at[0,'Company']='Federación de Caferos'

In [68]:

df

Out[68]:

Filters (masking)

The main application of labeled data for data analysis is the possibility to make filers, or cuts, to obtain specific reduced datasets to further analysis

In [109]:

import pandas as pd

In [110]:

numbers={"even": [0,2,4,-6,8],   #  First  key-list
         "odd" : [1,3,-5,7,9] }  #  Second key-list

In [111]:

df=pd.DataFrame(numbers)

In [112]:

df

Out[112]:

A mask is a list of True/False values

In [113]:

df.even.abs()>4

Out[113]:

  False
  False
  False
   True
   True
Name: even, dtype: bool

In [114]:

df[df.even.abs()>4]

Out[114]:

and → &

In [115]:

df[(df.even>0) & (df.odd<0)]

Out[115]:

negation → ~

In [51]:

df[~((df.even>0) & (df.odd<0)) ]

Out[51]:

or → |

In [116]:

df[(df.even<0) | (df.odd<0)]

Out[116]:

The `apply` method

The advantage of the spreadsheet paradigm is that the columns can be transformed with functions. All the typical functions avalaible for a spreadsheet are already implemented like the method .abs() used before, or the method: .sum()

In [118]:

df.even.sum()

Out[118]:

8

Activity: Explore the avalaible methods by using the completion system of the notebook after the last semicolon of df.even.

In [119]:

kk=df['even']

In [ ]:

kk.

df['even'].ipynb_checkpoints/

Column-level `apply`

We just select the column and apply the direct or implicit function:

Pre-defined function

In [121]:

df.even.apply(abs)

Out[121]:

  0
  2
  4
  6
  8
Name: even, dtype: int64

Implicit function

In [122]:

df.even.apply(lambda n:isinstance(n,int))

Out[122]:

  True
  True
  True
  True
  True
Name: even, dtype: bool

In [123]:

df.even.apply(lambda n: n**2)

Out[123]:

   0
   4
  16
  36
  64
Name: even, dtype: int64

Row-level apply

The foll row is passed as dictionary to the explicit or implicit function when apply is used for the full DataFrame and the option axis=1 is used at the end

In [124]:

df

Out[124]:

In [125]:

df['even']+df['odd']**2

Out[125]:

   1
  11
  29
  43
  89
dtype: int64

In [126]:

df.apply(lambda row: row['even']+row['odd']**2,axis='columns')

Out[126]:

   1
  11
  29
  43
  89
dtype: int64

In [66]:

df.apply(lambda row: row.get('even')+row.get('odd')**2,axis='columns')

Out[66]:

   1
  11
  29
  43
  89
dtype: int64

Chain tools for data analysis

There are several chain tools for data analyis like the

Spreadsheet based one, like Excel
Relational databases with the use of more advanced SQL tabular data with some data base software like MySQL
Non-relational databases (RAM) with Pandas, R, Paw,... ( max ~ RAM/8)
Non-relational databases (Disk): Dask, ROOT, MongoDB,...

Here we illustrate an example of use fo a non-relational database with Pandas

Relational databases

In [127]:

import pandas as pd

In [130]:

personas=pd.read_csv('https://raw.githubusercontent.com/restrepo/ComputationalMethods/master/data/personas.csv')
personas

Out[130]:

In [2]:

trabajos=pd.read_csv('https://raw.githubusercontent.com/restrepo/ComputationalMethods/master/data/trabajos.csv',
                     na_filter=False)
trabajos

Out[2]:

Example

Obtain the current work of Álvaro Uribe Vélez

In [5]:

trabajos

Out[5]:

It is convenient to normalize the columns with strings before to tray to search inside them with a DataFrame method like .

In [132]:

import unidecode

In [153]:

unidecode.unidecode('Álvaro de Uribe').lower()

Out[153]:

'alvaro de uribe'

In [133]:

cc=personas[personas['Nombre'].str.lower().apply(
    unidecode.unidecode).str.contains('alvaro uribe velez')].iloc[0].get('id')

In [134]:

trabajos[trabajos.get('id')==cc]['Cargo'].to_list()

Out[134]:

['Senador', 'Influencer']

Non-relational databases

Nested lists of dictionaries with a defined data scheme

In [135]:

personas['Fecha de Nacimiento']=pd.to_datetime( personas['Fecha de Nacimiento'] )

In [136]:

personas

Out[136]:

Extract-Transform-Load: ETL

In [137]:

from dateutil.relativedelta import relativedelta

In [138]:

personas['Edad']=personas['Fecha de Nacimiento'].apply(lambda t: 
                        relativedelta( pd.to_datetime('now'), t).years )

Out[138]:

/home/usuario/anaconda3/lib/python3.9/site-packages/pandas/core/arrays/datetimes.py:2199: FutureWarning: The parsing of 'now' in pd.to_datetime without `utc=True` is deprecated. In a future version, this will match Timestamp('now') and Timestamp.now()
  result, tz_parsed = tslib.array_to_datetime(

In [139]:

trabajos[trabajos['id']==666].to_dict(orient='records')

Out[139]:

[{'id': 666,
  'Inicio': 2013,
  'Fin': '2020',
  'Cargo': 'Senador',
  'Compañía': 'Senado de la República de Colombia'},
 {'id': 666,
  'Inicio': 2020,
  'Fin': '',
  'Cargo': 'Influencer',
  'Compañía': 'Twitter'}]

In [140]:

personas

Out[140]:

In [141]:

personas['id']

Out[141]:

0    888
1    666
Name: id, dtype: int64

In [142]:

personas['Trabajos']=personas['id'].apply(lambda i:  trabajos[trabajos['id']==i
                                                             ][['Inicio','Fin','Cargo','Compañía']
                                                              ].to_dict(orient='records') )

In [143]:

personas

Out[143]:

In [146]:

personajes=personas[['Nombre','Edad','Trabajos']]

In [147]:

personajes

Out[147]:

In [148]:

personajes.to_dict(orient='records')

Out[148]:

[{'Nombre': 'Juan Valdez',
  'Edad': 55,
  'Trabajos': [{'Inicio': 2010,
    'Fin': '',
    'Cargo': 'Arriero',
    'Compañía': 'Café de Colombia'}]},
 {'Nombre': 'Álvaro Uribe Vélez',
  'Edad': 69,
  'Trabajos': [{'Inicio': 2013,
    'Fin': '2020',
    'Cargo': 'Senador',
    'Compañía': 'Senado de la República de Colombia'},
   {'Inicio': 2020, 'Fin': '', 'Cargo': 'Influencer', 'Compañía': 'Twitter'}]}]

In [149]:

from IPython.display import JSON

In [150]:

JSON( personajes.to_dict(orient='records') )

Out[150]:

<IPython.core.display.JSON object>

Actividad

Obtenga el último trabajo de Álvaro Uribe Vélez

In [151]:

personajes[personajes['Nombre']=='Álvaro Uribe Vélez'
          ].get('Trabajos'
          ).apply(lambda l: [d.get('Cargo') for d in l if not d.get('Fin')]
          ).str[0].to_list()[0]

Out[151]:

'Influencer'

We have shown that the simple two dimensional spreadsheets where each cell values is a simple type like string, integer, or float, can be represented as a dictionary of lists values or a list of dictionary column-value assignment.

We can go further and allow to store in the value itself a more general data structure, like nested lists and dictionaries. This allows advanced data-analysis when the apply methos is used to operate inside the nested lists or dictionaries.

See for example:

World wide web

There are really three kinds of web

The normal web,
The deep web,
The machine web. The web for machine readable responses. It is served in JSON or XML formats, which preserve programming objects.

Normal web

In [ ]:

pd.read_html('https://en.wikipedia.org/wiki/COVID-19_pandemic_by_country_and_territory')[0][1:]

Real world example: microsoft academics

Machine web

For example, consider the following normal web page:

https://inspirehep.net/literature?q=doi:10.1103/PhysRevLett.122.132001

about a Scientific paper with people from the University of Antioquia. A machine web version can be easily obtained in JSON just by attaching the extra parameter &of=recjson, and direcly loaded from Pandas, which works like a browser for the third web:

In [ ]:

import pandas as pd

In [46]:

df=pd.read_json('https://inspirehep.net/api/literature?q=doi:10.1103/PhysRevLett.122.132001')

In [70]:

df

Out[70]:

We can use all the previous methods to extract the authors from 'Antioquia U.':

Note: For a dictionary, d is safer to use d.get('key') instead of just d['key'] to obtain some key, because not error is generated if the requested key does not exists at all

In [134]:

df[df['hits'].apply(lambda l: isinstance(l,list))]['hits' # extract cell with list
            ].apply(lambda l: [d.get('metadata') for d in l] # metadata of article
            ).str[0 #get the matched article dictionary
            ].str['authors' # get list of authors → l
            ].apply(lambda l: [ f'{d.get("first_name")} {d.get("last_name")}' for d in l  #author is a dictionary → d
                               #d.get('affiliations') is a list  of dictionaries → dd                               
                               if 'Antioquia U.' in [dd.get('value') for dd in d.get('affiliations')] 
                              ])

Out[134]:

hits    [Jhovanny Mejia Guisao, José David Ruiz Alvarez]
Name: hits, dtype: object

In [155]:

Authors=df[df['hits'].apply(lambda l: isinstance(l,list))]['hits' # extract cell with list
            ].apply(lambda articles: [article.get('metadata') for article in articles] # metadata of article
            ).str[0 #get the matched article dictionary
            ].str['authors' # get list of authors → l
            ]

In [157]:

names=Authors.apply(lambda authors: [ author.get('full_name') for author in authors  #author is a dictionary
                               #author.get('affiliations') is a list  of dictionaries → affiliation                              
                               if 'Antioquia U.' in [affiliation.get('value') for affiliation in author.get('affiliations')] 
                              ])
names[0]

Out[157]:

['Mejia Guisao, Jhovanny', 'Ruiz Alvarez, José David']

We can see that the column authors is quite nested: Is a list of dictionaries with the full information for each one of the authors of the article.

Activity: Check that the lenght of the auhors list coincides with the number_of_authors

In [ ]:

For further details see: https://github.com/restrepo/inspire/blob/master/gfif.ipynb

Activity: Repeat the same activity but using directly the JSON file, obtained with requests

In [139]:

#See: https://github.com/inspirehep/rest-api-doc/issues/4#issuecomment-645218074
import requests                                                                                                                                                      
response = requests.get('https://inspirehep.net/api/doi/10.1103/PhysRevLett.122.132001')                                                                              
authors = response.json()['metadata']['authors']                                                                                                                     
names = [author.get('full_name')
              for author in authors 
               if any(aff.get('value') == 'Antioquia U.' for aff in author.get('affiliations'))]
names

Out[139]:

['Mejia Guisao, Jhovanny', 'Ruiz Alvarez, José David']

Summary

Pandas_Cheat_Sheet PDF

ACTIVITIES

See:

Final remarks

With basic scripting and Pandas we already have a solid environment to analyse data. We introduce the other libraries motivated with the extending the capabilities of Pandas

Appendix

Summary with ChatGPT

Created in Deepnote