CODEWITHSUNDEEP
pythonsql-serverpandas
userPinealbody
userPinealbody

Reputation: 97

Using Pandas to join and append columns in a loop

I want to append columns from tables generated in a loop to a dataframe. I was hoping to accomplish this using pandas.merge, but it doesn't seem to be working out for me.

My code: 

from datetime import date
from datetime import timedelta
import pandas
import numpy
import pyodbc

date1 = date(2017, 1, 1)  #Starting Date
date2 = date(2017, 1, 10) #Ending Date

DateDelta = date2 - date1
DateAdd = DateDelta.days
StartDate = date1
count = 1

# Create the holding table
conn = pyodbc.connect('Server Information')
**basetable = pandas.read_sql("SELECT....") 

while count <= DateAdd:
    print(StartDate)
    **datatable = pandas.read_sql("SELECT...WHERE Date = "+str(StartDate)+"...")
    finaltable = basetable.merge(datatable,how='left',left_on='OrganizationName',right_on='OrganizationName')
    StartDate = StartDate + timedelta(days=1)
    count = count + 1
print(finaltable)

Shortened the select statements for brevity's sake, but the tables produced look like this:

**Basetable

School_District
---------------
District_Alpha
District_Beta
...
District_Zed

**Datatable

School_District|2016-01-01|
---------------|----------|
District_Alpha |  400     |
District_Beta  |  300     |
...            |  200     |
District_Zed   |  100     |

I have the datatable written so the column takes the name of the date selected for that particular loop, so column names can be unique once i get this up and running. My problem, however, is that the above code only produces one column of data. I have a good guess as to why: Only the last merge is being processed - I thought using pandas.append would be the way to get around that, but pandas.append doesn't "join" like merge does. Is there some other way to accomplish a sort of Join & Append using Pandas? My goal is to keep this flexible so that other dates can be easily input depending on our data needs.

In the end, what I want to see is:

School_District|2016-01-01|2016-01-02|...  |2016-01-10|
---------------|----------|----------|-----|----------|
District_Alpha |  400     |   1      |     |   45     |
District_Beta  |  300     |   2      |     |   33     |
...            |  200     |   3      |     |   5435   |
District_Zed   |  100     |   4      |     |   333    |

Upvotes: 0

Views: 863

Answers (1)

DYZ
DYZ

Reputation: 57033

Your error is in the statement finaltable = basetable.merge(datatable,...). At each loop iteration, you merge the original basetable with the new datatable, store the result in the finaltable... and discard it. What you need is basetable = basetable.merge(datatable,...). No finaltables.

Upvotes: 1

Related Questions