0

I have a excel dump which has 50+ columns with either dates or blank cells. When I read these excels in python they appear as dates with time stamp. Thus I tried to split every cell and convert into a string using a for loop - 

for all in df2:
    df2['all'] = df2['all'].astype(str).str[0:10]

however this is not working - any suggestions how to go about.

DavidG
  • 24,279
  • 14
  • 89
  • 82
Pigel J
  • 3
  • 6
  • u posted a suggestion - sf2 = sf2.apply(lambda x: x.astype(str).str[:10]) - this works perfect however for loop is not working well as it is copying split values into another columns. thanks! – Pigel J Apr 25 '18 at 10:16

2 Answers2

0

You're looping through your columns using the all variable you define, but within your loop you use all within quotes - making it a string, thus it would only act on the column named 'all'.

for all in df2:
    df2[all] = df2[all].astype(str).str[0:10]
BML91
  • 2,952
  • 3
  • 32
  • 54
-1

pandas has a pd.to_datetime function where you can specify the output format dateData=pd.to_datetime(df2, format = '%Y%m%d') may work. and if you want to convert to string you can use strftime

there is a similar question here

p.deman
  • 584
  • 2
  • 10
  • 24
  • well i get the error - 'DatetimeProperties' object has no attribute 'stfrtime' – Pigel J Apr 25 '18 at 10:15
  • what I tried: `test = pandas.read_excel("path\Test.xls") truc=pandas.to_datetime(test["nameColumn1"],format="%Y%M%D") bidule=truc.dt.strftime(date_format="%Y-%m-%d")` it works in my case >>> type(test) >> type(truc) >>> type(bidule[0]) – p.deman Apr 25 '18 at 11:16
  • to do all columns in one time: `df1 = pd.read_excel("file.xls") truc=df1.values test=pd.to_datetime(numpy.squeeze(truc[:])) resultat = test.strftime(data_format="%Y-%m-%d")` – p.deman Apr 25 '18 at 11:38