玩转数据处理120题｜Pandas&R

data = {"grammer":["Python","C","Java","GO",np.nan,"SQL","PHP","Python"],
       "score":[1,2,np.nan,4,5,6,7,10]}

import numpy as np
import pandas as pd
df = pd.DataFrame(data)

# 假如是直接创建
df = pd.DataFrame({
    "grammer": ["Python","C","Java","GO",np.nan,"SQL","PHP","Python"],
    "score": [1,2,np.nan,4,5,6,7,10]})

# R中没有字典概念，故直接创建dataframe/tibble
#> 第一种
df <- data.frame(
  "grammer" = c("Python","C","Java","GO",NA,"SQL","PHP","Python"),
  "score" = c(1,2,NA,4,5,6,7,10)
)
#> 第二种
library(tibble)
df <- tibble(
  "grammer" = c("Python","C","Java","GO",NA,"SQL","PHP","Python"),
  "score" = c(1,2,NA,4,5,6,7,10)
)
# 也可以用tribble横向建tibble

grammer  score
0 Python    1.0
7 Python   10.0

#> 1
df[df['grammer'] == 'Python']
#> 2
results = df['grammer'].str.contains("Python")
results.fillna(value=False,inplace = True)
df[results]

df[which(df$grammer == 'Python'),]

Index(['grammer', 'score'], dtype='object')

df.columns

names(df)
# [1] "grammer" "score"

df.rename(columns={'score':'popularity'}, inplace = True)

df <- df %>%
  rename(popularity = score)

df['grammer'].value_counts()

# 神方法table
table(df$grammer)

# pandas里有一个插值方法，就是计算缺失值上下两数的均值
df['popularity'] = df['popularity'].fillna(df['popularity'].interpolate())

library(Hmisc)
index <- which(is.na(df$popularity))
df$popularity <- impute(df$popularity,
                       (unlist(df[index-1, 2] +
                               df[index+1, 2]))/2)

df[df['popularity'] > 3]

df %>%
  filter(popularity > 3)
# 等价于
df[df$popularity > 3,] # 这种方法跟pandas很相似

df.drop_duplicates(['grammer'])

df[!duplicated(df$grammer),]

df['popularity'].mean()
# 4.75

#> 第一种
mean(df$popularity)
# [1] 4.75

#> 第二种
df %>%
  summarise(mean = mean(popularity))
## A tibble: 1 x 1
# mean
# <dbl>
# 1 4.75

df['grammer'].to_list()
# ['Python', 'C', 'Java', 'GO', nan, 'SQL', 'PHP', 'Python']

unlist(df$grammer)
# [1] "Python" "C" "Java" "GO" NA "SQL" "PHP" "Python"

df.to_excel('filename.xlsx')

#R对EXCEL文件不太友好
#第一种方法：利用readr包转为csv再用EXCEL打开
#文件本质依然是csv
library(readr)
write_excel_csv(df,'filename.csv')

#第二种方法：利用openxlsx包
openxlsx::write.xlsx(df,'filename.xlsx')

#也可以用xlsx包，但需要先配置JAVA环境
#确保JAVA配置到环境变量中并命名为JAVA_HOME
Sys.getenv("JAVA_HOME")
install.packages('rJava')
install.packages("xlsxjars")
library(rJava)
library(xlsxjars)
xlsx::write.xlsx(df,'filename.xlsx')

df.shape
# (8, 2)

dim(df)
# [1] 8 2

df[(df['popularity'] > 3) & (df['popularity'] < 7)]

library(dplyr)
df %>%
  filter(popularity > 3 & popularity <7)
# 等价于
df[(df$popularity > 3) & (df$popularity <7),]

temp = df['popularity']
df.drop(labels=['popularity'], axis=1,inplace = True)
df.insert(0, 'popularity', temp)

df <- df %>%
    select(popularity,everything())

df[df['popularity'] == df['popularity'].max()]

df %>%
  filter(popularity == max(popularity))
# 同理也有类似pandas的方法
df[df$popularity == max(df$popularity),]

df.tail()

# R中head和tail默认是6行，可以指定数字
tail(df,5)

df = df.drop(labels=df.shape[0]-1)

df[-dim(df)[1],]
# 等价于
df %>%
  filter(rownames(df) != max(rownames(df)))

row = {'grammer':'Perl','popularity':6.6}
df = df.append(row,ignore_index=True)

