You can use str.zfill:
#numeric as string
df = pd.DataFrame({'Section':['1', '2', '3', '4', 'SS', '15', 'S1', 'A1']})
df['Section'] = df['Section'].str.zfill(2)
print (df)
Section
0 01
1 02
2 03
3 04
4 SS
5 15
6 S1
7 A1
If mixed numeric with strings first cast to string:
df = pd.DataFrame({'Section':[1, 2, 3, 4, 'SS', 15, 'S1', 'A1']})
df['Section'] = df['Section'].astype(str).str.zfill(2)
print (df)
Section
0 01
1 02
2 03
3 04
4 SS
5 15
6 S1
7 A1
pandas - Python add a leading zero to column with str and int - Stack Overflow
python - Add Leading Zeros to Strings in Pandas Dataframe - Stack Overflow
pandas question: how can I preserve leading zeros after saving to a CSV?
python pandas add leading zero to make all months 2 digits - Stack Overflow
I am working on a script where I am taking data and inserting it into a database.
The easiest way for me to do this was putting it in pandas dataframes and then loading it into the DB.
One column will have integers like social security numbers were they are always 9 digits and sometimes have leading 0's. All 9 digits are always expected whether its 123456789 or 012345678.
It seems to me that I can keep the leading 0 if I set the dtype to str. However as soon as it gets set to int it drops the leading zero.
Now the database is expecting INT so I believe what is happening as it is loaded into the database pandas is doing a conversion from str to int for the DB and dropping the leadign zero.
Relevant part of script:
data = pd.read_csv(f'./Data/csv/{filename}', encoding = 'utf-16', index_col=False)
data = data.replace({np.nan: None})
conn = pyodbc.connect('DRIVER={SQL Server};'
f'SERVER={server};'
f'DATABASE={database};'
f'UID={user};'
f'PWD={password}')
cursor = conn.cursor()
for row in data.itertuples(index=False):
cursor.execute(eval(query), row)
cursor.commit()
cursor.close()
conn.close()Is there a way to retain or FILL the leading 0 spaces? (zfill does not work for INT as far as I know)
Or would it be better to change the schema of the DB to varchar or something even though that would be a PITA?
Thanks!
You can use str.zfill:
#numeric as string
df = pd.DataFrame({'Section':['1', '2', '3', '4', 'SS', '15', 'S1', 'A1']})
df['Section'] = df['Section'].str.zfill(2)
print (df)
Section
0 01
1 02
2 03
3 04
4 SS
5 15
6 S1
7 A1
If mixed numeric with strings first cast to string:
df = pd.DataFrame({'Section':[1, 2, 3, 4, 'SS', 15, 'S1', 'A1']})
df['Section'] = df['Section'].astype(str).str.zfill(2)
print (df)
Section
0 01
1 02
2 03
3 04
4 SS
5 15
6 S1
7 A1
Try this
df['Section'] = df['Section'].apply(lambda x: x.zfill(2))
You get
Section
0 01
1 02
2 03
3 04
4 SS
5 15
6 S1
7 A1
str attribute contains most of the methods in string.
df['ID'] = df['ID'].str.zfill(15)
See more: http://pandas.pydata.org/pandas-docs/stable/text.html
Try:
df['ID'] = df['ID'].apply(lambda x: '{0:0>15}'.format(x))
or even
df['ID'] = df['ID'].apply(lambda x: x.zfill(15))
EDIT: Solved! Well actually there wasn't really anything to solve lol I'm just a noob and a half. To those interested in the answer, please see the reply by u/spez_edits_thedonald. Thank you to everyone who helped. Wow i love this sub so much
I have a gigantic pandas dataframe (8 million rows) and I need to upload it's contents to our platform at work. Side note: I'm kind of excited because no one in my office has ever done this on this scale, so I'm hoping I can impress my bosses.
Unfortunately, there is a file size limit of 2 MB per upload into our platform. I can't use an API, which means I don't have the option of doing this row by row directly from the pandas dataframe itself (wish I could, that would make this a lot easier). This also means that I'm not really able to manipulate the data using pandas between file upload iterations... at least I dont think so? My plan is to slice my gigantic dataframe up into a bunch of smaller CSV files (I'm pretty sure I can figure out how to do this part myself) and then do some browser automation with selenium. Selenium sounds agonizing but honestly I'm cool with letting this run all night and then waking up tomorrow to see the finished product.
The problem is: when I save it to a CSV, the leading zeros in one of my columns get dropped. This is stopping me dead in my tracks. I can't figure out how to preserve leading zeros in the CSV itself. I know how to add leading zeros in a pandas df by doing:
df['my_column'] = df['my_column'].apply(lambda x: x.zfill(5)) but this doesn't help me once it's saved to the CSV
Here is an example of my dataframe:
| color | shape | identifier | |
|---|---|---|---|
| 0 | blue | circle | 06432 |
| 1 | red | square | 01245 |
| 2 | green | triangle | 08750 |
| 3 | yellow | oval | 12350 |
| 4 | orange | rectangle | 19862 |
This data is good and ready to go but when I save it to a csv by doing df.to_csv('saved_df.csv') it becomes this:
| color | shape | identifier | |
|---|---|---|---|
| 0 | blue | circle | 6432 |
| 1 | red | square | 1245 |
| 2 | green | triangle | 8750 |
| 3 | yellow | oval | 12350 |
| 4 | orange | rectangle | 19862 |
Please note how the leading zeros in df['identifier'] are dropped
Does anyone know how to keep leading zeros within the CSV file itself? Ultimately I need the leading zeros to be there after i do df.to_csv('saved_df.csv') and when I'm uploading the CSV to our platform.
I tried doing df['identifier'] = df['identifier'].astype(str) and then saving it to a csv but it seems like that column gets converted back to integers once it is saved to the csv.
I feel like I'm so close to impressing my bosses with this sheer amount of productivity. Any help would be greatly appreciated. Thank you, kind pythonistas of reddit :)
use map() method of Series with "{:02}".format:
data = """ Week product quantity Month
0 201301 coke 1.5 1
1 201302 fanta 1.7 2
2 201304 coke 3.6 5
3 201306 sprite 2.4 10
4 201308 pepsi 2.9 12
"""
import pandas as pd
import io
df = pd.read_csv(io.BytesIO(data), delim_whitespace=True)
df["Month"] = df.Month.map("{:02}".format)
In Python 2.7 you can format this value using
>>> month = 9
>>> '{:02}'.format(month)
'09'
here 2 in {:02} specifies convert the input digit in 2 chars by prefixing '0'. If input digit is of length 2 then that digit will remain unchanged.