1. 按日期排序
df211['rq']=pd.to_datetime(df211.rq)
df211=df211.sort_values(['rq']).reset_index(drop=True)
1)df = df.sort_values(by='date') 应该也行
2)以上操作后'rq'会变成timestamp类型,转换为datetime类型:
ts=(list(df211['rq'])[0]).date()
ts=(list(df211['rq'])[0]).to_pydatetime()
此外,获取某一列的日期范围并排序:
def change_date(s):
s = datetime.datetime.strptime(s, "%Y-%m-%d") # 把日期标准化,转化结果如:2015/1/4 => 2015-01-04 00:00:00
s = str(s) # 上一步把date转化为了时间格式,因此要把date转回str格式
return s[:10] # 只获取年月日,即“位置10”之前的字符串
data = list(df_0328['rq'].unique())
data=list(map(change_date,data) )
print(type(data))
data.sort(key=lambda date: datetime.datetime.strptime(date, "%Y-%m-%d"))
2.去掉特定值行列
df211 = df21.drop(df21[df21['road_name']!='汇新家园'].index).reset_index(drop=True)
3.统计列中各种值出现次数
df2['road_name'].value_counts()
4.处理一张表内嵌的多张表&处理多级表头
1)
xl = pd.ExcelFile('路区模型.xlsx',engine='openpyxl')
sheet_names = xl.sheet_names # 所有的sheet名称
print(sheet_names)
2)方法很多,没有找到最好的
如有二级表头,则:
df0512 = pd.read_excel('路区模型.xlsx',engine='openpyxl',\
sheet_name='表2',header=[0,1])
5.取出某一列中的数值/去掉非数值项
使用pd.to_numeric
b_=[x for x in (list(df21[('SF', 'B端揽收单量')])) if not np.isnan(pd.to_numeric(x, errors='coerce'))]
6.去某一列字符型前十个字符
df_deliver['date'] = df_deliver['create_time'].str[:10]
7.去除日期中小时
df_deliver['hour'] = pd.to_datetime(df_deliver['time']).apply(lambda x:x.hour)
8.坐标转换
def GCJ2WGS(lat,lon):
# location格式如下:locations[1] = "113.923745,22.530824"
a = 6378245.0 # 克拉索夫斯基椭球参数长半轴a
ee = 0.00669342162296594323 #克拉索夫斯基椭球参数第一偏心率平方
PI = 3.14159265358979324 # 圆周率
# 以下为转换公式
x = lon - 105.0
y = lat - 35.0
dLon = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * np.sqrt(abs(x));
dLon += (20.0 * np.sin(6.0 * x * PI) + 20.0 * np.sin(2.0 * x * PI)) * 2.0 / 3.0;
dLon += (20.0 * np.sin(x * PI) + 40.0 * np.sin(x / 3.0 * PI)) * 2.0 / 3.0;
dLon += (150.0 * np.sin(x / 12.0 * PI) + 300.0 * np.sin(x / 30.0 * PI)) * 2.0 / 3.0;
#纬度
dLat = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * np.sqrt(abs(x));
dLat += (20.0 * np.sin(6.0 * x * PI) + 20.0 * np.sin(2.0 * x * PI)) * 2.0 / 3.0;
dLat += (20.0 * np.sin(y * PI) + 40.0 * np.sin(y / 3.0 * PI)) * 2.0 / 3.0;
dLat += (160.0 * np.sin(y / 12.0 * PI) + 320 * np.sin(y * PI / 30.0)) * 2.0 / 3.0;
radLat = lat / 180.0 * PI
magic = np.sin(radLat)
magic = 1 - ee * magic * magic
sqrtMagic = np.sqrt(magic)
dLat = (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * PI);
dLon = (dLon * 180.0) / (a / sqrtMagic * np.cos(radLat) * PI);
wgsLon = lon - dLon
wgsLat = lat - dLat
return wgsLat,wgsLon
lat = list(df_1['lat'])
lon=list(df_1['lon'])
data=list(map(GCJ2WGS,lat,lon) )