row <- c(6.6,'Perl') # 需要和列的位置对应
# 或者建数据框
row <- data.frame(
  "grammer" = c("Perl"),
  "popularity" = c(6.6)
)

df <- rbind(df,row)

df.sort_values("popularity",inplace=True)

df <- df %>%
  arrange(popularity)

df['grammer'] = df['grammer'].fillna('R')
df['len_str'] = df['grammer'].map(lambda x: len(x))

library(Hmisc)
library(stringr)
df$grammer <- impute(df$grammer,'R')
str_length(df$grammer)

df$len_str <- str_length(df$grammer)

import pandas as pd
import numpy as np
df = pd.read_excel(r'C:\Users\chenx\Documents\Data Analysis\pandas120.xlsx')

#R语言处理excel不友好，直接读取日期时间数据会变成实数
#openxlsx::read.xlsx中的detectDates参数只能识别纯日期
#as.Data转换该列后时间数据丢失，只有日期
#故先把excel文件转存为csv后用readr包读取
# 该方法不理想
library(openxlsx)
df <- read.xlsx('pandas120.xlsx',detectDates = T)
df$createTime <- as.Date(df$createTime,origin="1900-01-01")
# 转存csv后再读
library(readr)
df <- read_csv('pandas120.csv')

df.head()

# 默认是6行，可指定行数
head(df,5)

# 方法一：apply + 自定义函数
def func(df):
    lst = df['salary'].split('-')
    smin = int(lst[0].strip('k'))
    smax = int(lst[1].strip('k'))
    df['salary'] = int((smin + smax) / 2 * 1000)
    return df

df = df.apply(func,axis=1)
# 方法二：iterrows + 正则
import re
for index,row in df.iterrows():
    nums = re.findall('\d+',row[2])
    df.iloc[index,2] = int(eval(f'({nums[0]} + {nums[1]}) / 2 * 1000'))

library(stringr)
df$salary <- df$salary %>%
  str_replace_all('k','') %>%
  str_split('-',simplify = T) %>%
  apply(2,as.numeric) %>%
  rowMeans() * 1000

education salary            
不限 19600.000000
大专 10000.000000
本科 19361.344538
硕士 20642.857143

df.groupby('education').mean()

df %>%
  group_by(education) %>%
  summarise(mean = mean(salary))

for index,row in df.iterrows():
   df.iloc[index,0] = df.iloc[index,0].to_pydatetime().strftime("%m-%d")

#转化后该列属性是 字符串，R中对时间格式要求严格
df$createTime <- as.Date(df$createTime) %>%
  str_replace('2020-','')

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 135 entries, 0 to 134
Data columns (total 4 columns):
createTime 135 non-null object
education 135 non-null object
salary 135 non-null int64
categories 135 non-null category
dtypes: category(1), int64(1), object(2)
memory usage: 3.5+ KB

df.info()

str(df)

# 内存查看需要用到其他的库
library(pryr)
object_size(df)
# 6.66 kB

df.describe()

summary(df)

bins = [0,5000, 20000, 50000]
group_names = ['低', '中', '高']
df['categories'] = pd.cut(df['salary'], bins, labels=group_names)

#用ifelse也可以
#底层原理有差别但实现结果一样
df <- df %>%
  mutate(categories = case_when(
    salary >= 0 & salary < 5000 ~ '低',
    salary >= 5000 & salary < 20000 ~ '低',
    TRUE ~ '高'
  ))

df.sort_values('salary', ascending=False)

df %>%
  arrange(desc(salary))

df.iloc[32]

df[33,]

np.median(df['salary'])
# 17500.0

median(df$salary)
# [1] 17500

# Jupyter运行matplotlib成像需要运行魔术命令
%matplotlib inline
plt.rcParams['font.sans-serif'] = ['SimHei'] # 解决中文乱码
plt.rcParams['axes.unicode_minus'] = False # 解决符号问题
import matplotlib.pyplot as plt
plt.hist(df.salary)

# 也可以用原生pandas方法绘图
df.salary.plot(kind='hist')

library(ggplot2)
library(patchwork)

df %>%
  ggplot(aes(salary)) +
  geom_histogram() +
  df %>%
  ggplot(aes(salary)) +
  geom_histogram(bins = 10) # 这个跟python的bins一致

df.salary.plot(kind='kde',xlim = (0,70000))

df %>%
  ggplot(aes(salary)) +
  geom_density() +
  xlim(c(0,70000))

