Styjun

How to draw a gif with expanding circles that reveal lines connecting a non-centered point to the expanding circle using Tikz

Doing research in academia and not liking competition

Extract an attribute value from XML

Back to the nineties!

Is this more than a packing puzzle?

Why do they not say "The Baby"

How can I legally visit the United States Minor Outlying Islands in the Pacific?

Are L-functions uniquely determined by their values at negative integers?

How are "soeben" and "eben" different from one another?

Adding a vertical line at the right end of the horizontal line in frac

How would someone destroy a black hole that’s at the centre of a planet?

Is a public company able to check out who owns its shares in very detailed format?

Should you avoid redundant information after dialogue?

Crab Nebula short story from 1960s or '70s

Could the crash sites of the Apollo 11 and 16 LMs be seen by the LRO?

Can a continent naturally split into two distant parts within a week?

Absconding a company after 1st day of joining

Why hasn't the U.S. government paid war reparations to any country it attacked?

What caused Windows ME's terrible reputation?

Can I capture stereo IQ signals from WebSDR?

Replacing URI when using dynamic hosts in Nginx reverse proxy

As a DM, how to avoid unconscious metagaming when dealing with a high AC character?

Does ability to impeach an expert witness on science or scholarship go too far?

What is the closed form of the following recursive function?

How to generate a unix time column from “year”,“date”,“month”,“time” columns, preferrably using pandas module for python

Does pandas iterrows have performance issues?How to get file creation & modification date/times in Python?How to get the current time in PythonHow can I make a time delay in Python?How to remove an element from a list by index?How to remove a key from a Python dictionary?Adding new column to existing DataFrame in Python pandasDelete column from pandas DataFrameSelect rows from a DataFrame based on values in a column in pandasGet list from pandas DataFrame column headersModify Pandas dataframe to list year month and date

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

There is an excel file logging a set of data. Its columns are as below, where each column is seperated by comma.

SampleData
year,date,month,location,time,count
2019,20,Jan,Japan,22:33,1
2019,31,Jan,Japan,19:21,1
2019,1,Jan,Japan,8:00,1
2019,4,Jan,Japan,4:28,2
2019,13,Feb,Japan,6:19,1

From this data, I would like to create python pandas dataframe, which looks like below.

DataFrame
u_datetime,location,count
1547991180,Japan,1
1548930060,Japan,1
1546297200,Japan,1
1546543680,Japan,2
1550006340,Japan,1

One of the DataFrame methods can be useful for this operation, but it does not take date with one digit.

pandas.to_datetime(
 DataFrame["year"].astype(str)
 + DataFrame["month"].astype(str) 
 + DataFrame["date"].astype(str)
 + DataFrame["time"].astype(str),
 format="%Y%b%d%-H%M"
)

Could anybody give me a hand?

Thank you.

python pandas

share|improve this question

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

asked Mar 26 at 6:31

wisywisy

611 bronze badge

add a comment | 

1

There is an excel file logging a set of data. Its columns are as below, where each column is seperated by comma.

SampleData
year,date,month,location,time,count
2019,20,Jan,Japan,22:33,1
2019,31,Jan,Japan,19:21,1
2019,1,Jan,Japan,8:00,1
2019,4,Jan,Japan,4:28,2
2019,13,Feb,Japan,6:19,1

From this data, I would like to create python pandas dataframe, which looks like below.

DataFrame
u_datetime,location,count
1547991180,Japan,1
1548930060,Japan,1
1546297200,Japan,1
1546543680,Japan,2
1550006340,Japan,1

One of the DataFrame methods can be useful for this operation, but it does not take date with one digit.

pandas.to_datetime(
 DataFrame["year"].astype(str)
 + DataFrame["month"].astype(str) 
 + DataFrame["date"].astype(str)
 + DataFrame["time"].astype(str),
 format="%Y%b%d%-H%M"
)

Could anybody give me a hand?

Thank you.

python pandas

share|improve this question

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

asked Mar 26 at 6:31

wisywisy

611 bronze badge

add a comment | 

There is an excel file logging a set of data. Its columns are as below, where each column is seperated by comma.

