Python pandas as simple DB

As noted earlier, pandas is a powerful library for data analysis, which can prove useful in almost any problem you tackle. In this example, we will keep our yarn inventory in a CSV file. Again, the first row contains our header or field names and the subsequent four rows the data. I’ve added “Quantity” field, which we will manage in more detail in the second part of this example.

> cat example.csv
Colour,Weight,Type,Quantity
blue,chunky,wool,3
green,sport,cotton,10
yellow,extra-fine,bamboo,7
red,lace,wool,42

We build on the code example before to provide methods to update inventory, both in the file and the copy within our python script. We shall use it to fix a mistake in the inventory.

import pandas as pd

filename = "example.csv"

def updateField(idField, id, attribute, value):
  # read the file
  df = pd.read_csv(filename)
  # find the row where idField has a value of id, 
  # and set its the given attribute to the given value
  df.loc[df[idField] == id,[attribute]] = [value]
  # update the file
  df.to_csv(filename, index=False)
  # return the latest version to the caller
  return df

  
def printInventory(inventory):
  for index, row in inventory.iterrows():
    print(str(row['Quantity']) + ": " + row['Weight'] + " "\
      + row['Colour'] + " yarn made from " + row['Type'])


inventory = pd.read_csv(filename)
printInventory(inventory)

print("--Correct colour in inventory--")
inventory = updateField('Weight', 'lace', 'Colour', 'pink')
printInventory(inventory)

When we run it, the program shows us the original inventory, as well as what it looks like once we’ve made the change

> python inventory.py
3: chunky blue yarn made from wool
10: sport green yarn made from cotton
7: extra-fine yellow yarn made from bamboo
42: lace red yarn made from wool
--Correct colour in inventory--
3: chunky blue yarn made from wool
10: sport green yarn made from cotton
7: extra-fine yellow yarn made from bamboo
42: lace pink yarn made from wool

> cat example.csv
Colour,Weight,Type,Quantity
blue,chunky,wool,3
green,sport,cotton,10
yellow,extra-fine,bamboo,7
pink,lace,wool,42

If I want to update the inventory when I make a yarn sale, I can use the following method

def deprecateValueInField(idField, id, attribute):
  # read the file
  df = pd.read_csv(filename)
  # find the row where idField has a value of id, 
  # and set its the given attribute to the given value
  df.loc[df[idField] == id, [attribute]] = df.loc[df[idField] == \
    id, [attribute]] - 1
  # update the file
  df.to_csv(filename, index=False)
  # return the latest version to the caller
  return df

Which we can call when the sale is made, and update both our local copy of the inventory and the one stored on disk

print("--Sold yarn, updating inventory--")
inventory = deprecateValueInField('Weight', 'lace', 'Quantity')
printInventory(inventory)

Resulting in the following when we run the code

3: chunky blue yarn made from wool
10: sport green yarn made from cotton
7: extra-fine yellow yarn made from bamboo
42: lace pink yarn made from wool
--Sold yarn, updating inventory--
3: chunky blue yarn made from wool
10: sport green yarn made from cotton
7: extra-fine yellow yarn made from bamboo
41: lace pink yarn made from wool

And the following stored in our example.csv file

Colour,Weight,Type,Quantity
blue,chunky,wool,3
green,sport,cotton,10
yellow,extra-fine,bamboo,7
pink,lace,wool,41

See also Dynamic Command Line Arguments with Python argparse to see how you can start building a command line to allow the user query or update this little database.