del df['categories']
# 等价于
df.drop(columns=['categories'], inplace=True)

df <- df[,-4]
# 提高可读性可采用如下代码
df <- df %>%
  select(-c('categories'))

df['test'] = df['education'] + df['createTime']

df <- df %>%
  mutate(test = paste0(df$education,df$createTime))

df["test1"] = df["salary"].map(str) + df['education']

df <- df %>%
  mutate(test1 =
           paste0(df$salary,df$education))

df[['salary']].apply(lambda x: x.max() - x.min())
# salary 41500
# dtype: int64

df %>%
  summarise(delta = max(salary) - min(salary)) %>%
  unlist()
# delta
# 41500

pd.concat([df[1:2], df[-1:]])

rbind(df[1,],df[dim(df)[1],])

df.append(df.iloc[7])

rbind(df,df[8,])

createTime object
education object
salary int64
test object
test1 object
dtype: object

df.dtypes
# createTime object
# education object
# salary int64
# test object
# test1 object
# dtype: object

str(df)
# tibble [135 x 5] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
# $ createTime: chr [1:135] "03-16" "03-16" "03-16" "03-16" ...
# $ education : chr [1:135] "本科" "本科" "不限" "本科" ...
# $ salary : num [1:135] 27500 30000 27500 16500 15000 14000 23000 12500 7000 16000 ...
# $ test : chr [1:135] "本科03-16" "本科03-16" "不限03-16" "本科03-16" ...
# $ test1 : chr [1:135] "27500本科" "30000本科" "27500不限" "16500本科" ...

df.set_index("createTime")

df %>%
  tibble::column_to_rownames('createTime')

df1 = pd.DataFrame(pd.Series(np.random.randint(1, 10, 135)))

df1 <- sapply(135,function(n) {
  replicate(n,sample(1:10,1))
})
# 列名暂时不一样，下一题重命名

df= pd.concat([df,df1],axis=1)

df <- cbind(df,df1) %>%
  rename(`0` = df1)
# 非常规命名需要用``包裹变量名

df["new"] = df["salary"] - df[0]

df <- df %>%
  mutate(new = salary - `0`)

df.isnull().values.any()
# False

# 这个包的结果呈现非常有趣
library(mice)
md.pattern(df)

df['salary'].astype(np.float64)

as.double(df2$salary)

len(df[df['salary'] > 10000])
# 119

df %>%
  filter(salary > 10000) %>%
  dim(.) %>%
  .[1]

本科 119
硕士 7
不限 5
大专 4
Name: education, dtype: int64

df.education.value_counts()

table(df$education)

df['education'].nunique()
# 4

length(unique(df$education))
# [1] 4

rowsums = df[['salary','new']].apply(np.sum, axis=1)
res = df.iloc[np.where(rowsums > 60000)[0][-3:], :]

df[df$salary + df$new > 60000,] %>%
  .[nrow(.)-3+1:nrow(.),] %>%
  na.omit(.)

import pandas as pd
import numpy as np

df = pd.read_excel(r'C:\Users\chenx\Documents\Data Analysis\Pandas51-80.xls')

# 转存csv后再读
library(readr)
df <- read_csv('C:/Users/chenx/Documents/Data Analysis/Pandas51-80.csv')

df.head(3)

head(df,3)

代码 1
简称 2
日期 2
前收盘价(元) 2
开盘价(元) 2
最高价(元) 2
最低价(元) 2
收盘价(元) 2
成交量(股) 2
成交金额(元) 2
.................

df.isnull().sum()

colSums(is.na(df))

df[df['日期'].isnull()]

df[is.na(df$日期),]

列名："代码", 第[327]行位置有缺失值
列名："简称", 第[327, 328]行位置有缺失值
列名："日期", 第[327, 328]行位置有缺失值
列名："前收盘价(元)", 第[327, 328]行位置有缺失值
列名："开盘价(元)", 第[327, 328]行位置有缺失值
列名："最高价(元)", 第[327, 328]行位置有缺失值
列名："最低价(元)", 第[327, 328]行位置有缺失值
列名："收盘价(元)", 第[327, 328]行位置有缺失值
................

for i in df.columns:
    if df[i].count() != len(df):
        row = df[i][df[i].isnull().values].index.tolist()
        print('列名："{}", 第{}行位置有缺失值'.format(i,row))

library(glue)

