Slice last 4 characters from a csv column in Pandas-CodePudding

I'm pretty new at Python and I am trying to get the last four characters from every row of data from a column in a CSV file. For example, I have column E for a large number of license plates. I'm able to successfully grab that column from the CSV file and write it in the appropriate spot in xlsx file. I've tried slicing the column in different ways, but I can't seem to get it right. Either I only get the first 4 license plates in the list or don't get the last four characters from each license plate. Below is the code I have right now that only displays the first four from the list.

Any help is greatly appreciated. Thank you in advance.

import pandas as pd

df_csv = pd.read_csv('data.csv', usecols=['license_plate'])
df_xlsx = pd.read_excel('report.xlsx', header=None)

license_plate = df_csv['license_plate']
license_plate = license_plate[-4:]

writer = pd.ExcelWriter('report.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay')

license_plate.to_excel(writer, sheet_name='Report', startcol=8, startrow=3, header=None, index=None)

writer.close()

I know there's something I have to change with this part of the code, but after looking at and trying different methods I can't seem to get the correct output. Below is the code I have and only shows the first 4 license plates in their entirety.

license_plate = df_csv['license_plate']
license_plate = license_plate[-4:]

An example of what I am trying to get is if I have a whole column of license plates and the first 3 license plates are: ['123456', 'ABCDEF', ['1A2B3C'] ... and so on. The output should be printed in the excel sheet as '3456' for the first row, 'CDEF' for the 2nd row, '2B3C' for the 3rd row, and so on until the list is completed.

CodePudding user response：

Using the str.slice function on each entry in the license plate column of your dataset, you will get the desired result. Here's the updated version of your code:

import pandas as pd
from openpyxl import load_workbook

df_csv = pd.read_csv('data.csv', usecols=['license_plate'])
df_xlsx = pd.read_excel('report.xlsx', header=None)

df_csv['license_plate'] = df_csv['license_plate'].str[-4:]

writer = pd.ExcelWriter('report.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay')
wb = load_workbook('report.xlsx')

df_csv['license_plate'].to_excel(writer, sheet_name='Report', startcol=8, startrow=3, header=None, index=None)

writer.close()

The str.slice function is applied to each entry in the license plate column using this code, and the final four characters are preserved. After that, the revised data is written to the excel sheet.