SampleData
year,date,month,location,time,count
2019,20,Jan,Japan,22:33,1
2019,31,Jan,Japan,19:21,1
2019,1,Jan,Japan,8:00,1
2019,4,Jan,Japan,4:28,2
2019,13,Feb,Japan,6:19,1

From this data, I would like to create python pandas dataframe, which looks like below.

DataFrame
u_datetime,location,count
1547991180,Japan,1
1548930060,Japan,1
1546297200,Japan,1
1546543680,Japan,2
1550006340,Japan,1

One of the DataFrame methods can be useful for this operation, but it does not take date with one digit.

pandas.to_datetime(
 DataFrame["year"].astype(str)
 + DataFrame["month"].astype(str) 
 + DataFrame["date"].astype(str)
 + DataFrame["time"].astype(str),
 format="%Y%b%d%-H%M"
)

Could anybody give me a hand?

Thank you.

python pandas

share|improve this question

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

asked Mar 26 at 6:31

wisywisy

611 bronze badge

SampleData
year,date,month,location,time,count
2019,20,Jan,Japan,22:33,1
2019,31,Jan,Japan,19:21,1
2019,1,Jan,Japan,8:00,1
2019,4,Jan,Japan,4:28,2
2019,13,Feb,Japan,6:19,1

DataFrame
u_datetime,location,count
1547991180,Japan,1
1548930060,Japan,1
1546297200,Japan,1
1546543680,Japan,2
1550006340,Japan,1

pandas.to_datetime(
 DataFrame["year"].astype(str)
 + DataFrame["month"].astype(str) 
 + DataFrame["date"].astype(str)
 + DataFrame["time"].astype(str),
 format="%Y%b%d%-H%M"
)

share|improve this question

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

asked Mar 26 at 6:31

wisywisy

611 bronze badge

share|improve this question

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

asked Mar 26 at 6:31

wisywisy

611 bronze badge

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

edited Mar 26 at 7:00

Erik Cederstrand

4,03933 gold badges2323 silver badges4848 bronze badges

asked Mar 26 at 6:31

wisywisy

611 bronze badge

asked Mar 26 at 6:31

wisywisy

611 bronze badge

3 Answers
3

active

oldest

votes

1

try this

from datetime import datetime

data['datetime'] = data[['year','date','month','time']].apply(lambda x: datetime.strptime(str(x['year'])+'-'+str(x['date'])+'-'+str(x['month'])+' '+str(x['time']), "%Y-%d-%b %H:%M").timestamp(), axis=1)

data[['datetime','location','count']]

Output
​

 datetime 
0 1548003780.0 
1 1548942660.0 
2 1546309800.0 
3 1546556280.0 
4 1550018940.0 

 location 
0 Japan 
1 Japan 
2 Japan 
3 Japan 
4 Japan 

 count 
0 1 
1 1 
2 1 
3 2 
4 1 

share|improve this answer

edited Mar 26 at 7:18

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, your solution working, but OP need pandas solution, not python. Also apply are loops under the hood, so better not use if exist vectorized solutions, here function pd.to_datetime. link
  
  – jezrael
  Mar 26 at 7:22
  
  

  
  
  
  

add a comment | 

1

In case you are working with csv file this can be done easily using parse_dates.

dateparse = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')
df = pd.read_csv('/home/users/user/xxx.csv', parse_dates ='date_time':[0,1,2,4])
df['u_datetime'] = df['date_time'].values.astype(np.int64) // 10 ** 9
df_new = df[['u_datetime', 'location', 'count']]

share|improve this answer

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

add a comment | 

0

You are close, need %Y%b%d%H:%M format and then convert to unix time by cast to int64 with integer division by 10**9:

s = (DataFrame["year"].astype(str)+
 DataFrame["month"].astype(str)+
 DataFrame["date"].astype(str)+
 DataFrame["time"].astype(str))
DataFrame['u_datetime'] = pd.to_datetime(s, format="%Y%b%d%H:%M").astype(np.int64) // 10**9

DataFrame = DataFrame[['u_datetime','location','count']]
print (DataFrame)
 u_datetime location count