for (i in names(df)){
  if(sum(is.na(df[,'日期'])) != 0){
    res1 <- which(is.na(df[,i]))
    res2 <- paste(res1,collapse = ',')
    print(glue('列名："{i}", 第[{res2}]行有缺失值'))
  }
}

df.dropna(axis=0, how='any', inplace=True)

df <- na.omit(df)

axis：0-行操作（默认），1-列操作
how：any-只要有空值就删除（默认），all-全部为空值才删除
inplace：False-返回新的数据集（默认），True-在原数据集上操作

# Jupyter运行matplotlib
%matplotlib inline

df['收盘价(元)'].plot()
# 等价于
import matplotlib.pyplot as plt
plt.plot(df['收盘价(元)'])

library(ggplot2)

df %>%
  ggplot(aes(日期,`收盘价(元)`)) +
  geom_line()

plt.rcParams['font.sans-serif'] = ['SimHei'] # 解决中文乱码
plt.rcParams['axes.unicode_minus'] = False # 解决符号问题

df[['收盘价(元)','开盘价(元)']].plot()

df %>%
  ggplot() +
  geom_line(aes(日期,`收盘价(元)`), size=1.2, color='steelblue') +
  geom_line(aes(日期,`开盘价(元)`), size=1.2, color='orange') +
  ylab(c('价格(元)'))
# 这种画出来没有图例，当然可以手动添加，但为了映射方便可以用另一种方法

library(tidyr)

df %>%
  select(日期,`开盘价(元)`,`收盘价(元)`) %>%
  pivot_longer(c(`开盘价(元)`,`收盘价(元)`),
               names_to='type',values_to='price') %>%
  ggplot(aes(日期,price,color=type)) +
  geom_line(size=1.2) +
  scale_color_manual(values=c('steelblue','orange')) +
  theme_bw() +
  theme(
    panel.grid.major = element_blank(),
    panel.grid.minor = element_blank(),
    legend.title = element_blank(),
    legend.position = c(0.86, 0.9)
  )

plt.hist(df['涨跌幅(%)'])
# 等价于
df['涨跌幅(%)'].hist()

df %>%
  ggplot(aes(`涨跌幅(%)`)) +
  geom_histogram()
# 可以指定bins

df['涨跌幅(%)'].hist(bins = 30)

df %>%
  ggplot(aes(`涨跌幅(%)`)) +
  geom_histogram(bins=30)

temp = pd.DataFrame(columns = df.columns.to_list())

temp <- as_tibble(names(df))

for index,row in df.iterrows():
    if type(row[13]) != float:
        temp = temp.append(df.loc[index])

#换手率这一列属性为chr，需要先强转数值型
#如果转换失败会变成NA，判断即可
df[is.na(as.numeric(df$`换手率(%)`)),]

df[df['换手率(%)'] == '--']

df %>%
  filter(`换手率(%)` == '--')

df = df.reset_index(drop=True)

rownames(df) <- NULL

# 如果是tibble则索引始终是按顺序

lst = []
for index,row in df.iterrows():
    if type(row[13]) != float:
        lst.append(index)
df.drop(labels=lst,inplace=True)

df[!is.na(as.numeric(df$`换手率(%)`)),]
# 或者根据前几题的经验，非数字就是'--'
df <- df %>%
  filter(`换手率(%)` != '--')

df['换手率(%)'].plot(kind='kde',xlim=(0,0.6))

df$`换手率(%)` <- as.double(df$`换手率(%)`)
ggplot(df) +
  geom_density(aes(`换手率(%)`))

df['收盘价(元)'].diff()

df %>%
  summarise(delta = `收盘价(元)` - lag(`收盘价(元)`))

data['收盘价(元)'].pct_change()

df %>%
  summarise(pct_change = (`收盘价(元)` - lag(`收盘价(元)`))/lag(`收盘价(元)`))

df.set_index('日期')

df %>%
  column_to_rownames(var='日期')

df['收盘价(元)'].rolling(5).mean()

library(RcppRoll)
df %>%
  transmute(avg_5 = roll_mean(`收盘价(元)`,n = 5,align="right",fill = NA))

df['收盘价(元)'].rolling(5).sum()

df %>%
  transmute(sum_5 = roll_sum(`收盘价(元)`,n = 5,align="right",fill = NA))

df['收盘价(元)'].plot()
df['收盘价(元)'].rolling(5).mean().plot()
df['收盘价(元)'].rolling(20).mean().plot()

