Python Forum

Hi,

How can I reindex columns? I get an error that says "ValueError: Length mismatch: Expected axis has 18 elements, new values have 5 elements"

The excel file is attached.

import pandas as pd
import getpass
import sys
import datetime
from ftplib import FTP_TLS
import sys

user_id = getpass.getuser()

# Read Download and Dealers
data = pd.read_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_Support.csv',engine='python',sep=";")

data.head()

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'])
data.columns = ['Local_ID','ID bilhete','Chassis','Entrada','Escalado']

# Add Telefone
data["Telefone"]=1

# Add current date column
data['Data Envio'] = pd.to_datetime('today').strftime("%d/%m/%Y")


data

dealers
# Merge, remove extra columns,
data_merged = data.merge(dealers,how='left', left_on='Local_ID',right_on='Código')

data_merged
data_merged = data_merged.loc[:,["Telefone","Local","ID bilhete","Chassis","Entrada","Escalado", "Data Envio"]]
data_merged.columns = ['Telefone', 'Instalações', 'ID bilhete', 'Chassis', 'Entrada', 'Escalado', 'Data Envio']

# create today name
now = datetime.datetime.now()
Nday = str(now.day).zfill(2)
Nmonth = str(now.month).zfill(2)
Nyear = now.year
name = "Teleservices" + "_" + str(Nyear)+ "_" +str(Nmonth)+ "_" +str(Nday)

# Save file
data_merged.to_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_V2/' +  name + '.csv',sep=";",index=False,encoding='utf-8-sig')
#data_merged.to_csv('C:/Users/rfcr/Rigor/Caetano Retail - Organização/1 - Baviera/5 - Power BI/Teleservices/Daily Tickets/' +  name + '.csv',sep=";",index=False,encoding='utf-8-sig')

#Login to FTP (TLS secure)
ftp = FTP_TLS()
ftp.set_debuglevel(2)
ftp.connect('ftp.gruposalvadorcaetano.pt', port=21)
ftp.login(user='cretail.csi', passwd='cSvJCasuo2kzTH')

# Open File
file = open('C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_V2/' +  name + '.csv','rb')

# Change directory in FTP
ftp.cwd('/CCENTER/IN')

# Upload file to FTP with correct name
ftp.storbinary('STOR ' + name + '.csv', file)

# Close FTP and File
file.close()
ftp.close()

# Finsih Script
print("Finished")
sys.exit()

Hi rr1997,

I don't think you have uploaded the File, could you do it again ?

And I think I will be able to help you.

Regards

Eddie Winch

(Feb-23-2021, 03:22 PM)eddywinch82 Wrote: [ -> ]Hi rr1997,

I don't think you have uploaded the File, could you do it again ?

And I think I will be able to help you.

Regards

Eddie Winch

Hi, I can't attach files, so this url have my file, can you please check that?

https://filebin.net/wzqr9zvflgi3nmxb

Hi rr1997,

Many thanks for posting the Url for the File,

I will get back to you soon.

Regards

Eddie Winch

Hi rr1997,

If I change the following line Of Code, which you have provided.

From :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'])

To :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'], axis='columns')

I now get the Error :-

Error:
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-19-b1f6520698fe> in <module>
     28 dealers
     29 # Merge, remove extra columns,
---> 30 data_merged = data.merge(dealers,how='left', left_on='Local_ID',right_on='Código')
     31 
     32 data_merged

