pandas读取Excel批量转换时间戳

1

pip install pandas

1

pip install xlrd

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

import pandas as pd

import time,datetime

file_path = r'C:\Users\Administrator\Desktop\携号转网测试\admin_log.xls'

df = pd.read_excel(file_path, sheet_name = "admin",header=None)

# print(df.values[1,0])

data=[]

for i in range(len(df)):

    timechuo = df.values[i,0]

    time_local = time.localtime(timechuo)

    dt = time.strftime("%Y-%m", time_local)

    data.append(dt)

result = pd.value_counts(data)

print(result)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

#将GPS时间戳数据批量转化为日期时间

import numpy as np

import pandas as pd

import time

#设置Spyder右侧console区的print输出行列数无限制

pd.set_option('display.max_columns', None)

pd.set_option('display.max_rows', None)

#读取数据

data=pd.read_excel('C:/Users/Administrator/Desktop/GPS时间.xlsx') #excel文件的路径，命名为GPS时间.xlsx

data.loc[:, 'localminute'] = data['gps_ts'].apply(lambda x :time.localtime(x)) #gps_ts为GPS时间.xlsx的列名，具体见图1

#转换的时间格式为"年-月-日 时:分:秒"

data.loc[:, 'time'] = data['localminute'].apply(lambda x :time.strftime("%Y-%m-%d %H:%M:%S", x))

print(data)