df %>%
  mutate(avg_5 = roll_mean(`收盘价(元)`,n = 5,align="right",fill = NA),
         avg_20 = roll_mean(`收盘价(元)`,n = 20,align="right",fill = NA)) %>%
  ggplot() +
  geom_line(aes(日期,`收盘价(元)`),color = 'steelblue',size = 1.2) +
  geom_line(aes(日期,avg_5),color = 'orange',size = 1.2) +
  geom_line(aes(日期,avg_20),color = 'green',size = 1.2)

df = df.set_index('日期')
df['收盘价(元)'].resample('W').max()

library(plyr)

res <- dlply(df,.(cut(日期,"1 week")),"[")
res_max <- sapply(res,function(n)max(n$`收盘价(元)`),simplify=TRUE)
as.data.frame(res_max)

df['收盘价(元)'].plot()
df['收盘价(元)'].resample('7D').max().plot()

res %>%
  rownames_to_column('date')
res$date <- as.Date(res$date)

ggplot(df) +
  geom_line(aes(日期,`收盘价(元)`),color = 'steelblue',size = 1.2) +
  geom_line(data = res, aes(date,res_max),
            color = 'orange',size = 1.2)

df.shift(5)

lag(df,5)

df.shift(-5)

lead(df,5)

df['开盘价(元)'].expanding(min_periods=1).mean()

#R中没有expanding完全一致的函数
#考虑到expanding实际功能就是累积均值
#可以用cummean
#但cummean的功能和我预想的不同
#可能是包之间相互干扰
#最后采用cumsum/1:n的形式完成本题

res <- df %>%
  transmute(cummean = cumsum(`开盘价(元)`)/1:dim(df)[1])

df['expanding Open mean']=df['开盘价(元)'].expanding(min_periods=1).mean()
df[['开盘价(元)', 'expanding Open mean']].plot(figsize=(16, 6))

library(tidyr)
df %>%
  cbind(res) %>%
  dplyr::rename(Opening_Price = `开盘价(元)`,
         Expanding_Open_Mean = cummean) %>%
  select(日期,Opening_Price,Expanding_Open_Mean) %>%
  pivot_longer(c(Opening_Price,Expanding_Open_Mean),
               names_to = 'type',
               values_to ='price') %>%
  ggplot(aes(日期,price,color = type)) +
  geom_line(size=1.2) +
  scale_color_manual(values=c('orange','steelblue')) +
  theme_bw() +
  theme(
    panel.grid.major = element_blank(),
    panel.grid.minor = element_blank(),
    legend.title = element_blank(),
    legend.position = c(0.9, 0.9)
  )

df['former 30 days rolling Close mean']=df['收盘价(元)'].rolling(20).mean()
df['upper bound']=df['former 30 days rolling Close mean']+2*df['收盘价(元)'].rolling(20).std()
df['lower bound']=df['former 30 days rolling Close mean']-2*df['收盘价(元)'].rolling(20).std()

df <- df %>%
  mutate(avg_20 = roll_mean(`收盘价(元)`,n = 20,align="right",fill = NA),
         upper_bound = avg_20 + 2 * roll_sd(`收盘价(元)`,n = 20,align="right",fill = NA),
         lower_bound = avg_20 - 2 * roll_sd(`收盘价(元)`,n = 20,align="right",fill = NA))

df[['收盘价(元)', 'former 30 days rolling Close mean','upper bound','lower bound' ]].plot(figsize=(16, 6))

df %>%
  dplyr::rename(former_30_days_rolling_Close_mean = avg_20,
                Closing_Price = `收盘价(元)`) %>%
  select(日期,Closing_Price,
           former_30_days_rolling_Close_mean,upper_bound,lower_bound) %>%
  pivot_longer(c(Closing_Price,former_30_days_rolling_Close_mean,upper_bound,lower_bound),
               names_to = 'type',
               values_to ='price') %>%
  ggplot(aes(日期,price,color = type)) +
  geom_line(size=1.2) +
  scale_color_manual(values=c('steelblue','orange','red','green')) +
  theme_bw() +
  theme(
    panel.grid.major = element_blank(),
    panel.grid.minor = element_blank(),
    legend.title = element_blank(),
    legend.position = c(0.6, 0.2)
  )

import pandas as pd
import numpy as np
print(np.__version__)
# 1.16.5
print(pd.__version__)
# 0.25.1

packageVersion("tidyverse")
# [1] ‘1.3.0’
packageVersion("dplyr")
# [1] ‘0.8.99.9002’

