How to create Excel file from two SQLite tables using condition in pandas?

user_3030327 0
Viewed 204

I have two SQLite tables as Sqlite Table 1 and Sqlite Table 2. Table1 has ID,Name and Code columns. Table2 has ID, Values and Con columns. I want to create Excel as ID,Name,Code and Values Columns. ID,Name and Code columns comes from Table1 and Values column comes from table2 with sum value of Values column of table2 with two conditions are ID columns should be match and Con column satisfied with Done Value.

Below image is for reference: enter image description here

pandas python-3.x sqlite
2 Answers

I would approach this problem in steps. First extract the sql tables into pandas dataframes. I am no expert on that aspect of the problem, but assuming you have two dataframes like the following:

df1 =   ID  Name    Code
      0 1   a       1a
      1 2   b       2b
      2 3   a       3c

and

df2 =   ID  Values  Con
     0  1   5       Done
     1  2   9       No
     2  1   7       Done
     3  2   4       No
     4  1   8       No
     5  3   1      Done  

def sumByIndex(dx, row):
    # return sum value or 0 if ID doesn't exist
    idx = row['ID']
    st = list(dx['ID'])
    if idx in st:
        return dx[dx['ID'] == idx]['Values'].values[0]
    else:
        return 0    

def combineFrames(d1, d2):
    #Return updated version of d1 with "Values" column added
    d3 = d2[d2['Con'] == 'Done'].groupby('ID', as_index= False).sum()
    
    d1['Values'] = d1.apply( lambda row: sumByIndex(d3, row), axis = 1)
    return d1

then print(combineFrames(df1, df2)) yields:

   ID Name Code  Values
0   1    a   1a      12
1   2    b   2b       0
2   3    a   3c       1
user_142490870

  My program obtains the data from sqllite table 1 and sqlite table 2 in the form of lists (tuples and lists) with the corresponding values of ID, Name, Code and ID, Values, Con by making the request to the database like this 'SELECT * FROM sqlite table 1'

# sqlite table 1
table1 = [[5674, 'a', '1a'], [3385, 'b', '2b'], [5548, 'a', '3c']]

# sqlite table 2
table2 = [(5674, 5, 'Done'), (3385, 9, 'No'), (5674, 7, 'Done'), (3385, 4, 'No'), (5674, 8, 'No'), (5548, 1, 'Done')]

  To begin I will add all the values Values in a dictionary that matches it with the corresponding ID

map_values = {table2[i][0]:0 for i in range(len(table2))}

for i in range(len(table2)):
  if (table2[i][2] == 'Done'):
      map_values[table2[i][0]] += table2[i][1]

then I define the pandas.DataFrame() instance using sqlite table 1 by this way:

df = pd.DataFrame(table1, index=[i for i in range(1, len(table1)+1)], columns=["ID", "Name", "Code"])

also the values of "Values" are stored in that order to later be added with a new Values column.

df["Values"] = list(map_values.values())

output:

     ID Name Code  Values
1  5674    a   1a      12
2  3385    b   2b       0
3  5548    a   3c       1

excel:

df.to_excel(r'./excel_file.xlsx', index=False)

excel

user_117174810
Related