how do I achieve this in Python? Source file is a CSV file, and value of one column in that file is converted from numeric to day and month. Thank you very much in advance.
Example below:
Picture of the column: room column
In my python script, value should look below:
1-Feb ---> 2-1
2-Feb ---> 2-2
3-Mar ---> 3-3
4-Mar ---> 3-4
Here's my script.
import os
import pandas as pd
directory = 'C:/Path'
ext = ('.csv')
for filename in os.listdir(directory):
f = os.path.join(directory, filename)
if f.endswith(ext):
head_tail = os.path.split(f)
head_tail1 = 'C:/Path'
k =head_tail[1]
r=k.split(".")[0]
p=head_tail1 "/" r " - Revised.csv"
mydata = pd.read_csv(f)
# to pull columns and values
new = mydata[["A","Room","C","D"]]
new = new.rename(columns={'D': 'Qty. of Parts'})
new['Qty. of Parts'] = 1
new.to_csv(p ,index=False)
#to merge columns and values
merge_columns = ['A', 'Room', 'C']
merged_col = ''.join(merge_columns).replace('ARoomC', 'F')
new[merged_col] = new[merge_columns].apply(lambda x: '.'.join(x), axis=1)
new.drop(merge_columns, axis=1, inplace=True)
new = new.groupby(merged_col).count().reset_index()
new.to_csv(p, index=False)
CodePudding user response:
If you want to convert the strings to dates to later get the values
import datetime
# datetime.datetime(1900, 2, 1, 0, 0)
d = datetime.datetime.strptime("1-Feb", "%d-%b")
print(f'{d.month}-{d.day}')
result:
2-1
CodePudding user response:
You can use pandas.to_datetime
:
new["Room"]= (
pd.to_datetime(new["Room"], format="%d-%b", errors="coerce")
.dt.strftime("%#m-%#d")
.fillna(new["Room"])
)
Example :
col
0 1-Feb
1 2-Feb
2 3-Mar
3 2-1
Gives :
col
0 2-1
1 2-2
2 3-3
3 2-1
NB: In case you're facing errors, you need to give a minimal reproducible example to show how look like your original (.csv
) opened in a text editor and not in Excel.