tem = np.random.randint(1,100,20)
df1 = pd.DataFrame(tem)

df1 <- sapply(20,function(n) {
  replicate(n,sample(1:100,1))
}) %>%
  as.data.frame(.) %>%
  dplyr::rename(`0` = V1)

tem = np.arange(0,100,5)
df2 = pd.DataFrame(tem)

df2 <- as.data.frame(seq(0,99,5)) %>%
  dplyr::rename(`0` = "seq(0, 99, 5)")

tem = np.random.normal(0, 1, 20)
df3 = pd.DataFrame(tem)

df3 <- as.data.frame(rnorm(20,0,1)) %>%
  dplyr::rename(`0` = "rnorm(20, 0, 1)")

df = pd.concat([df1,df2,df3],axis=0,ignore_index=True)

df <- rbind(df1,df2,df3)

0 1 2
0 95 0 0.022492
1 22 5 -1.209494
2 3 10 0.876127
3 21 15 -0.162149
4 51 20 -0.815424
5 30 25 -0.303792
...............

df = pd.concat([df1,df2,df3],axis=1,ignore_index=True)

df <- cbind(df1,df2,df3)
names(df) <- c(0,1,2)

np.percentile(df, q=[0, 25, 50, 75, 100])

summary(unlist(df))

df.columns = ['col1','col2','col3']

df <- df %>%
  dplyr::rename(col1 = 1,
                col2 = 2,
                col3 = 3)
# 或者用类似pandas的方法
names(df) <- c('col1','col2','col3')

df['col1'][~df['col1'].isin(df['col2'])]

df[!(df$col1 %in% df$col2),1]

temp = df['col1'].append(df['col2'])
temp.value_counts()[:3]

count(unlist(c(df$col1,df$col2))) %>%
  arrange(desc(freq)) %>%
  filter(row_number() <= 3)

np.argwhere(df['col1'] % 5==0)

which(df['col1'] %% 5==0)

df['col1'].diff().tolist()

df %>%
  summarise(col1 - lag(col1)) %>%
  na.omit(.) # 不去NA也可以，pandas没有去除

df.iloc[:, ::-1]

df %>%
  select(col3,col2,everything())

df['col1'].take([1,10,15])
# 等价于
df.iloc[[1,10,15],0]

df[c(1,10,15) + 1,1]

res = np.diff(np.sign(np.diff(df['col1'])))
np.where(res== -2)[0] + 1
# array([ 2, 4, 7, 9, 12, 15], dtype=int64)

res1 <- which((df$col1 - lag(df$col1) > 0))
res2 <- which((df$col1 - lead(df$col1) > 0))

intersect(res1,res2)
# [1] 3 5 7 12 14 17 19

# 另一种方法，类似pandas的用符号判断

res <- sign(df$col1 - lag(df$col1))

which(res - lag(res) == -2) - 1
# # [1] 3 5 7 12 14 17 19

df[['col1','col2','col3']].mean(axis=1)

rowMeans(df)

np.convolve(df['col2'], np.ones(3)/3, mode='valid')

library(RcppRoll)

df %>%
  summarise(avg_3 = roll_mean(col2, n=3))

df.sort_values("col3",inplace=True)

df <- df %>%
  arrange(col3)

df.col1[df['col1'] > 50] = '高'

df[df$col1 > 50,1] <- '高'

np.linalg.norm(df['col1']-df['col2'])
# 194.29873905921264

# 可以利用概念计算
res <- (df$col1 - df$col2) ^ 2
sqrt(sum(res))
# [1] 197.0102

# 也可以利用dist函数，但需要形成两个不同的观测
dist(rbind(df$col1,df$col2))
# 1
# 2 197.0102

df1 = pd.read_csv(r'C:\Users\chenx\Documents\Data Analysis\数据1.csv',encoding='gbk', usecols=['positionName', 'salary'],nrows = 10)

#一步读取文件的指定列用readr包或者原生函数都没办法
#如果文件特别大又不想全部再选指定列可以用如下办法
#基本思想先读取较少的数据获取列名
#给目标列以外的列打上NULL导致第二次读取文件时NULL列丢失即可

res <- read.csv('数据1.csv',encoding = 'GBK',nrows = 3)
classes <- sapply(res, class)
classes[-match(c('positionName','salary'),names(classes))] <-
  rep('NULL', length(classes) - 2)

df <- read.csv('数据1.csv',encoding = 'GBK',nrows = 10,
               colClasses = classes)

