如何在Python中比较两个Excel文件中的列？

huangapple
go评论99阅读模式
英文:

how to compare column between two excel in python?

问题

我有两个Excel表格

Excel 1:

A,B,C

1,2,3

Excel 2:

A,C,B

1,3,2

如何根据Excel 1的列来重新排列Excel 2？

使A,C,B变为A,B,C

我使用以下代码来检查列的顺序：

  1. comparison_Columns = pd.read_excel(xls).columns == pd.read_excel(xls2).columns
  2. if all(comparison_Columns):
  3.     pass
  4. else:
  5.     print('列顺序错误！！！！')
英文:

I have two excel

Excel 1 :

A,B,C

1,2,3

Excel 2 :

A,C,B

1,3,2

How can i re position the excel 2 base on excel 1 column ?

so that A,C,B and become A,B,C

I use the following code to check column orders:

  1. comparison_Columns = pd.read_excel(xls).columns == pd.read_excel(xls2).columns
  2. if all(comparison_Columns):
  3.     pass
  4. else:
  5.     print('Wrong column order !!!!! ')

答案1

得分: 1

  1. df1 = pd.read_excel(xls)
  2. df2 = pd.read_excel(xls2)
  4. if all(df1.columns == df2.columns):
  5.     pass
  6. else:
  7.     df1 = df1[df2.columns]
英文: 
  1. df1 = pd.read_excel(xls)
  2. df2 = pd.read_excel(xls2)
  4. if all(df1.columns == df2.columns):
  5.     pass
  6. else:
  7.     df1 = df1[df2.columns]

答案2

得分: 0

不管数据来自Excel还是其他格式，只要您知道它们的列顺序相同，您可以这样做：

  1. import pandas as pd
  2. df0 = pd.DataFrame([[1,2,3]], columns=["A","B","C"])
  3. df1 = pd.DataFrame([[1,3,2]], columns=["A","C","B"])
  5. print(df1[df0.columns])
  7.    A  B  C
  8. 0  1  2  3
英文:

It doesn't really matter if the data comes from excel or other format. If you know that both have the same columns up to order you could just

  1. import pandas as pd
  2. df0 = pd.DataFrame([[1,2,3]], columns=["A","B","C"])
  3. df1 = pd.DataFrame([[1,3,2]], columns=["A","C","B"])
  5. print(df1[df0.columns])
  7.    A  B  C
  8. 0  1  2  3

答案3

得分: 0

以下是翻译好的代码部分：

  1. 这段代码片段将正常工作：
  4.     def areColumnSame(df1, df2, checkTypes = True):
  5.         if checkTypes:
  6.             type1 = dict(df1.dtypes)
  7.             type2 = dict(df2.dtypes)
  8.             return type1 == type2
  9.         
  10.         else:
  11.             col1 = list(df1.columns)
  12.             col2 = list(df2.columns)
  13.             col1.sort()
  14.             col2.sort()
  15.             return col1 == col2
  18. 为了展示上面的代码如何工作，让我们探讨一些示例：
  20. 考虑三个 Excel 文件：
  21.    
  23.     | A | B | C |
  24.     |---|---|---|
  25.     | 1 | 2 | 3 |
  26.     | 4 | 5 | 6 |
  27.     
  28.     | A | C | B |
  29.     |---|---|---|
  30.     | 1 | 3 | 2 |
  31.     | 4 | 6 | 5 |
  32.     
  33.     | A | B | C | A.1 | B.1 | C.1 |
  34.     |---|---|---|-----|-----|-----|
  35.     | 1 | 2 | 3 | 1   | 2   | 3   |
  36.     | 4 | 5 | 6 | 4   | 5   | 6   |
  38. 现在，对于第一个文件，`dict(df.dtypes)` 如下所示：
  40.     {'A': dtype('int64'),
  41.      'B': dtype('int64'),
  42.      'C': dtype('int64')}
  44. 类似地，对于其他两个文件：
  46.     {'A': dtype('int64'),
  47.      'C': dtype('int64'),
  48.      'B': dtype('int64')}
  50. 和 
  52.     {'A': dtype('int64'),
  53.      'B': dtype('int64'),
  54.      'C': dtype('int64'),
  55.      'A.1': dtype('int64'),
  56.      'B.1': dtype('int64'),
  57.      'C.1': dtype('int64')}
  60. 我们只需要比较这些字典来获得结果。同时，它还检查数据的类型。
  62. 因此，前两个文件之间的比较将为真，而与第三个文件的比较将返回假。
  64. 但您始终可以禁用类型检查，在这种情况下，我们只会检查`[A, B, C]`是否与`[A, C, B]`相同，而不会比较它们的类型。
  66. <details>
  67. <summary>英文:</summary>
  69. This code snippet will work fine:
  72.     def areColumnSame(df1, df2, checkTypes = True):
  73.         if checkTypes:
  74.             type1 = dict(df1.dtypes)
  75.             type2 = dict(df2.dtypes)
  76.             return type1 == type2
  77.         
  78.         else:
  79.             col1 = list(df1.columns)
  80.             col2 = list(df2.columns)
  81.             col1.sort()
  82.             col2.sort()
  83.             return col1 == col2
  86. To show how the above code works let us explore examples:
  88. Consider three excel files:
  89.    
  91.     | A | B | C |
  92.     |---|---|---|
  93.     | 1 | 2 | 3 |
  94.     | 4 | 5 | 6 |
  95.     
  96.     | A | C | B |
  97.     |---|---|---|
  98.     | 1 | 3 | 2 |
  99.     | 4 | 6 | 5 |
  100.     
  101.     | A | B | C | A.1 | B.1 | C.1 |
  102.     |---|---|---|-----|-----|-----|
  103.     | 1 | 2 | 3 | 1   | 2   | 3   |
  104.     | 4 | 5 | 6 | 4   | 5   | 6   |
  106. Now for the first file the `dict(df.dtypes)` is shown below:
  108.     {&#39;A&#39;: dtype(&#39;int64&#39;),
  109.      &#39;B&#39;: dtype(&#39;int64&#39;),
  110.      &#39;C&#39;: dtype(&#39;int64&#39;)}
  112. Similarly for other two files:
  114.     {&#39;A&#39;: dtype(&#39;int64&#39;),
  115.      &#39;C&#39;: dtype(&#39;int64&#39;),
  116.      &#39;B&#39;: dtype(&#39;int64&#39;)}
  118. and 
  120.     {&#39;A&#39;: dtype(&#39;int64&#39;),
  121.      &#39;B&#39;: dtype(&#39;int64&#39;),
  122.      &#39;C&#39;: dtype(&#39;int64&#39;),
  123.      &#39;A.1&#39;: dtype(&#39;int64&#39;),
  124.      &#39;B.1&#39;: dtype(&#39;int64&#39;),
  125.      &#39;C.1&#39;: dtype(&#39;int64&#39;)}
  128.  We just need to compare these dictionaries to get the result. At the same time, it also checks for the type of data.
  130. Hence for the comparison between the first two files will be true whereas the comparison with third will return false.
  132. But you can always disable the type-checking in which case we will just check whether `[A, B, C]` is the same as `[A, C, B]` without comparing their types.
  135. </details>

huangapple
  • 本文由 发表于 2020年1月6日 22:57:39
  • 转载请务必保留本文链接：https://go.coder-hub.com/59614275.html
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定