I have two or more dataframes in Pandas in a list
A Value1
1 ABC
2 HYT
3 IUF
A Value2
1 IJT
2 GFH
3 QER
When using
df = pd.concat(dfs, axis = 0)
And after writing to CSV, the final output is like this
A Value1 Value2
1 ABC
1 IJT
2 HYT
2 GFH
3 IUF
3 QER
But I'd like it to be like this
A Value1 Value2
1 ABC IJT
2 HYT GFH
3 IUF QER
Can someone point me in the right direction?
Two problems:
axis 0 (should be axis 1).'Value2' in df2.So:
df1, df2 = dfs
pd.concat((df1, df2['Value2']), axis=1)
returns
A Value1 Value2
0 1 ABC IJT
1 2 HYT GFH
2 3 IUF QER
Here is one way. The benefit of this method is it works for arbitrary lst, provided each dataframe has column 'A'.
lst = [df1, df2]
df = pd.concat([i.set_index('A') for i in lst], axis=1).reset_index()
# A Value1 Value2
# 0 1 ABC IJT
# 1 2 HYT GFH
# 2 3 IUF QER
You could achieve the desired result using pandas.merge or pandas.concat
import pandas as pd
df1 = pd.DataFrame({"A": [1, 2, 3],
"Value1": ['ABC', 'HYT', 'IUF']})
df2 = pd.DataFrame({"A": [1, 2, 3],
"Value2": ['IJT', 'GFH', 'QER']})
df_merge= pd.merge(df1, df2, how='left', on=["A"])
df_concat = pd.concat([df1, df2['Value2']], axis = 1)
print(df_merge)
print(df_concat)
Expected Output for pandas.merge:
A Value1 Value2
0 1 ABC IJT
1 2 HYT GFH
2 3 IUF QER
Expected Output for pandas.concat:
A Value1 Value2
0 1 ABC IJT
1 2 HYT GFH
2 3 IUF QER
