Search code examples
pythonmysqlmatplotlibnested-loops

Iteration over nested loops generated from MYSQL

I'm trying to iterate over generated list coming from MYSQL as following :

result8 =[('January', 'Alexandria ', 2),
          ('January', 'Cairo', 2), 
          ('February', 'Alexandria ', 1), 
          ('March', 'Cairo', 1), 
          ('March', 'Alexandria ', 1), 
          ('April', 'Upper Egypt', 1), 
          ('April', 'Alexandria ', 1), 
          ('May', 'Alexandria ', 1), 
          ('June', 'Delta ', 1), 
          ('June', 'Alexandria ', 1), 
          ('July', 'Giza', 1)]

Firstly, I tried to collect all items related to same month, as supposed after that drawing a chart(stacked column chart in matplotlib)

jan=['January']
feb=['February']
mar=['March']
apr=['April']
may=['May']
jun=['June']
july=['July']
aug=['August']
sept=['September']
october=['October']
nov=['November']
dec=['December']

I did a function to iterate and append distinct month information:

def seperated_month(j,month,my_list):
    for i in result8:
        if i[j] == month:
            my_list.append(i[1])
            my_list.append(i[2])

seperated_month(0, 'January', jan)
seperated_month(0, 'February', feb)
seperated_month(0, 'March', mar)
seperated_month(0, 'April', apr)
seperated_month(0, 'May', may)
seperated_month(0, 'June', jun)
seperated_month(0, 'July', july)
seperated_month(0, 'August', aug)
seperated_month(0, 'September', sept)
seperated_month(0, 'October', october)
seperated_month(0, 'November', nov)
seperated_month(0, 'December', dec)

This is the output:

['January', 'Alexandria ', 2, 'Cairo', 2]
['February', 'Alexandria ', 1]
['March', 'Alexandria ', 1, 'Cairo', 1]
['April', 'Upper Egypt', 1, 'Alexandria ', 1]
['May', 'Alexandria ', 1]
['June', 'Delta ', 1, 'Alexandria ', 1]
['July', 'Giza', 1]
['August']
['September']
['October']
['November']
['December']

Now supposed to collect 5 lists information:

alex =[]
cairo =[]
giza =[]
delta=[]
upper_egypt=[]

So I collect all months together to start iterating to collect information of each city:

all_list = [jan,feb,mar,apr,may,jun,july,aug,sept,october,nov,dec]

 for i in all_list:
     if all_list[i] == 'Cairo':
         cairo.append(all_list[i+1]))
     else:
         cairo.append(int(0))

And the reason I collect all months together, to make an iteration over 12 months and in case there is no data it'll be replace by zero.

But I can't do that iteration, as all_list became "nested lists", and I can't iterate on each item. And if I used nested for loop, index will be more or less than 12 months, based on length of data inside.

My request is to iterate over all_list and get the append information based on "name of city".

for example at the end :

cairo = [2,0,1,0,0,0,0,0,0,0,0,0]
Solution

  • You should use an appropriate containers and module for this task, not use dozens of variables.

    I suggest pandas/DataFrame:

    import pandas as pd
df = (pd.DataFrame(result8, columns=['month', 'city', 'value'])
        .pivot(index='month', columns='city', values='value')
     )
df.plot.bar(stacked=True)

    output:

    month        April  February  January  July  June  March  May
city                                                         
Alexandria     1.0       1.0      2.0   NaN   1.0    1.0  1.0
Cairo          NaN       NaN      2.0   NaN   NaN    1.0  NaN
Delta          NaN       NaN      NaN   NaN   1.0    NaN  NaN
Giza           NaN       NaN      NaN   1.0   NaN    NaN  NaN
Upper Egypt    1.0       NaN      NaN   NaN   NaN    NaN  NaN

    stacked bar plot