df2 = pd.read_csv(r'C:\Users\chenx\Documents\Data Analysis\数据2.csv',
                  converters={'薪资水平': lambda x: '高' if float(x) > 10000 else '低'} )

library(readr)

df2 <- read_csv('数据2.csv') %>%
  mutate('学历要求',
         '薪资水平' = ifelse(
           薪资水平 > 10000,'高','低'))

df2.iloc[::20, :][['薪资水平']]

df2[seq(1,dim(df2)[1],20),]

df = pd.DataFrame(np.random.random(10)**10, columns=['data'])

df = pd.DataFrame(np.random.random(10)**10, columns=['data'])
df.round(3)

df <- tibble(data = runif(10)^10)
round(df,3)

df.style.format({'data': '{0:.2%}'.format})

tibble(data = str_glue('{round(df$data * 100,2)}%'))

df['data'].argsort()[len(df)-3]

df %>%
  mutate(nrow = rownames(.)) %>%
  arrange(desc(data)) %>%
  filter(row_number() == 3) %>%
  select(nrow)

df.iloc[::-1, :]

df %>%
  arrange(desc(rownames(.)))

df1= pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
'key2': ['K0', 'K1', 'K0', 'K1'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})

df2= pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
'key2': ['K0', 'K0', 'K0', 'K0'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})

pd.merge(df1, df2, on=['key1', 'key2'])

df1 <- data.frame(
  "key1" = c("K0","K0","K1","K2"),
  "key2" = c("K0","K1","K0","K1"),
  "A" = paste0('A',0:3),
  "B" = paste0('B',0:3)
)

df2 <- data.frame(
  "key1" = c("K0","K1","K1","K2"),
  "key2" = paste0('K',rep(0,4)),
  "C" = paste0('C',0:3),
  "D" = paste0('D',0:3)
)

full_join(df1,df2,by = c('key1','key2')) %>%
  na.omit(.)

pd.merge(df1, df2, how='left', on=['key1', 'key2'])

left_join(df1,df2,by = c('key1','key2'))

df = pd.read_csv(r'C:\Users\chenx\Documents\Data Analysis\数据1.csv',encoding='gbk')
pd.set_option("display.max.columns", None)

df <- read_csv('数据1.csv', locale = locale(encoding = "GBK")) %>%
  print(width = Inf)

np.where(df.secondType == df.thirdType)

df %>%
  mutate(nrow = rownames(.)) %>%
  filter(secondType == thirdType) %>%
  select(nrow) %>%
  unlist()

np.argwhere(df['salary'] > df['salary'].mean())[2]
# array([5], dtype=int64)

df %>%
  mutate(nrow = rownames(.)) %>%
  filter(salary > mean(salary)) %>%
  select(nrow) %>%
  filter(row_number() == 3)
# # A tibble: 1 x 1
# nrow
# <chr>
# 1 6

df[['salary']].apply(np.sqrt)

df %>%
  summarise(salary_sqrt = sqrt(salary))

df['split'] = df['linestaion'].str.split('_')

df <- df %>%
  mutate(split = str_split(linestaion,'_'))

df.shape[1]
# 54

length(df)
# [1] 54

df[df['industryField'].str.startswith('数据')]

df[grep("^数据", df$industryField),]

pd.pivot_table(df,values=["salary","score"],index="positionId")

df <- df %>%
  group_by(positionId) %>%
  dplyr::summarise(salary = mean(salary),
            score = mean(score)) %>%
  as.data.frame(.)
rownames(df) <- NULL
tibble::column_to_rownames(df,var='positionId')

df[["salary","score"]].agg([np.sum,np.mean,np.min])

res <- df %>%
  select(salary,score) %>%
  pivot_longer(c(salary,score),names_to = 'type',values_to = 'value') %>%
  group_by(type) %>%
  summarise(sum = sum(value),mean = mean(value),min = min(value))

rownames(res) <- NULL

res %>%
  column_to_rownames('type') %>%
  t(.)

df.agg({"salary":np.sum,"score":np.mean})

df %>%
  summarise(salary_sum = sum(salary),
            score_mean = mean(score))

df[['district','salary']].groupby(by='district').mean().sort_values(
    'salary',ascending=False).head(1)

df %>%
  group_by(district) %>%
  summarise(avg = mean(salary)) %>%
  arrange(desc(avg)) %>%
  filter(row_number() == 1)