0 1548023580 Japan 1
1 1548962460 Japan 1
2 1546329600 Japan 1
3 1546576080 Japan 2
4 1550038740 Japan 1

share|improve this answer

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55351039%2fhow-to-generate-a-unix-time-column-from-year-date-month-time-columns-pr%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

try this

from datetime import datetime

data['datetime'] = data[['year','date','month','time']].apply(lambda x: datetime.strptime(str(x['year'])+'-'+str(x['date'])+'-'+str(x['month'])+' '+str(x['time']), "%Y-%d-%b %H:%M").timestamp(), axis=1)

data[['datetime','location','count']]

Output
​

 datetime 
0 1548003780.0 
1 1548942660.0 
2 1546309800.0 
3 1546556280.0 
4 1550018940.0 

 location 
0 Japan 
1 Japan 
2 Japan 
3 Japan 
4 Japan 

 count 
0 1 
1 1 
2 1 
3 2 
4 1 

share|improve this answer

edited Mar 26 at 7:18

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, your solution working, but OP need pandas solution, not python. Also apply are loops under the hood, so better not use if exist vectorized solutions, here function pd.to_datetime. link
  
  – jezrael
  Mar 26 at 7:22
  
  

  
  
  
  

add a comment | 

1

try this

from datetime import datetime

data['datetime'] = data[['year','date','month','time']].apply(lambda x: datetime.strptime(str(x['year'])+'-'+str(x['date'])+'-'+str(x['month'])+' '+str(x['time']), "%Y-%d-%b %H:%M").timestamp(), axis=1)

data[['datetime','location','count']]

Output
​

 datetime 
0 1548003780.0 
1 1548942660.0 
2 1546309800.0 
3 1546556280.0 
4 1550018940.0 

 location 
0 Japan 
1 Japan 
2 Japan 
3 Japan 
4 Japan 

 count 
0 1 
1 1 
2 1 
3 2 
4 1 

share|improve this answer

edited Mar 26 at 7:18

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, your solution working, but OP need pandas solution, not python. Also apply are loops under the hood, so better not use if exist vectorized solutions, here function pd.to_datetime. link
  
  – jezrael
  Mar 26 at 7:22
  
  

  
  
  
  

add a comment | 

try this

from datetime import datetime

data['datetime'] = data[['year','date','month','time']].apply(lambda x: datetime.strptime(str(x['year'])+'-'+str(x['date'])+'-'+str(x['month'])+' '+str(x['time']), "%Y-%d-%b %H:%M").timestamp(), axis=1)

data[['datetime','location','count']]

Output
​

 datetime 
0 1548003780.0 
1 1548942660.0 
2 1546309800.0 
3 1546556280.0 
4 1550018940.0 

 location 
0 Japan 
1 Japan 
2 Japan 
3 Japan 
4 Japan 

 count 
0 1 
1 1 
2 1 
3 2 
4 1 

share|improve this answer

edited Mar 26 at 7:18

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

from datetime import datetime

data['datetime'] = data[['year','date','month','time']].apply(lambda x: datetime.strptime(str(x['year'])+'-'+str(x['date'])+'-'+str(x['month'])+' '+str(x['time']), "%Y-%d-%b %H:%M").timestamp(), axis=1)

data[['datetime','location','count']]

 datetime 
0 1548003780.0 
1 1548942660.0 
2 1546309800.0 
3 1546556280.0 
4 1550018940.0 

 location 
0 Japan 
1 Japan 
2 Japan 
3 Japan 
4 Japan 

 count 
0 1 
1 1 
2 1 
3 2 
4 1 

share|improve this answer

edited Mar 26 at 7:18

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

answered Mar 26 at 6:46

iamklausiamklaus

2,37611 gold badge66 silver badges1818 bronze badges

1

In case you are working with csv file this can be done easily using parse_dates.

dateparse = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')
df = pd.read_csv('/home/users/user/xxx.csv', parse_dates ='date_time':[0,1,2,4])
df['u_datetime'] = df['date_time'].values.astype(np.int64) // 10 ** 9
df_new = df[['u_datetime', 'location', 'count']]

share|improve this answer

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

add a comment | 

