0

I was trying to fit my scenario with the below - but failed

Pandas - Python, deleting rows based on Date column

I have a output.csv file with the following columns 

Customer, Alertkey, Node, Alertgroup, FirstOccurrence,
TKT_Flag, X733SpecificProb, TKT_TicketNumber, TKT_Keyword

The file will be updated from database every 7 days incrementally with last 7 days data

So ideally I have to drop the first 7 days of data from the file itself.

I could write below but getting type error "TypeError: string indices must be integers"

import pandas as pd
from dateutil.relativedelta import relativedelta
from dateutil import parser


df=pd.read_csv('output.csv', usecols=['FirstOccurrence'],parse_dates=[0])
df=df['FirstOccurrence'].iloc[0]
dt = parser.parse(df)
SevenDays = dt + relativedelta( days = +7 )
df=df[(parser.parse(df['FirstOccurrence']) < SevenDays)].drop(df.columns)

There will be millions of lines. I am copying first few lines from 1st Jan 2016. But it will be from 1st Jan 2016 to till date. Every week it will append and should delete records of first 7 days - i.e first time it should delete records from 1st Jan to 6th Jan and so on

Customer,Alertkey,Node,Alertgroup,FirstOccurrence,TKT_Flag,X733SpecificProb,TKT_TicketNumber,TKT_Keyword
Cust1,Cust1_11_53_Services_Warning,Node_Cust1,ITM_K53_SERVICEMON,2016-01-01 00:12:59,1005,TOLPUKC_OS:25223174,INC000014799786,CGMIDDLEWARE_MEDIUM_CONNECTDIRECT
Cust1,Cust1_11_53_Services_Warning,Node1_Cust1,ITM_K53_SERVICEMON,2016-01-01 00:12:59,1005,TOLPUKC_OS:25223175,INC000014799785,CGMIDDLEWARE_MEDIUM_CONNECTDIRECT
Cust2,Cust2_21_NT_System_CPU_Critical,Cust2_Node8,ITM_NT_System,2016-01-01 00:15:48,101,PARPFRC_OS:21192843,INC000000628410,WINDOWS_MEDIUM_DEFPRODUCTSILVER
Cust3,Cust3_10352_LZ_TDW_DISK_Critica,Cust3_Node22,ITM_Linux_Disk,2016-01-01 00:17:05,200,TOLPUKC_OS:25223370,INC000001412280,CGMOM_HIGH_DEFPRODUCT
Cust6,Cust6_11_53_Services_Warning,Cust6_Node700,ITM_K53_SERVICEMON,2016-01-01 00:22:36,22,TOLPUKC_OS:25223601,INC000002250120,CGIOWINTELIMOC_MEDIUM_DEFPRODUCT
Ted Petrou
  • 59,042
  • 19
  • 131
  • 136
Anirban Banerjee
  • 93
  • 3
  • 9
  • Please include the _complete_ error message. – DYZ Jun 28 '17 at 01:59
  • Your code says drop columns not rows? – dartdog Jun 28 '17 at 02:28
  • Error as below File "C:\ProgramData\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 866, in runfile execfile(filename, namespace) File "C:\ProgramData\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 102, in execfile exec(compile(f.read(), filename, 'exec'), namespace) File "D:/Anirban_Backup/Drive_D/W0rk/Script/Python/DataBase_Connection/Delete_Rows_BasedOnTime.py", line 10, in df=df[(parser.parse(df['FirstOccurrence']) < SevenDays)].drop(df.columns) TypeError: string indices must be integers – Anirban Banerjee Jun 28 '17 at 03:39
  • DYZ Error as below File "C:\ProgramData\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 866, in runfile execfile(filename, namespace) File "C:\ProgramData\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 102, in execfile exec(compile(f.read(), filename, 'exec'), namespace) File "D:/Anirban_Backup/Drive_D/W0rk/Script/Python/DataBase_Connection/Delete_Rows_BasedOnTime.py", line 10, in df=df[(parser.parse(df['FirstOccurrence']) < SevenDays)].drop(df.columns) TypeError: string indices must be integers. dartdog No idea hw – Anirban Banerjee Jun 28 '17 at 03:45

1 Answers1

0

replace this : df=df[(parser.parse(df['FirstOccurrence']) < SevenDays)].drop(df.columns)

with: df = df.drop(df[(parser.parse(df['FirstOccurrence']) < SevenDays)].index, inplace=True)

try this hope this help you.

ammy
  • 618
  • 1
  • 5
  • 13
  • df = df.drop(df[(parser.parse(df['FirstOccurrence']) < SevenDays)].index, inplace=True) AttributeError: 'str' object has no attribute 'drop' – Anirban Banerjee Jun 28 '17 at 09:12