请教一个关于合并多个不同行和列csv的问题

比如有如下3个csv1,csv2,csv3。有什么好的方法可以合并成第二张图的csv。
csv第一行包含了当前的csv文件名
感谢大家能够帮助我,谢谢!

Jason990420
最佳答案
from io import StringIO
import pandas as pd

csv1 = """
ID,one,two,three
A,1,4,7
B,2,5,8
C,3,6,9
""".strip()

csv2 = """
ID,one,two,three,four
A,1,6,11,16
E,2,7,12,17
F,3,8,13,18
G,4,9,14,19
H,5,10,15,20
""".strip()

csv3 = """
ID,one,two,ye,no,maybe
I,1,1,1,1,1
J,2,2,2,2,2
K,3,3,3,3,3
L,4,4,4,4,4
M,5,5,5,5,5
""".strip()

df1 = pd.read_csv(StringIO(csv1), sep=",", header=0).convert_dtypes()
df2 = pd.read_csv(StringIO(csv2), sep=",", header=0).convert_dtypes()
df3 = pd.read_csv(StringIO(csv3), sep=",", header=0).convert_dtypes()

how = 'outer'
df4 = pd.merge(df1, df2, how=how)
df4 = pd.merge(df4, df3, how=how)

print(df4)
   ID  one  two  three  four    ye    no  maybe
0   A    1    4      7  <NA>  <NA>  <NA>   <NA>
1   B    2    5      8  <NA>  <NA>  <NA>   <NA>
2   C    3    6      9  <NA>  <NA>  <NA>   <NA>
3   A    1    6     11    16  <NA>  <NA>   <NA>
4   E    2    7     12    17  <NA>  <NA>   <NA>
5   F    3    8     13    18  <NA>  <NA>   <NA>
6   G    4    9     14    19  <NA>  <NA>   <NA>
7   H    5   10     15    20  <NA>  <NA>   <NA>
8   I    1    1   <NA>  <NA>     1     1      1
9   J    2    2   <NA>  <NA>     2     2      2
10  K    3    3   <NA>  <NA>     3     3      3
11  L    4    4   <NA>  <NA>     4     4      4
12  M    5    5   <NA>  <NA>     5     5      5
result = df4.to_csv(na_rep="nan", index=False)
print("csv1,csv2,csv3\n"+result)
csv1,csv2,csv3
ID,one,two,three,four,ye,no,maybe
A,1,4,7,nan,nan,nan,nan
B,2,5,8,nan,nan,nan,nan
C,3,6,9,nan,nan,nan,nan
A,1,6,11,16,nan,nan,nan
E,2,7,12,17,nan,nan,nan
F,3,8,13,18,nan,nan,nan
G,4,9,14,19,nan,nan,nan
H,5,10,15,20,nan,nan,nan
I,1,1,nan,nan,1,1,1
J,2,2,nan,nan,2,2,2
K,3,3,nan,nan,3,3,3
L,4,4,nan,nan,4,4,4
M,5,5,nan,nan,5,5,5
7个月前 评论
jwwlchen (楼主) 6个月前
Jason990420 (作者) 6个月前
jwwlchen (楼主) 6个月前
讨论数量: 4
Jason990420
from io import StringIO
import pandas as pd

csv1 = """
ID,one,two,three
A,1,4,7
B,2,5,8
C,3,6,9
""".strip()

csv2 = """
ID,one,two,three,four
A,1,6,11,16
E,2,7,12,17
F,3,8,13,18
G,4,9,14,19
H,5,10,15,20
""".strip()

csv3 = """
ID,one,two,ye,no,maybe
I,1,1,1,1,1
J,2,2,2,2,2
K,3,3,3,3,3
L,4,4,4,4,4
M,5,5,5,5,5
""".strip()

df1 = pd.read_csv(StringIO(csv1), sep=",", header=0).convert_dtypes()
df2 = pd.read_csv(StringIO(csv2), sep=",", header=0).convert_dtypes()
df3 = pd.read_csv(StringIO(csv3), sep=",", header=0).convert_dtypes()

how = 'outer'
df4 = pd.merge(df1, df2, how=how)
df4 = pd.merge(df4, df3, how=how)

print(df4)
   ID  one  two  three  four    ye    no  maybe
0   A    1    4      7  <NA>  <NA>  <NA>   <NA>
1   B    2    5      8  <NA>  <NA>  <NA>   <NA>
2   C    3    6      9  <NA>  <NA>  <NA>   <NA>
3   A    1    6     11    16  <NA>  <NA>   <NA>
4   E    2    7     12    17  <NA>  <NA>   <NA>
5   F    3    8     13    18  <NA>  <NA>   <NA>
6   G    4    9     14    19  <NA>  <NA>   <NA>
7   H    5   10     15    20  <NA>  <NA>   <NA>
8   I    1    1   <NA>  <NA>     1     1      1
9   J    2    2   <NA>  <NA>     2     2      2
10  K    3    3   <NA>  <NA>     3     3      3
11  L    4    4   <NA>  <NA>     4     4      4
12  M    5    5   <NA>  <NA>     5     5      5
result = df4.to_csv(na_rep="nan", index=False)
print("csv1,csv2,csv3\n"+result)
csv1,csv2,csv3
ID,one,two,three,four,ye,no,maybe
A,1,4,7,nan,nan,nan,nan
B,2,5,8,nan,nan,nan,nan
C,3,6,9,nan,nan,nan,nan
A,1,6,11,16,nan,nan,nan
E,2,7,12,17,nan,nan,nan
F,3,8,13,18,nan,nan,nan
G,4,9,14,19,nan,nan,nan
H,5,10,15,20,nan,nan,nan
I,1,1,nan,nan,1,1,1
J,2,2,nan,nan,2,2,2
K,3,3,nan,nan,3,3,3
L,4,4,nan,nan,4,4,4
M,5,5,nan,nan,5,5,5
7个月前 评论
jwwlchen (楼主) 6个月前
Jason990420 (作者) 6个月前
jwwlchen (楼主) 6个月前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!