在python中创建两个csv文件的笛卡尔乘积(交叉联接) [英] Create the cartesian product (cross join) of two csv files in python

问题描述

我试图通过交叉连接两个现有的csv文件来创建一个新的csv文件.

I tried to create a new csv file by cross joining two existing csv files.

csv文件#1:

hour    Elevation   Azimuth x   y   z   sunx    suny    sunz
06:29:00    -0.833  67.72   0.379094033 0.925243946 -0.014538068    0.379094033 0.925243946 -0.014538068
07:00:00    6.28    68.75   0.360264063 0.92641472  0.109387255 0.360264063 0.92641472  0.109387255

csv文件#2:

ID  SURFACES    A1X A1Y A1Z A2X A2Y A2Z B1X B1Y B1Z B2X B2Y B2Z AX  AY  AZ  BX  BY  BZ  ABX ABY ABZ planex  planey  planez
1   GROUND  800085.3323 961271.977  -3.07E-18   800080.8795 961246.1978 -3.07E-18   800097.1572 961269.9344 -3.07E-18   800085.3323 961271.977  -3.07E-18   4.4528  25.7792 0.00E+00    11.8249 -2.0426 0.00E+00    0   0   -313.9317514    0   0   -1
2   ROOF    800019.3994 961242.7732 12  800021.442  961254.5981 12  800090.3488 961230.5181 12  800019.3994 961242.7732 12  -2.0426 -11.8249    0.00E+00    70.9494 -12.2551    0.00E+00    0   0   864.0018273 0

我想要文件的笛卡尔积(每个小时都有所有曲面，就像执行SQL交叉联接一样.)

I want the cartesian product of the files (each hour with all surfaces, just like performing a SQL cross join).

我要问的一个例子:
http://dotnetslackers.com/images/articleimages/sqljoins5.jpg

推荐答案

我不知道任何现成的解决方案，所以我做到了:

I don't know of any out-of-the-box solution, so I made this:

import csv
from itertools import product

def main():
    with open('file1.csv', 'rb') as f1, open('file2.csv', 'rb') as f2:
        reader1 = csv.reader(f1, dialect=csv.excel_tab)
        reader2 = csv.reader(f2, dialect=csv.excel_tab)

        # Step 1: Read and write the headers separately.
        header1, header2 = next(reader1), next(reader2)
        with open('output.csv', 'wb') as out:
            writer = csv.writer(out, dialect=csv.excel_tab)
            writer.writerow(header1 + header2)
            # Step 2: Write the product of the rest of the rows.
            writer.writerows(
                row1 + row2 for row1, row2 in product(reader1, reader2))

main()

---

带有文件:

---

With files:

file1.csv

file1.csv

hour    Elevation   Azimuth
06:29:00    -0.833  67.72
07:00:00    6.28    68.75

file2.csv

file2.csv

ID  SURFACES
1   GROUND
2   ROOF

您将获得以下 output.csv :

hour    Elevation   Azimuth ID  SURFACES
06:29:00    -0.833  67.72   1   GROUND
06:29:00    -0.833  67.72   2   ROOF
07:00:00    6.28    68.75   1   GROUND
07:00:00    6.28    68.75   2   ROOF

这篇关于在python中创建两个csv文件的笛卡尔乘积(交叉联接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！