now there are two tables (txt files). You need to change one column value of one file to the corresponding column value of the other file, as follows:
File 1
2
I need to reassign the value of file 1"s x and y column according to the column name t and userid to get the corresponding x, y value from file 2. As the use of Pandas is not familiar with, hope to know the guidance, thank you!
import pandas as pd
import numpy as np
df1 = pd.DataFrame([[0, 1, 2, 3], [4, 5, 6, 7]], columns=['t', 'user_id', 'x', 'y'])
>>> df1
t user_id x y
0 0 1 2 3
1 4 5 6 7
df2 = pd.DataFrame([[0, 1, 0, 0], [4, 5, 0, 0], [5, 6, 7, 8]], columns=['t', 'user_id', 'x', 'y'])
>>> df2
t user_id x y
0 0 1 0 0
1 4 5 0 0
2 5 6 7 8
def f(row):
if np.isnan(row['x_y']):
row['x'], row['y'] = row['x_x'], row['y_x']
else:
row['x'], row['y'] = row['x_y'], row['y_y']
return row
res = pd.merge(df2, df1, how='left', on=['t', 'user_id']).apply(f, axis=1)[['t', 'user_id', 'x', 'y']]
>>> res
t user_id x y
0 0.0 1.0 2.0 3.0
1 4.0 5.0 6.0 7.0
2 5.0 6.0 7.0 8.0to improve performance, you should reduce the line-by-line operation of pandas.DataFrame.apply () . In this case, you can use the numpy.where () binary operator, as follows
In [1]: import pandas as pd
In [2]: import numpy as np
In [3]: df1 = pd.DataFrame({'t': [1,2,3], 'user_id': [10,20,30], 'v': [1.1,2.2,3.3]})
In [4]: df1
Out[4]:
t user_id v
0 1 10 1.1
1 2 20 2.2
2 3 30 3.3
In [5]: df2 = pd.DataFrame({'t': [4,1,2], 'user_id': [40,10,20], 'v': [400,100,200]})
In [6]: df2
Out[6]:
t user_id v
0 4 40 400
1 1 10 100
2 2 20 200
In [7]: df3 = pd.merge(df1, df2, how='right', on=['t', 'user_id'])
In [8]: df3
Out[8]:
t user_id v_x v_y
0 1 10 1.1 100
1 2 20 2.2 200
2 4 40 NaN 400
In [9]: df3['v'] = np.where(np.isnan(df3.v_x), df3.v_y, df3.v_x)
In [10]: df3
Out[10]:
t user_id v_x v_y v
0 1 10 1.1 100 1.1
1 2 20 2.2 200 2.2
2 4 40 NaN 400 400.0
In [11]: del df3['v_x']
In [12]: del df3['v_y']
In [13]: df3
Out[13]:
t user_id v
0 1 10 1.1
1 2 20 2.2
2 4 40 400.0Previous: How to use js/quill editor to write a rich text editor for switching between source code and text
there are two sets of data that make two dataframe, with the same row and column tags want to know how to get two dataframe to fill the missing values with each other. ...
data is the dataframe, of stock data, including the stock symbol and trading date. then I want to eliminate the data that the trading day is less than 15 days per month. the question now is: I have obtained the dataframe2, of less than 15 days per mon...
such a table, if you want to group it with key1, take the row with the larger data2, and how do you make a table? tried df.groupby ( key1 ,as_index=False). Max () takes the largest of all columns. This won t work. What can I do? this is the origin...
mainly wants to do tagging for named entity recognition; specifically, there are two excel files called A (n row 1 column), B (n row 1 column). for example, each line in An is a descriptive sentence, and each line in B is an entity name how to effici...
import pandas as pd reader=pd.read_csv file1 = reader.get_chunk (1000) file1.columns= [ souceip , sourceport , destport , protocol , flags , fwd , stos , pkt , byt , attack_type ] print (file1) here you re-modify the column nam...
there are at most four sources of the same data (revenue data) here, and some are null,. I want to choose one of these four sources as the statistical source (put in the value column). Which method should I choose to do this? ...
Table 1 add column matching Table 2 key _ word, if Table 1 add contains Table 2 key_word, write group, in Table 1 column 4, if not, mark "not included ". PS: I also measured with iteration, Cartesian product also tried, but did not achieve the desired ...
df is a dataframe , in which there are some NaN values in df . The following code formats each column in turn (for example, changing money into a string that begins with a dollar sign, and the corresponding function is formatter_function ). How t...
for example, there is data A B C 1 1 br 2 3 2 3 3 3 4 record5 1 3 such a table should be divided into A B C 1 13 1 23 2 3 3 43 1 53 1 63 excuse me how to operate ...
problem description I read the difference data from two different excel, and then put them into a new excel. It is normal to display a column of ID number under IDE, but it becomes a scientific counting method in excel. Is there a way to make a column...
at present, I am building a consolidated financial statement system, which grabs numbers from the financial system and then does data cleaning and calculation, in which the cleaning phase is mainly completed by pandas. In the captured data, the numbers ...
A function that follows lines in conditional Filter pandas. works fine for non-empty dataframe, but for empty dataframe, Filter with column name , empty datafram missing column name the problem is repeated as follows: In [5]: t1 = pd.DataFrame(co...
< H2 > Development environment < H2 > python 3.6 pandas 0.23.3 has a DataFrame, as follows: import pandas as pd arr = [[10,10,1],[22,24.2,1.1],[15,15,1],[9,8.1,0.9],[50,55,1.1]] df = pd.DataFrame(arr,columns=[ volume , amount , price ]) ...
problem description A pit when performing operations in the actual development of pandas! -------------------------------------------------------------------------------- age name 0 10 abc 1 11 None 2 9 def ---------------------------...
recently, I want to process the form for analysis. Due to the lack of experience in dealing with sql or csv, I have encountered the following problems and asked to solve them: question 1: companyID fresh 23865 13-2 23866 10-3 23867 8-...
pandas6Excel ...
use Pandas to analyze an excel data, which contains the excel date field date , stored values such as 2018-12-31 , and use pd.read_excel to read all excel data. Since the values in the table are entered artificially, in order to avoid potential readi...