Perform date filtering within a column using Python

Asked

Viewed 624 times

0

good afternoon.

I need to do an Excel file filtering, this filtering consists of taking a particular column and performing its filter. I’m a beginner here and I don’t know if I’m making the most complicated way to something simple. Could you help me?

#!/usr/bin/env python

# coding: utf-8
#Import files

import pandas as pd
import datetime as dt
import xlrd
import os.path

'''É aqui que eu não sei o motivo de não estar filtrando, preciso pegar
 a data de hoje e fazer - 6 dias para pegar o limiar de uma semana 
 exatamente.'''

#Create a Pandas dataframe from the data.
xlsx = pd.ExcelFile('D:/Python/Customer Parts Requirement.xlsx')
df = pd.read_excel(xlsx, 'Customer Parts Requirement')
start_date = dt.timedelta(days=7)
end_date = pd.Timestamp.today()
real_end = end_date - start_date
df['Invoice_Date'] = pd.to_datetime(df['Invoice_Date'])
mask = (df['Invoice_Date'] > real_end) & (df['Invoice_Date'] <= end_date)

#Create Dictionary 
columnRMA = ['RO No.', 'RMA No.', 'RMA Status']
columnSO = ['RO No.', 'SO No.', 'SO Status', 'HW Invoice No.']
columnInfo = ['SR No.', 'RO No.', 'Customer Invoice No.', 'Acceptence Date', 'Return Order No']
columnNF = ['RO No.', 'HW Invoice No.', 'Invoice_Date']

#Create File
writer = pd.ExcelWriter('D:/Python/SO and RMA Update.xlsx', engine='xlsxwriter', date_format='mmmm dd yyyy')

# Convert the dataframe to an XlsxWriter Excel object.
df[columnRMA].to_excel(writer, sheet_name="RMA",index=False)
df[columnSO].to_excel(writer, sheet_name="SO", index=False)
df[columnInfo].to_excel(writer, sheet_name="Info", index=False)
df[columnNF].to_excel(writer, sheet_name="NF", index=False)

# In[8]:

#Save File
writer.save()

# In[9]:

#Message
from tkinter import *
class Application:
    def __init__(self, master=None):
        self.widget1 = Frame(master)
        self.widget1.pack()
        self.msg = Label(self.widget1, text="Arquivo Criado Com Sucesso")
        self.msg["font"] = ("Verdana", "10", "italic", "bold")
        self.msg.pack ()
root = Tk()
Application(root)
root.mainloop()
python pandas

  • Hello, so I never worked using Excel, however excel can export files. CSV, which are very easy to work in python, recommend a search. Aprçs

  • Hey, Sidon, what’s up? Thanks for answering, but the output that is exported is in xlsx, in practice, Python should select the invoice_date column and apply a day filter. As an example, today (current date) - x days, which would be 6 days. Then whatever was in that range Python would select and execute the rest of the code. But when I tell him to do it I don’t know what happens that doesn’t filter the data.

1 answer

0

    #Import files
    import pandas as pd
    import datetime as dt
    from datetime import date
    from datetime import datetime, timedelta
    import xlrd
    import numpy as np

    #Open Files
    xlsx = pd.ExcelFile('D:/Python/Customer Parts Requirement.xlsx', index_col="invoice_date")
    df = pd.read_excel(xlsx, 'Customer Parts Requirement')

    #Create a Pandas dataframe from the data.
    start_date = dt.timedelta(days=7)
    end_date = date.today()
    real_end = (end_date - start_date)
    df['invoice_date'] = pd.to_datetime(df['invoice_date'])
    data = (pd.to_datetime(df['invoice_date']))

    #Create Dictionary 
    columnRMA = ['RO No.', 'RMA No.', 'RMA Status']
    columnSO = ['RO No.', 'SO No.', 'SO Status', 'HW Invoice No.']
    columnInfo = ['SR No.', 'RO No.', 'Customer Invoice No.', 'Acceptence Date', 'Return Order No']
    columnNF = ['RO No.', 'HW Invoice No.', 'invoice_date']

    #Create File
    writer = pd.ExcelWriter('D:/Python/SO and RMA Update.xlsx', engine='xlsxwriter', date_format='mmmm dd yyyy')

    # Convert the dataframe to an XlsxWriter Excel object.
    df[columnRMA].to_excel(writer, sheet_name="RMA",index=False)
    df[columnSO].to_excel(writer, sheet_name="SO", index=False)
    df[columnInfo].to_excel(writer, sheet_name="Info", index=False)
    df[columnNF].to_excel(writer, sheet_name="NF", index=False)

    #Save File
    writer.save()

    #Message
    from tkinter import *
    class Application:
        def __init__(self, master=None):
            self.widget1 = Frame(master)
            self.widget1.pack()
            self.msg = Label(self.widget1, text="Arquivo Criado Com Sucesso")
            self.msg["font"] = ("Verdana", "10", "italic", "bold")
            self.msg.pack ()
    root = Tk()
    Application(root)
    root.mainloop()



'''''Pessoal, consegui resolver o problema da filtragem de certa forma, mas agora estou querendo salvar essa filtragem que foi realizada em data = (pd.to_datetime(df['invoice_date'])) no próprio arquivo eliminando as linhas que não baterem com a condição, para depois criar os conteiners''''''

# Convert the dataframe to an XlsxWriter Excel object.
df[columnRMA].to_excel(writer, sheet_name="RMA",index=False)
df[columnSO].to_excel(writer, sheet_name="SO", index=False)
df[columnInfo].to_excel(writer, sheet_name="Info", index=False)
df[columnNF].to_excel(writer, sheet_name="NF", index=False)

''''''Alguém pode me ajudar a resolver isso?''''

Browser other questions tagged python pandas

You are not signed in. Login or sign up in order to post.