1

In case you are working with csv file this can be done easily using parse_dates.

dateparse = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')
df = pd.read_csv('/home/users/user/xxx.csv', parse_dates ='date_time':[0,1,2,4])
df['u_datetime'] = df['date_time'].values.astype(np.int64) // 10 ** 9
df_new = df[['u_datetime', 'location', 'count']]

share|improve this answer

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

add a comment | 

In case you are working with csv file this can be done easily using parse_dates.

dateparse = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')
df = pd.read_csv('/home/users/user/xxx.csv', parse_dates ='date_time':[0,1,2,4])
df['u_datetime'] = df['date_time'].values.astype(np.int64) // 10 ** 9
df_new = df[['u_datetime', 'location', 'count']]

share|improve this answer

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

dateparse = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')
df = pd.read_csv('/home/users/user/xxx.csv', parse_dates ='date_time':[0,1,2,4])
df['u_datetime'] = df['date_time'].values.astype(np.int64) // 10 ** 9
df_new = df[['u_datetime', 'location', 'count']]

share|improve this answer

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

answered Mar 26 at 8:20

LoochieLoochie

1,00633 silver badges1111 bronze badges

0

You are close, need %Y%b%d%H:%M format and then convert to unix time by cast to int64 with integer division by 10**9:

s = (DataFrame["year"].astype(str)+
 DataFrame["month"].astype(str)+
 DataFrame["date"].astype(str)+
 DataFrame["time"].astype(str))
DataFrame['u_datetime'] = pd.to_datetime(s, format="%Y%b%d%H:%M").astype(np.int64) // 10**9

DataFrame = DataFrame[['u_datetime','location','count']]
print (DataFrame)
 u_datetime location count
0 1548023580 Japan 1
1 1548962460 Japan 1
2 1546329600 Japan 1
3 1546576080 Japan 2
4 1550038740 Japan 1

share|improve this answer

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges

add a comment | 

0

You are close, need %Y%b%d%H:%M format and then convert to unix time by cast to int64 with integer division by 10**9:

s = (DataFrame["year"].astype(str)+
 DataFrame["month"].astype(str)+
 DataFrame["date"].astype(str)+
 DataFrame["time"].astype(str))
DataFrame['u_datetime'] = pd.to_datetime(s, format="%Y%b%d%H:%M").astype(np.int64) // 10**9

DataFrame = DataFrame[['u_datetime','location','count']]
print (DataFrame)
 u_datetime location count
0 1548023580 Japan 1
1 1548962460 Japan 1
2 1546329600 Japan 1
3 1546576080 Japan 2
4 1550038740 Japan 1

share|improve this answer

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges

add a comment | 

You are close, need %Y%b%d%H:%M format and then convert to unix time by cast to int64 with integer division by 10**9:

s = (DataFrame["year"].astype(str)+
 DataFrame["month"].astype(str)+
 DataFrame["date"].astype(str)+
 DataFrame["time"].astype(str))
DataFrame['u_datetime'] = pd.to_datetime(s, format="%Y%b%d%H:%M").astype(np.int64) // 10**9

DataFrame = DataFrame[['u_datetime','location','count']]
print (DataFrame)
 u_datetime location count
0 1548023580 Japan 1
1 1548962460 Japan 1
2 1546329600 Japan 1
3 1546576080 Japan 2
4 1550038740 Japan 1

share|improve this answer

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges

s = (DataFrame["year"].astype(str)+
 DataFrame["month"].astype(str)+
 DataFrame["date"].astype(str)+
 DataFrame["time"].astype(str))
DataFrame['u_datetime'] = pd.to_datetime(s, format="%Y%b%d%H:%M").astype(np.int64) // 10**9

DataFrame = DataFrame[['u_datetime','location','count']]
print (DataFrame)
 u_datetime location count
0 1548023580 Japan 1
1 1548962460 Japan 1
2 1546329600 Japan 1
3 1546576080 Japan 2
4 1550038740 Japan 1

share|improve this answer

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges

answered Mar 26 at 6:50

jezraeljezrael

388k2828 gold badges391391 silver badges467467 bronze badges