1. 创建一个8行5列的DataFrame对象，列名为[‘Num’, ‘Name’,‘C++’, ‘Java’, ‘Python’]，分别代表学号，姓名，C++成绩，Java成绩，Python成绩。所有成绩要求分布在[50,100)区间，是随机生成的整数。（以下所述的行号和列号均代表行列索引的默认编号。请使用本章所学的知识按顺序解决这些问题，不要投机取巧！）
(1) 复制第4个学生的全部信息，把它作为新行追加到表尾；
(2) 将(3, 4)、(6, 3)、(5, 2)、(2, 3)、(1, 0)这几个位置上的元素置为NaN；
(3) 输出所有包含缺失值的行；
(4) 删除学号为缺失值的行；
(5) 将成绩列上的所有缺失值用其同列上的前一个和后一个成绩的平均值替换；
(6) 检测是否存在重复行；如果存在，保留最后一行，删除其余行；
(7) 重置行索引；
(8) 在2,3,4列上分别找到大于60的最小值，然后把这些值用55替换（如果某列上有多个最小值，则全部替换）；
(9) 输出Java成绩不及格的学生的信息（学号，姓名，Java成绩）；
(10) 输出“挂科”学生的全部信息。
(11) 自己人工仔细检查以上每一道小题的操作结果是否正确。如果确认无误，用DataFrame的to_excel方法将数据存储到“Scores.xlsx”文件中，用Excel打开并查看数据是否正确。如果以上都正确，最后输出 ：“我做对了！”
import pandas as pd
import numpy as np
import random
data = {'Num': ['1', '2', '3', '4', '5', '6', '7', '8'],
'Name': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'],
'C++': [random.randint(50, 99) for i in range(8)],
'Java': [random.randint(50, 99) for i in range(8)],
'Python': [random.randint(50, 99) for i in range(8)]}
df = pd.DataFrame(data)
df.loc[8] = pd.Series(df.iloc[3])
#q1 = df.iloc[3:4, :]
#df = df.append(q1, ignore_index=True, sort=False)
df.loc[3, 'Python'] = np.NaN
df.loc[6, 'Java'] = np.NaN
df.loc[5, 'C++'] = np.NaN
df.loc[2, 'Java'] = np.NaN
df.loc[1, 'Num'] = np.NaN
print(df[df.isnull().any(axis=1)])
df.dropna(subset=['Num'], inplace=True)
t1 = pd.DataFrame.copy(df, deep=True)
t2 = pd.DataFrame.copy(df, deep=True)
col = ['C++', 'Java', 'Python']
for i in col:
t1[i].fillna(method='ffill', axis=0, inplace=True)
t2[i].fillna(method='bfill', axis=0, inplace=True)
df[i] = (t1[i] + t2[i]) / 2
df.drop_duplicates(subset='Num', keep='last', inplace=True)
df.reset_index(drop=True, inplace=True)
f = lambda x: min(x) if min(x) > 60 else 0.1
for i in col:
df[i].replace(to_replace=f(df[i]), value=55.0, inplace=True)
print(df)
print(df[df['Java'].values < 60].iloc[:, 0:3])
print(df[(df['C++'] < 60) | (df['Java'] < 60) | (df['Python'] < 60)])
df.to_excel(r"D:\A学习\python科学计算\实验\3\data.xlsx", index=False)
print("我做对了！")
import numpy as np
import pandas as pd
scores = np.random.randint(50,100,size=[8,3])
names = ['张三','李四','王五','赵六','周大生','吴琼','郑海燕','钱思思']
nums = np.arange(2020005,2020013)
df_basic = pd.DataFrame({'学号':nums,'姓名':names},dtype='str')
df_grade = pd.DataFrame(scores,columns=['C++','Java','Python'])
df1 = pd.concat([df_basic,df_grade],axis=1)
ser1 =pd.Series(df1.iloc[4],index=df1.columns)
df1 = df1.append(ser1,ignore_index=False)
df1.iloc[3,4] = np.nan
df1.iloc[6,3] = np.nan
df1.iloc[5,2] = np.nan
df1.iloc[2,3] = np.nan
df1.iloc[1,0] = np.nan
print(df1[df1.isnull().any(axis=1)])
df1.dropna(how='any',subset=['学号'],inplace=True)
df1.iloc[:,2:] = (df1.iloc[:,2:].fillna(method='ffill') + df1.iloc[:,2:].fillna(method='bfill'))/2
df1.drop_duplicates(keep='last',inplace=True)
df1.reset_index(drop=True,inplace=True)
a = min(df1[df1['C++'] > 60]['C++'])
b = min(df1[df1['Java'] > 60]['Java'])
c = min(df1[df1['Python'] > 60]['Python'])
df1.replace({'C++':a, 'Java':b, 'Python':c},55,inplace=True)
print(df1.query('Java<60').loc[:,['学号','姓名','Java']])
print(df1[np.any(df1.iloc[:,2:] < 60,axis=1)])
df1.to_excel('Scores.xlsx',index=False)
2. 创建2个行、列大小均不相同的DataFrame对象（要求有部分行索引名和列索引名相同）。
(1) 手工计算按某个列索引（或列索引的组合）内连接、外连接、左连接和右连接的结果；然后通过编程验证计算结果的正确性。（用merge函数验证）
(2) 手工计算在0轴和1轴分别并联和交联的结果；然后通过编程验证计算结果的正确性。（用concat函数验证）
(3) 手工计算按行索引作为连接键左连接的结果；然后通过编程验证计算结果的正确性。（用join函数验证）
import pandas as pd
import numpy as np
df1 = pd.DataFrame({'key1': ['k1', 'k1', 'k2', 'k3'],
'key2': ['k1', 'k2', 'k1', 'k2'],
'A': ['a1', 'a2', 'a3', 'a4'],
'B': ['b1', 'b2', 'b3', 'b4']})
df2 = pd.DataFrame({'key1': ['k1', 'k2', 'k2'],
'key2': ['k1', 'k1', 'k1'],
'C': ['c1', 'c2', 'c3'], })
print(df1.merge(df2, on=['key1', 'key2']))
"""
key1 key2 A B C
0 k1 k1 a1 b1 c1
1 k2 k1 a3 b3 c2
2 k2 k1 a3 b3 c3
"""
print(df1.merge(df2, how='outer', on=['key1', 'key2']))
"""
key1 key2 A B C
0 k1 k1 a1 b1 c1
1 k1 k2 a2 b2 NaN
2 k2 k1 a3 b3 c2
3 k2 k1 a3 b3 c3
4 k3 k2 a4 b4 NaN
"""
print(df1.merge(df2, how='left', on=['key1', 'key2']))
"""
key1 key2 A B C
0 k1 k1 a1 b1 c1
1 k1 k2 a2 b2 NaN
2 k2 k1 a3 b3 c2
3 k2 k1 a3 b3 c3
4 k3 k2 a4 b4 NaN
"""
print(df1.merge(df2, how='right', on=['key1', 'key2']))
"""
key1 key2 A B C
0 k1 k1 a1 b1 c1
1 k2 k1 a3 b3 c2
2 k2 k1 a3 b3 c3
"""
print(pd.concat([df1, df2], ignore_index=True))
"""
key1 key2 A B C
0 k1 k1 a1 b1 NaN
1 k1 k2 a2 b2 NaN
2 k2 k1 a3 b3 NaN
3 k3 k2 a4 b4 NaN
4 k1 k1 NaN NaN c1
5 k2 k1 NaN NaN c2
6 k2 k1 NaN NaN c3
"""
print(pd.concat([df1, df2], axis=1))
"""
key1 key2 A B key1 key2 C
0 k1 k1 a1 b1 k1 k1 c1
1 k1 k2 a2 b2 k2 k1 c2
2 k2 k1 a3 b3 k2 k1 c3
3 k3 k2 a4 b4 NaN NaN NaN
"""
print(pd.concat([df1, df2], axis=0, join='inner', ignore_index=True))
""" 将列取交合并，ABC不重合，所以没有
key1 key2
0 k1 k1
1 k1 k2
2 k2 k1
3 k3 k2
4 k1 k1
5 k2 k1
6 k2 k1
"""
print(pd.concat([df1, df2], axis=1, join='inner'))
""" 只是将前三行合并
key1 key2 A B key1 key2 C
0 k1 k1 a1 b1 k1 k1 c1
1 k1 k2 a2 b2 k2 k1 c2
2 k2 k1 a3 b3 k2 k1 c3
"""
print(df1.join(df2.set_index(['key1','key2']), on=['key1','key2']))#默认left
"""
key1 key2 A B C
0 k1 k1 a1 b1 c1
1 k1 k2 a2 b2 NaN
2 k2 k1 a3 b3 c2
2 k2 k1 a3 b3 c3
3 k3 k2 a4 b4 NaN
"""
print(df1.join(df2,lsuffix='_1', rsuffix='_2'))#默认left
"""
key1_1 key2_1 A B key1_2 key2_2 C
0 k1 k1 a1 b1 k1 k1 c1
1 k1 k2 a2 b2 k2 k1 c2
2 k2 k1 a3 b3 k2 k1 c3
3 k3 k2 a4 b4 NaN NaN NaN
"""
