#!/usr/bin/env python
# coding: utf-8

# In[ ]:


import numpy as np
import pandas as pd
data=pd.read_csv('Credit Card files.xlsx')
print(data)
data.head()
data.tail()

#Set Seed and generate numbers randomly- Random Number column
np.random.seed(10)
data['randNumCol'] = np.random.randint(1, 1000000, data.shape[0])

print(data['randNumCol'])

#Sort by the new column randNumCol
data = data.sort_values(by=['randNumCol'], ascending=True)

#add a column for generating incremental sequence of numbers to use it for Unique_Id generation
data['Auto_SEQ'] = range(0, 0+len(data))
print(data['Auto_SEQ'])

#convert the two columns to object datatype to combine them to form the unique ID

data['Auto_SEQ']=data['Auto_SEQ'].astype(str)
data['UniqueID']=data['UniqueID'].astype(str)
data.dtypes

data["Name Length"]=data["Unique Record ID"].str.len()
print(data["Name Length"])

#data.dtypes

if (np.any(data["Name Length"] != 6)):
    data["UniqueID"]=  data["UniqueID"].str.zfill(6)
else: 0

# Creating a unique_ID field and appending the two columns together
print(data['UniqueID'])
data["UniqueID"]= data['Auto_SEQ']+data['Unique Record ID']
  
# For fixed length of Unique_ID
data["UniqueID1"]= data["UniqueID"].str.zfill(11) 
print(data['UniqueID1'])

#data['Zip'].unique()
#data['Zip'] = data['Zip'].replace('\.\d*', '', regex=True)
#data['Zip']= data['Zip'].map(float)
#data["Zip"] = [float(str(i).replace("", "")) for i in data["Zip"]]
#data['Zip']= data['Zip'].astype(int)
data['Zip']= data['Zip'].fillna(0).astype(int)
#df.birth_year.astype(int)
print(data['Zip'])
#data['Zip'] = data['Zip'].astype(float).astype(int)
data = data.replace(np.nan,'')

data = data.replace(np.nan,'')
#data = data.replace(np.nan,'')

#print(data.head())
newdata = pd.DataFrame(data, columns = ['UniqueID1', 'First Name', 'Last Name','Middle Initial','Street Name', 'City', 'State', 'Zip', 'Social Security Number'])
newdata.dtypes
#print(newdata.head())

np.savetxt(r'/FinalOutput.txt', newdata.values, fmt='%+11s %+20s %+21s %+1s %+45s %+26s %+2s %+5s %+9s', delimiter=' ')

