pandas>数据整合与修改

Question

https://docs.google.com/spreadsheets/d/1-NGRvRPirlbN0WAMFJwLbTfyowVXGaA2LnIcRHSGkhY/edit?usp=sharing

不幸的是，我不知道如何简单地上传数据。所以我把excel文件上传到了我的google电子表格上。有两个电子表格(选项卡)

1. current
2. future

在“当前”页上，我想保留“6”列。(参考，“6”栏不得删除)在“Ref#”栏上，需要用上述值填充空缺(请参阅所附的图像文件)--我不知道如何创建“未来”数据

​

​

Answer 1

#add two new result columns and shift them
df["refb"]=df["Ref#"].shift(1)
df["refc"]=df["Ref#"].shift(2)

#empty unwanted values
#in Pandas the ~ before a statement means the inverse of the statement

#so this mean "select rows that NOT contains the word "STANDARD" and fill the #refb columns with nan value => pd.np.nan is a sorcut for np.nan

df.loc[~df.refb.str.contains("STANDARD",na=False) ,"refb"]=pd.np.nan

#this mean "select rows that NOT starts by A" and fill the refc columns with #nan values
df.loc[~df.refc.str.contains("^A",na=False) ,"refc"]=pd.np.nan

#fill with wanted values
df.refb=df.refb.ffill()
df.refc=df.refc.ffill()

#select columns and row and rename columns as you expect
df=df.loc[df["6"].notnull(),["SMD_Nr",'refc','refb',"6"]]
df.columns=["SMD_Nr","Part#","Description","6"]

  SMD_Nr        Part#                              Description       6
6   A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C1011
7   A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C1091
8   A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C1121
9   A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C1261
10  A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C1311
11  A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C3252
12  A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C3302
13  A3C0282740191  A2C01561600   C CER,C0G,100pF,1%,50V,0402,-,STANDARD   C3332
16  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1041
17  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1061
18  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1071
19  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1161
20  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1180
21  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1191
22  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1201
23  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1221
24  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C1241
25  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD  C1251A
26  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2001
27  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2011
28  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2061
29  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2141
30  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2151
31  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2161
32  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2181
33  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2261
34  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2271
35  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2281
36  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2291
37  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C2361
38  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C4204
39  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C4207
40  A3C0282740191  A2C02317600   C CER,X7R,47nF,10%,16V,0402,-,STANDARD   C7603
43  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C1070
44  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C1130
45  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C1140
46  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C1240
47  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD  C1250A
48  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2000
49  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2010
50  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2090
51  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2140
52  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2150
53  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2160
54  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2180
55  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2260
56  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2270
57  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2280
58  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C2360
59  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C3251
60  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4014
61  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4068
62  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4072
63  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4080
64  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4082
65  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4083
66  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C4084
67  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C6005
68  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C6009
69  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C6016
70  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C6017
71  A3C0282740191  A2C02103100  C CER,X7R,10nF,10%,100V,0603,-,STANDARD   C6035