CODEWITHSUNDEEP
pythondataframemerge
joho
joho

Reputation: 35

Merge two data frames

I tried two merge two data frames by adding the first line of the second df to the first line of the first df. I also tried to concatenate them but eiter failed. The format of the Data is

1,3,N0128,Durchm.,5.0,0.1,5.0760000000000005,0.076,-----****--
2,0.000,,,,,,,
3,3,N0129,Position,62.2,0.376,62.238,0.136,***---
4,76.1,-36.000,0.300,-36.057,,,,
5,2,N0130,Durchm.,5.0,0.1,5.067,0.067,-----***---
6,0.000,,,,,,,

The expected format of the output should be

1,3,N0128,Durchm.,5.0,0.1,5.0760000000000005,0.076,-----****--,0.000,,,,,,,
2,3,N0129,Position,62.2,0.376,62.238,0.136,***---**,76.1,-36.000,0.300,-36.057,,,,
3,N0130,Durchm.,5.0,0.1,5.067,0.067,-----***---,0.000,,,,,,,

I already splitted the dataframe from above into two frames. The first one contains only the odd indexes and the second one the even one's. My problem is now, to merge/concatenate the two frames, by adding the first row of the second df to the first row of the first df. I already tried some methods of merging/concatenating but all of them failed. All the print functions are not neccessary, I only use them to have a quick overview in the console. The code which I felt most comfortable with is:

os.chdir(output)
csv_files = os.listdir('.')
for csv_file in (csv_files):
        if csv_file.endswith(".asc.csv"):
            df = pd.read_csv(csv_file)
            keep_col = ['Messpunkt', 'Zeichnungspunkt', 'Eigenschaft', 'Position', 'Sollmass','Toleranz','Abweichung','Lage']
            new_df = df[keep_col]
            new_df = new_df[~new_df['Messpunkt'].isin(['**Teil'])]
            new_df = new_df[~new_df['Messpunkt'].isin(['**KS-Oben'])]
            new_df = new_df[~new_df['Messpunkt'].isin(['**KS-Unten'])]
            new_df = new_df[~new_df['Messpunkt'].isin(['**N'])]
            print(new_df)   
            new_df.to_csv(output+csv_file)     
            
            df1 = new_df[new_df.index % 2 ==1]
            df2 = new_df[new_df.index % 2 ==0]
            df1.reset_index()
            df2.reset_index()
            print (df1)
            print (df2)
            merge_df = pd.concat([df1,df2], axis=1)
            print (merge_df)
            merge_df.to_csv(output+csv_file)

I highly appreciate some help.

With this code, the output is:

1,3,N0128,Durchm.,5.0,0.1,5.0760000000000005,0.076,-----****--,,,,,,,,
2,,,,,,,,,0.000,,,,,,,
3,3,N0129,Position,62.2,0.376,62.238,0.136,***---,,,,,,,,
4,,,,,,,,,76.1,-36.000,0.300,-36.057,,,,
5,2,N0130,Durchm.,5.0,0.1,5.067,0.067,-----***---,,,,,,,,
6,,,,,,,,,0.000,,,,,,,

Upvotes: 0

Views: 49

Answers (1)

furas
furas

Reputation: 142651

I get expected result when I use reset_index() to have the same index in both DataFrames.

It may need also drop=True to skip index as new column

pd.concat([df1.reset_index(drop=True), df2.reset_index(drop=True)], axis=1)

Minimal working example.

I use io only to simulate file in memory.

text = '''1,3,N0128,Durchm.,5.0,0.1,5.0760000000000005,0.076,-----****--
2,0.000,,,,,,,
3,3,N0129,Position,62.2,0.376,62.238,0.136,***---
4,76.1,-36.000,0.300,-36.057,,,,
5,2,N0130,Durchm.,5.0,0.1,5.067,0.067,-----***---
6,0.000,,,,,,,'''

import pandas as pd
import io

pd.options.display.max_columns = 20  # to display all columns

df = pd.read_csv(io.StringIO(text), header=None, index_col=0)

#print(df)

df1 = df[df.index % 2 == 1] # .reset_index(drop=True)
df2 = df[df.index % 2 == 0] # .reset_index(drop=True)

#print(df1)
#print(df2)

merge_df = pd.concat([df1.reset_index(drop=True), df2.reset_index(drop=True)], axis=1)

print(merge_df)

Result:

     1      2         3     4      5       6      7            8     1        2      3       4   5   6   7    8
0  3.0  N0128   Durchm.   5.0  0.100   5.076  0.076  -----****--   0.0      NaN    NaN     NaN NaN NaN NaN  NaN
1  3.0  N0129  Position  62.2  0.376  62.238  0.136       ***---  76.1  -36.000  0.300 -36.057 NaN NaN NaN  NaN
2  2.0  N0130   Durchm.   5.0  0.100   5.067  0.067  -----***---   0.0      NaN    NaN     NaN NaN NaN NaN  NaN

EDIT:

It may need

merge_df.index = merge_df.index + 1

to correct index.

Upvotes: 1

Related Questions