c:\python39\lib\site-packages\pandas\core\frame.py in merge(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
   7948         from pandas.core.reshape.merge import merge
   7949 
-> 7950         return merge(
   7951             self,
   7952             right,

c:\python39\lib\site-packages\pandas\core\reshape\merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
     72     validate=None,
     73 ) -> "DataFrame":
---> 74     op = _MergeOperation(
     75         left,
     76         right,

c:\python39\lib\site-packages\pandas\core\reshape\merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
    650             self.right_join_keys,
    651             self.join_names,
--> 652         ) = self._get_merge_keys()
    653 
    654         # validate the merge keys dtypes. We may need to coerce

c:\python39\lib\site-packages\pandas\core\reshape\merge.py in _get_merge_keys(self)
   1003                     if not is_rkey(rk):
   1004                         if rk is not None:
-> 1005                             right_keys.append(right._get_label_or_level_values(rk))
   1006                         else:
   1007                             # work-around for merge_asof(right_index=True)

c:\python39\lib\site-packages\pandas\core\generic.py in _get_label_or_level_values(self, key, axis)
   1561             values = self.axes[axis].get_level_values(key)._values
   1562         else:
-> 1563             raise KeyError(key)
   1564 
   1565         # Check for duplicates

KeyError: 'Código'

​
​

Could you also upload, your other .csv File.

Teleservices_Support.csv ?

As that is also used in the Code you provide, and I think it's absence, may be causing that KeyError.

If you have that File, The Code you provide may work now, with the change I made. But I would like to try
the Code out myself, with the other .csv File if that is okay with you ?

Eddie Winch

(Feb-23-2021, 08:11 PM)eddywinch82 Wrote: [ -> ]Hi rr1997,

If I change the following line Of Code, which you have provided.

From :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'])

To :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'], axis='columns')

I now get the Error :-

Error:
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-19-b1f6520698fe> in <module>
     28 dealers
     29 # Merge, remove extra columns,
---> 30 data_merged = data.merge(dealers,how='left', left_on='Local_ID',right_on='Código')
     31 
     32 data_merged

c:\python39\lib\site-packages\pandas\core\frame.py in merge(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
   7948         from pandas.core.reshape.merge import merge
   7949 
-> 7950         return merge(
   7951             self,
   7952             right,

c:\python39\lib\site-packages\pandas\core\reshape\merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
     72     validate=None,
     73 ) -> "DataFrame":
---> 74     op = _MergeOperation(
     75         left,
     76         right,

c:\python39\lib\site-packages\pandas\core\reshape\merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
    650             self.right_join_keys,
    651             self.join_names,
--> 652         ) = self._get_merge_keys()
    653 
    654         # validate the merge keys dtypes. We may need to coerce

c:\python39\lib\site-packages\pandas\core\reshape\merge.py in _get_merge_keys(self)
   1003                     if not is_rkey(rk):
   1004                         if rk is not None:
-> 1005                             right_keys.append(right._get_label_or_level_values(rk))
   1006                         else:
   1007                             # work-around for merge_asof(right_index=True)

c:\python39\lib\site-packages\pandas\core\generic.py in _get_label_or_level_values(self, key, axis)
   1561             values = self.axes[axis].get_level_values(key)._values
   1562         else:
-> 1563             raise KeyError(key)
   1564 
   1565         # Check for duplicates

KeyError: 'Código'

​
​

Could you also upload, your other .csv File.

Teleservices_Support.csv ?

As that is also used in the Code you provide, and I think it's absence, may be causing that KeyError.

If you have that File, The Code you provide may work now, with the change I made. But I would like to try
the Code out myself, with the other .csv File if that is okay with you ?

Eddie Winch

Hi,

sorry for the waiting time

here it is the url:

https://filebin.net/643nkv5flvy2ceb8

Hi rr1997,

Many thanks for uploading, the other .csv File for me.

When I run the Code again, with both .csv Files present on my computer, I now get an Output .csv
File, do you now get the Output, you wanted to get ? I.e. the Output .csv File that I get, when you also run the Code ?

Best Regards

Eddie Winch

(Feb-24-2021, 03:31 PM)eddywinch82 Wrote: [ -> ]Hi rr1997,

Many thanks for uploading, the other .csv File for me.

When I run the Code again, with both .csv Files present on my computer, I now get an Output .csv
File, do you now get the Output, you wanted to get ? I.e. the Output .csv File that I get, when you also run the Code ?

Best Regards

Eddie Winch

When I run the code I can't get that output .csv and I want to get that .csv

Hi rr1997,

This is the Code I am using :-

import pandas as pd
import getpass
import sys
import datetime
from ftplib import FTP_TLS
import sys
 
user_id = getpass.getuser()
 
# Read Download and Dealers
data = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Teleservices_Support.csv',engine='python',sep=";")
 
data.head()
 
data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'], axis='columns')
data.columns = ['Local_ID','ID bilhete','Chassis','Entrada','Escalado']
 
# Add Telefone
data["Telefone"]=1
 
# Add current date column
data['Data Envio'] = pd.to_datetime('today').strftime("%d/%m/%Y")
 
 
data
 
dealers
# Merge, remove extra columns,
data_merged = data.merge(dealers,how='left', left_on='Local_ID',right_on='Código')
 
data_merged
data_merged = data_merged.loc[:,["Telefone","Local","ID bilhete","Chassis","Entrada","Escalado", "Data Envio"]]
data_merged.columns = ['Telefone', 'Instalações', 'ID bilhete', 'Chassis', 'Entrada', 'Escalado', 'Data Envio']
 
# create today name
now = datetime.datetime.now()
Nday = str(now.day).zfill(2)
Nmonth = str(now.month).zfill(2)
Nyear = now.year
name = "Teleservices" + "_" + str(Nyear)+ "_" +str(Nmonth)+ "_" +str(Nday)
 
# Save file
data_merged.to_csv('C:/Users/'+ user_id +'/Desktop/' +  name + '.csv',sep=";",index=False,encoding='utf-8-sig')
#data_merged.to_csv('C:/Users/rfcr/Rigor/Caetano Retail - Organização/1 - Baviera/5 - Power BI/Teleservices/Daily Tickets/' +  name + '.csv',sep=";",index=False,encoding='utf-8-sig')
 
#Login to FTP (TLS secure)
ftp = FTP_TLS()
ftp.set_debuglevel(2)
ftp.connect('ftp.gruposalvadorcaetano.pt', port=21)
ftp.login(user='cretail.csi', passwd='cSvJCasuo2kzTH')
 
# Open File
file = open('C:/Users/'+ user_id +'/Desktop/' +  name + '.csv','rb')
 
# Change directory in FTP
ftp.cwd('/CCENTER/IN')
 
# Upload file to FTP with correct name
ftp.storbinary('STOR ' + name + '.csv', file)
 
# Close FTP and File
file.close()
ftp.close()
 
# Finsih Script
print("Finished")
sys.exit()

I have both .csv Files on my Desktop, and have the Output .csv File saved to the desktop too.

Try the above Code, you may not be getting the Output .csv File I get, due to an incorrect File Path(s) in the Code you are using, try putting both .csv Files on your Desktop like me.

Before trying the above suggestions, try changing the following two lines of Code.

From :-

# Read Download and Dealers
data = pd.read_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_Support.csv',engine='python',sep=";")

To :-

# Read Download and Dealers
data = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Portable Script/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_Support.csv',engine='python',sep=";")

And changing this line of Code.

From :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'])

To :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'], axis='columns')

In the Code you provide, at the start of this Thread.

Best Regards

Eddie Winch

(Feb-24-2021, 07:18 PM)eddywinch82 Wrote: [ -> ]Hi rr1997,

This is the Code I am using :-

import pandas as pd
import getpass
import sys
import datetime
from ftplib import FTP_TLS
import sys
 
user_id = getpass.getuser()
 
# Read Download and Dealers
data = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Teleservices_Support.csv',engine='python',sep=";")
 
data.head()
 
data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'], axis='columns')
data.columns = ['Local_ID','ID bilhete','Chassis','Entrada','Escalado']
 
# Add Telefone
data["Telefone"]=1
 
# Add current date column
data['Data Envio'] = pd.to_datetime('today').strftime("%d/%m/%Y")
 
 
data
 
dealers
# Merge, remove extra columns,
data_merged = data.merge(dealers,how='left', left_on='Local_ID',right_on='Código')
 
data_merged
data_merged = data_merged.loc[:,["Telefone","Local","ID bilhete","Chassis","Entrada","Escalado", "Data Envio"]]
data_merged.columns = ['Telefone', 'Instalações', 'ID bilhete', 'Chassis', 'Entrada', 'Escalado', 'Data Envio']
 
# create today name
now = datetime.datetime.now()
Nday = str(now.day).zfill(2)
Nmonth = str(now.month).zfill(2)
Nyear = now.year
name = "Teleservices" + "_" + str(Nyear)+ "_" +str(Nmonth)+ "_" +str(Nday)
 
# Save file
data_merged.to_csv('C:/Users/'+ user_id +'/Desktop/' +  name + '.csv',sep=";",index=False,encoding='utf-8-sig')
#data_merged.to_csv('C:/Users/rfcr/Rigor/Caetano Retail - Organização/1 - Baviera/5 - Power BI/Teleservices/Daily Tickets/' +  name + '.csv',sep=";",index=False,encoding='utf-8-sig')
 
#Login to FTP (TLS secure)
ftp = FTP_TLS()
ftp.set_debuglevel(2)
ftp.connect('ftp.gruposalvadorcaetano.pt', port=21)
ftp.login(user='cretail.csi', passwd='cSvJCasuo2kzTH')
 
# Open File
file = open('C:/Users/'+ user_id +'/Desktop/' +  name + '.csv','rb')
 
# Change directory in FTP
ftp.cwd('/CCENTER/IN')
 
# Upload file to FTP with correct name
ftp.storbinary('STOR ' + name + '.csv', file)
 
# Close FTP and File
file.close()
ftp.close()
 
# Finsih Script
print("Finished")
sys.exit()

I have both .csv Files on my Desktop, and have the Output .csv File saved to the desktop too.

Try the above Code, you may not be getting the Output .csv File I get, due to an incorrect File Path(s) in the Code you are using, try putting both .csv Files on your Desktop like me.

Before trying the above suggestions, try changing the following two lines of Code.

From :-

# Read Download and Dealers
data = pd.read_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv('C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_Support.csv',engine='python',sep=";")

To :-

# Read Download and Dealers
data = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Portable Script/Download_Report_Teleservices.csv',sep=";")
dealers = pd.read_csv(r'C:/Users/'+ user_id +'/Desktop/Portable Script/Teleservices_Support.csv',engine='python',sep=";")

And changing this line of Code.

From :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'])

To :-

data = data.reindex(['Número do ponto de serviço autorizado','ID','VIN','Data de criação','Escalado em'], axis='columns')

In the Code you provide, at the start of this Thread.

Best Regards

Eddie Winch

Thank you so much!!