How split a column in two columns in pandas

Question

I have el next dataframe

data=read_csv('enero.csv')
data

           Fecha           DirViento  MagViento  
0   2011/07/01  00:00        318        6.6      
1   2011/07/01  00:15        342        5.5        
2   2011/07/01  00:30        329        6.6        
3   2011/07/01  00:45        279        7.5        
4   2011/07/01  01:00        318        6.0        
5   2011/07/01  01:15        329        7.1        
6   2011/07/01  01:30        300        4.7        
7   2011/07/01  01:45        291        3.1

How to split the column Fecha in two columns,for example, get a dataframe as follows:

      Fecha     Hora     DirViento  MagViento  
0   2011/07/01  00:00        318        6.6      
1   2011/07/01  00:15        342        5.5        
2   2011/07/01  00:30        329        6.6        
3   2011/07/01  00:45        279        7.5        
4   2011/07/01  01:00        318        6.0        
5   2011/07/01  01:15        329        7.1        
6   2011/07/01  01:30        300        4.7        
7   2011/07/01  01:45        291        3.1

I am using pandas for to read data

I try to calculate daily averages from a monthly database has daily data recorded every 15 minutes. To do this, use pandas and grouped the columns: Date and Time for get a dataframe as follow:

 Fecha Hora
 2011/07/01 00:00 -4.4
            00:15 -1.7
            00:30 -3.4
 2011/07/02 00:00 -4.5
            00:15 -4.2
            00:30 -7.6
 2011/07/03 00:00 -6.3
            00:15 -13.7
            00:30 -0.3

with this look, I get the following

grouped.mean()                                                                         

Fecha     DirRes
2011/07/01 -3 
2011/07/02 -5
2011/07/03 -6

Wouldn't it be better to have Fecha as an actual datetime object, e.g. passing parse_dates=['Fecha'] to read_csv. — Andy Hayden, Oct 25 '13 at 06:23
I agree with @AndyHayden you can pass to `read_csv` a `parse_dates` parameter and this will read the string and try to parse it as a datetime like so: `data=read_csv('enero.csv', parse_dates=['Fecha'])` — EdChum, Oct 25 '13 at 07:25

score 5 · Answer 1 · edited May 23 '17 at 12:01

Here is a link to the quite similar question that has been answered before, hope it is helpful. In your case, you can split the content in the Fecha by the space and construct a list of the second part of the string. Then add the content to the new column inserted

import pandas as p
t = p.read_csv('test2.csv')

#store into a data frame
df = p.DataFrame(t)


#update the fecha col value and create new col hora
lista = [item.split(' ')[2] for item in df['Fecha']]
listb = p.Series([item.split(' ')[0] for item in df['Fecha']])
df['Fecha'].update(listb)
df['Hora'] = lista

#change Hora position
#I am not sure whether this is efficient or not
#as I am also quite new to Pandas
col = df.columns.tolist()
col = col[-1:]+col[:-1]
col[0], col[1] = col[1], col[0]

df = df[col]

print df

Hope this could solve your problem and here is the output.

        Fecha   Hora  DirViento  MagViento
0  2011/07/01  00:00        318        6.6
1  2011/07/01  00:15        342        5.5
2  2011/07/01  00:30        329        6.6
3  2011/07/01  00:45        279        7.5
4  2011/07/01  01:00        318        6.0
5  2011/07/01  01:15        329        7.1
6  2011/07/01  01:30        300        4.7
7  2011/07/01  01:45        291        3.1

How split a column in two columns in pandas

1 Answers1